By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,592 Members | 975 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,592 IT Pros & Developers. It's quick & easy.

Query Based on Selection Using a Checkbox

P: 11
I have a database that tracks design changes made by different groups of people who work for different managers. I currently have a form setup such that the user can select a range of dates and when clicking a button to view the report, the report only shows the data within the selected date range.

I'd like to add the functionality that the user can check a box next to a single manager or multiple manager names and the report will display the data in the specified date range and for only the managers selected. I've been able to get the report and form to work for a single manager, but I can't seem to figure out how to get it to work for all the managers.

For a single manager, in the query I have the "manager" field criteria set to [txtmanager] where [txtmanager] is something the user selects. I thought about trying to have several checkboxes (one for each manager) on my form and then have some sort of IF statement that IF the checkbox was checked, then [txtmanager] = manager1. For multiple managers I thought I might be able to do somelike with AND statements.

Is this the correct way to try to do this and how would I go about doing it?
May 31 '07 #1
Share this Question
Share on Google+
1 Reply

MMcCarthy
Expert Mod 10K+
P: 14,534
You can use VBA code to do this dynamically. Remove the criteria of the form dates and the manager from the query and use VBA code to create the criteria instead. Forget the Checkbox and create a combobox of mangers instead (cboManger).

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strDocName As String
  3. Dim strLinkCriteria As String
  4.  
  5.     strDocName = "Your Report Name"
  6.     strLinkCriteria = "([DateField] BETWEEN #" & txtDateFrom & "# AND #" & txtDateTo & "#)"
  7.  
  8.     If nz(Me!cboManger, "") <> "" Then ' If manger is selected
  9.        strLinkCriteria = strLinkCriteria & " AND ([Manager]='" & Me!cboManager & "')"
  10.  
  11.     DoCmd.OpenReport strDocName, , , strLinkCriteria
  12.  
Jun 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.