473,836 Members | 1,520 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Report based on check box and list box...

I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

Thanks for your help!
Jan 11 '08 #1
5 6749
On Jan 11, 11:08*am, Andrew Meador <amead...@hotma il.comwrote:
* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

* *So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

* *Thanks for your help!
Dynamically build your query when the user clicks the button to
generate the report. I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. Pass the sql
you build to the report via code and you have what you want.
Jan 11 '08 #2
On Jan 11, 12:38*pm, frogste...@yaho o.com wrote:
On Jan 11, 11:08*am, Andrew Meador <amead...@hotma il.comwrote:


* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).
* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.
* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.
* *So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.
* *Thanks for your help!

Dynamically build your query when the user clicks the button to
generate the report. *I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. *Pass the sql
you build to the report via code and you have what you want.- Hide quoted text -

- Show quoted text -
Well, my thinking was close. I need to build the filter in code and
then pass it to the report. I assume I could do it in a similar way as
you are talking about as far as passing the whole query to the report.
The problem is I don't know how to do this. If you can give a sample
or refer me to directions on how to do this that would be excellent!

Thanks!
Jan 11 '08 #3
On Jan 11, 12:47*pm, Andrew Meador <amead...@hotma il.comwrote:
On Jan 11, 12:38*pm, frogste...@yaho o.com wrote:


On Jan 11, 11:08*am, Andrew Meador <amead...@hotma il.comwrote:
* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).
* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.
* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.
* *So basically it seems like the filter needs to be changed basedon
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.
* *Thanks for your help!
Dynamically build your query when the user clicks the button to
generate the report. *I do the exact same thing in one of my databases
where the report form has 8 list boxes, several check boxes, and a
hand full of text boxes to enter various date ranges. *Pass the sql
you build to the report via code and you have what you want.- Hide quoted text -
- Show quoted text -

* *Well, my thinking was close. I need to build the filter in code and
then pass it to the report. I assume I could do it in a similar way as
you are talking about as far as passing the whole query to the report.
The problem is I don't know how to do this. If you can give a sample
or refer me to directions on how to do this that would be excellent!

Thanks!- Hide quoted text -

- Show quoted text -
many ways to accomplish this. One is to declare a global variable as
a string. In the module where user clicks the button to build the SQL
assign the dynamically built SQL to the global string. Then in the
code for the form set the source to that variable.
Jan 11 '08 #4
Andrew Meador wrote:
I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).

What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.

What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.

So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.

Thanks for your help!
This might get you started in a direction that'll work for you.

Private Sub CommandReport_C lick()
Dim var As Variant
Dim strF As String 'holds report filter

If Me.ListBoxName. ItemsSelected.C ount 0 Then

'some items in listbox are selected. Change ListBoxName
'to your name. Assumes the key is the first column in the
'list box. If you have 4 columns, the first is 0, last is 3.
For Each var In Me.ListBoxName. ItemsSelected
strF = strF & Me.ListBoxName. Column(0, var) & ", "
Next

'remove the ", " and surround keys/values in ()
strF = "(" & Left(strF, Len(strHold) - 2) & ")"

'now see if it is "IN" list or "Exclude" list.
strF = "ID " & IIf(Me.CheckBox , "Not ", "") & _
"IN " & strF & " And "

'result so far is "ID (not) IN (1,2,3) And "

'let's pretend there's a text box code. Use single quotes
If Me.Code "" Then
strF = strF & "Code = '" & Me.Code & "' And "
End If

'lets pretend the dollar amt must be value. No quotes
If Me.DollarAmt 0 Then
strF = strF & "DollarAmt >= " & Me.DollarAmt & " And "
End If

'and we have a date range. Use # around dates
If Not IsNull(Me.FromD ate) Then
strF = strF & "DateFld >= #" & Me.FromDate & "# And "
End If
If Not IsNull(Me.ToDat e) Then
strF = strF & "DateFld <= #" & Me.ToDate & "# And "
End If

'now remove the trailing "And"
strF = Left(strF, Len(strF) - 5)

'now call the report with filter
DoCmd.OpenRepor t "TestReport ", , , strF

Else
MsgBox "Please select some items from listbox."
End If

Exit Sub
Too Long
http://www.youtube.com/watch?v=hb8uv4lGnno

Jan 12 '08 #5
On Jan 11, 9:37*pm, Salad <o...@vinegar.c omwrote:
Andrew Meador wrote:
* *I have a form (Change Card List by Status) with a check box
(cboNOT) and a list box (lstStatus). There is an Open Report button
that opens a report (Report - Change Card List) which uses a query
(SQL -Change Card List).
* *What I want to do is have the form open the report where a filter
is set to use the values from the check box AND the value selected
from the list box to generate the report.
* *What I can't figure out is how to use the check box's value in this
filter. If the check box is not checked the the filter would only be
based on the list box selection, but if the check box is selected, the
filter will select all records where NOT list box selection =
table.field.
* *So basically it seems like the filter needs to be changed based on
the check box. I think maybe a text string in code in a click event on
the Open Report button could set this filter string, but from that
point I don't know how to get the form to open the report (it in not
currently done by code) and how to set the filter of the report to the
form string.
* *Thanks for your help!

This might get you started in a direction that'll work for you.

