Create multiple-criteria query using VBA

Topic

New Reply

I have inherited a database with a form that is supposed to give users the ability to select a variety of options and then create a report. The form has 17 combo boxes where the user is supposed to be able to make 1 to 17 selections to run a query. The combo boxes look up their values from a table that contains a list of tasks. The list is then used to query another table that references these tasks. The goal is to get a list of individuals who are able to perform these tasks. If there were 3 combo boxes, I’d be fine. I am creating the query using VBA because it was not working using an Access query referencing the form. The code I’m using will work for 3 boxes, but I shudder to think how much code would be required to handle 17. There must be an easier way to set this up. What am I missing?

Here’s my code for the first 3 boxes. I apologize for the non-creative names for the combo boxes that were used by the previous owner of the database! I check elsewhere if combo6 is null, and demand at least one selection be made. I build the query by using the variable strIn as criteria for an In (” “) statement within the query.

If IsNull(Me.Combo8) Then
If IsNull(Me.Combo10) Then
strIn = “‘” & Me.Combo6 & “‘”
Else
strIn = “(‘” & Me.Combo6 & “‘,'” & Me.Combo10 & “‘)”
End If
Else
If IsNull(Me.Combo10) Then
strIn = “(‘” & Me.Combo6 & “‘,'” & Me.Combo8 & “‘)”
Else
strIn = “(‘” & Me.Combo6 & “‘,'” & Me.Combo8 & “‘,'” & Me.Combo10 & “‘)”
End If
End If

Reply | Quote

Replies

Instead of 17 combo boxes, I’d use a single list box with its MultiSelect property set to Simple or Extended. The user can select any number of items in the list box.

You’d use code like this (lstSelect is the name of the list box):

Code:

Dim v As Variant
Dim strIn As String

For Each v In Me.lstSelect.ItemsSelected
  strIn = strIn & ", " & Me.lstSelect.ItemData(v)
Next v

If Not strIn = "" Then
  strIn = "(" & Mid(strIn, 3) & ")"
End If

The advantage of this approach is that it doesn’t matter how many items the user selects – 1, or 5, or 37.

Reply | Quote

Your solution works perfectly Hans, as always! The user will be pleased since they needed to run this query several times to accommodate all the selections they needed. The list box is much more flexible and easier to work with.

Thanks again!

Reply | Quote