Full Version: Multiple Combo Boxes for same field for query ...
UtterAccess Forums > Microsoft® Access > Access Forms
ceidre1970
I have a database to keep track of overtime. Via combo boxes, the form prompts the user for department, date range, and employee and returns the amount of overtime worked by that employee for that department in the given range. I'd like to put 5 combo boxes on the form and allow the user to select up to 5 employees, resulting in a report with each of the employees listed with their respective data. I have considered a multi-select list box, but since we have 300+ employees the ability to start typing a last name to find each employee seems a little more user-friendly. I am new to Access, and am unsure if what I'm proposing is the most feasible way to accomplish what I need. Thanks.
ScottGem
A multi-select listbox is more feasible. Especially if you are also filtering by dept and date range. You can then filter the listbox so it only shows employees from that department and who had overtime in that date range.
ceidre1970
In my form the user chooses department first, then date range, then the list box. Would the list box be filtered upon opening the form, or in an event? Thanks.
ScottGem
Do a search here for Cascading or synchronized combos. This is an oft discussed and standard tehcnique to filter a list based on a previous selection. It can be used with combo or list boxes.
ceidre1970
I am stuck on getting my list box to work correctly. Although I've read numerous articles on multi-select list boxes as filters for queries/reports in the last few hours I can't seem to get my head around the concept. I know I can't enter the list box directly as criteria in a query, after that I'm lost! I'd like to be able to select multiple employees from the list box on frmSpecificEmpOT, and have those names/id's be the basis for qrySpecificEmpOT which is the source for rptSpecificEmpOT. The report should contain Last Name, First Name, Sum of Hours, and Last Date Worked for each selection from the list box. Originally I had one combo box on frmSpecificEmpOT that contained employees, which I have since replaced with a list box. Help please!
Thanks.
Edited by: ceidre1970 on Tue May 2 12:35:48 EDT 2006.
ScottGem
If you look at the Help for Multi-Select listboxes, the basic idea is you need to cycle thru the ItemsSelected collection, creating a list of the selected items. Basically building a WHERE clause to be used in an SQL statement.
ceidre1970
I've continued to look at help files for multi-select listboxes to no avail. I found the following code, which is purported to be just what I need (from .mvps.org/access/forms/frm0007.htm).
im frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem
'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
However, I have no idea what to do with it! Surely it doesn't go in the criteria field of the query design. I thought perhaps it would go in the row source but don't really have any idea. Also, many of the articles/forums I've read talk about coding the On Click event procedure. Is it really necessary to add another cmd button or can I just use an "OK" button to submit all selections and open a report which runs a query? I attached the db in I'm working on to my previous post. An example using/modifying that db would be most helpful. I apologize for my lack of understanding. It's more of a learning curve than I expected.
Thanks,
ScottGem
The MVPS site code is a good example. What it does is build an entire SQL statement. The Result of the coder would the a string variable called strSQL that might look like this:
Select * from Employees where [EmpID]= 123 OR [EmpID]= 345 OR [EmpID]= 678;
this would reflect 3 employees seected in the list box. Now the question becaose what to do with that SQL statement. One possibility would be to assing that to a hidden textbox on your form, then reference it on your Report in the On Open Event:
Me.RecordSource = Forms!formname!txtSQL
So when you open the report it takes that SQL as its recordsource.
ceidre1970
"There aren't any stupid questions, just stupid people!" <<< This is how I feel today!
understand the SQL statement will be the data source for a hidden text box, which will be the source for my report. I still don't understand what to do with the code I got from the MVPS site. The list box After Update event?
Geez ... before today I really thought I was starting to get a handle on Access ...
ScottGem
You use the MVPS vode to generate the SQL statement. Then assign that to your hidden control.
ceidre1970
How do I use the code to generate the SQL statement (what do I do with it), and how do I assign the SQL to the hidden control - the Control Source?
Thanks again,
ScottGem
You really need to try and understand the code. If you look at that code, you will see a SQL statement being generated and assigned to a text variable. You assign the text to the hidden control using a simple assignment statement: x = y
ceidre1970
I think it's obvious I'm in over my head on this, because I'm still only grasping parts of what you're saying. I don't know what to do with the assignment statement, nor do I know what to do with it. Thanks anyway for taking the time to try and help.
ScottGem
I think you are psyching yourself out of understanding something that is really not that hard. By psyching yourself out, you aren't trying to reason things through.
That is an assignment? Its setting something to something else. The example I gave you was x = y. In your case x is the name of your hidden control and y is the variable storing the SQL statement. Isn't that clear?
So the next question is what is the variable holding the SQL. Well, if you look at the code, that should be very obvious.
ceidre1970
I believe I understand you now ... (sort of) ... I was taking your "x=y" example literally. strSQL is the variable in that particular code?
found a different solution, which makes more sense to me than what I was previously trying to do. Can you see any advantages of using one way over the other?
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In lbEmp.ItemsSelected
stDocCriteria = stDocCriteria & "[field] = " & listbox.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function
Private Sub cmdOK_Click()
DoCmd.OpenReport "rpt", acPreview, , GetCriteria()
End Sub
ScottGem
You are starting off doing the same basic thing. Each code snippet is building a WHERE clause by looping thru the ItemsSelected collection of the List box. The only real difference is that the first snippet, builds the WHERE clause into a full SQL statement and the second, builds just the WHERE clause to be used with the OpenReport method.
The only change I would make is to combine this into one Put the CetCriteria code into the OK button Click event eliminating the GetCriteria = stDocCriteria line and substituting stDocCreitera in the OpenReport.
ceidre1970
Works great ... thanks!

One last question:
I'd like to use tab controls on my switchboard to designate the departments for which a user can enter overtime. I started out with a combo box in each form, but I'd rather have a user choose the department just once at the start rather than for every time he opens a form for data entry or every time he needs a report. I already had the database set up with the combo boxes. Is there a way to use the same form/report/query for each department, or would each department need it's own?

Thanks.

To clarify - Is there a way to let the form/report/query know which tab (department) the request is coming from? Something like "the user used ____ button/tab to get here, so that means the results should be for the _____ department".
Edited by: ceidre1970 on Fri May 5 12:26:33 EDT 2006.
ScottGem
As long as you have a variable someplace where you can reference the current department, you can then filter for it.
ceidre1970
Thanks.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.