Private Sub CommandReport_C lick()
* * *Dim var As Variant
* * *Dim strF As String *'holds report filter

* * *If Me.ListBoxName. ItemsSelected.C ount 0 Then

* * * * *'some items in listbox are selected. *Change ListBoxName
* * * * *'to your name. *Assumes the key is the first column in the
* * * * *'list box. *If you have 4 columns, the first is 0, last is 3.
* * * * *For Each var In Me.ListBoxName. ItemsSelected
* * * * * * *strF = strF & Me.ListBoxName. Column(0, var) &", "
* * * * *Next

* * * * *'remove the ", " and surround keys/values in ()
* * * * *strF = "(" & Left(strF, Len(strHold) - 2) & ")"

* * * * *'now see if it is "IN" list or "Exclude" list.
* * * * *strF = "ID " & IIf(Me.CheckBox , "Not ", "") & _
* * * * * * *"IN " & strF & " And "

* * * * 'result so far is "ID (not) IN (1,2,3) And "

* * * * *'let's pretend there's a text box code. *Use single quotes
* * * * *If Me.Code "" Then
* * * * * * *strF = strF & "Code = '" & Me.Code & "' And"
* * * * *End If

* * * * *'lets pretend the dollar amt must be value. *No quotes
* * * * *If Me.DollarAmt 0 Then
* * * * * * *strF = strF & "DollarAmt >= " & Me.DollarAmt & " And "
* * * * *End If

* * * * *'and we have a date range. *Use # around dates
* * * * *If Not IsNull(Me.FromD ate) Then
* * * * * * *strF = strF & "DateFld >= #" & Me.FromDate & "# And "
* * * * *End If
* * * * *If Not IsNull(Me.ToDat e) Then
* * * * * * *strF = strF & "DateFld <= #" & Me.ToDate & "# And "
* * * * *End If

* * * * *'now remove the trailing "And"
* * * * *strF = Left(strF, Len(strF) - 5)

* * * * 'now call the report with filter
* * * * *DoCmd.OpenRepo rt "TestReport ", , , strF

* * *Else
* * * * *MsgBox "Please select some items from listbox."
* * *End If

Exit Sub

Too Longhttp://www.youtube.com/watch?v=hb8uv4l Gnno- Hide quoted text -

- Show quoted text -
Wow! Thanks! That was really helpfull, I'm still fresh to actually
getting hands on coding done as well as working in Access and this was
very enlightening. Not too long for me, gave me a few new ideas!
I appreciate your time and thoroughness!

Andrew
Jan 14 '08 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
17687
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to Create a Dynamic Crosstab Report PRODUCT :Microsoft Access PROD/VER:1.00 1.10 OPER/SYS:WINDOWS
4
4742
by: Sami | last post by:
I hope someone will tell me how to do this without having to do any VB as I know nothing in that area. I am a rank beginner in using Access. I have created a database consisting of student athletes.  I have now learned how to join two different tables in a query so that I might generate a report. Specifically, student athletes at a community college are required to graduate with an AA or AS degree.  Consequently, various steps are in...
2
3850
by: Sara | last post by:
I have followed instructions on the http://allenbrowne.com/tips.html for limiting a report to a date range. At the bottom there is a note that says You will end up using this form for all sorts of reports. You may add an option group or list box that selects which report you want printed, and a check box that determines whether the report should be opened in preview mode. I have tried several times to do this but can't figure it out....
2
1921
by: Heather | last post by:
I want to create a report that shows the percent of safety audits complete by each individual shift, based on the safety audit date. The user has to put in the dates they want the report to pull up. This is done in a parameter value box. Is there a way for the user to put in which shift to pull up, based off a field that is a list box?
0
2867
by: Bill | last post by:
I've searched comp.databases.ms-access for a solution and found help but still haven't got my problem solved. I want to record weekly attendance and print an attendance "check off" sheet where attendees simply check on their name row and class date column to record their attendance. The marked attendance sheet is later used to update the database and print an updated attendance sheet for the following week's class. I'm working with...
6
9435
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate report" button located on my form to print all of the list box values (that have been updated via selection from combo boxes) from the form to the report. I've tried using a macro with the code: Macro Name: cmdGenerateReport : On Click Action:...
3
1595
by: Eugenio | last post by:
Hello everybody, I'm new to this forum, so sorry if i post something that has been already answered althought i couldn't find it in search. Anyway, there it goes: i have a search system which gives you the values you search for in a list box. The list box has its "Multi Select" property set to "Extended" which allows you to pick specific records from the list box. I also have a button to allow you to produce a report based on the values u've...
12
4052
by: micarl | last post by:
How would i print a report based on criteria selected from several Combo Boxes as well as multiple Multi Select List Boxes, that are located on the same form? I can get one Multi List Box, just not several, to report using this code i found - Private Sub cmdPreview_Click()
6
1655
patjones
by: patjones | last post by:
Good afternoon: This seems like it shouldn't be hard, and then again this is how so many problems seem at the outset. My situation is this: I have a report called rptMain319, which is based on a table tblMain319. The report groups employees by their work location (). By putting a Count(*) function in the footer for the Payroll Distribution grouping, I can get the total number of employees in that particular work location. My problem...
0
9820
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10844
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10548
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10591
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7792
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5649
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5826
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4452
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3115
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.