I have three listboxes on a form which are to be used to select criteria for the WHERE clause of another form when it is opened. I currently have the following code on the onclick event of a command button:
Private Sub Command10_Click()
Dim strList0 As String
Dim strList2 As String
Dim strList4 As String
Dim strForm As String
Dim strGender As String
Dim strEthnic As String
Dim strSQL As String
For Each SelectedItem In List0.ItemsSelected
strList0 = strList0 & "[Form Groups_ID]=" & List0.ItemData(SelectedItem) & " Or "
strList0 = "(" & Left(strList0, (Len(strList0) - 4)) & ") AND "
For Each SelectedItem In List2.ItemsSelected
strList2 = strList2 & "[Genders_ID]=" & List2.ItemData(SelectedItem) & " Or "
strList2 = "(" & Left(strList2, (Len(strList2) - 4)) & ") AND "
For Each SelectedItem In List4.ItemsSelected
strList4 = strList4 & "[Ethnicities_ID]=" & List4.ItemData(SelectedItem) & " Or "
strList4 = "(" & Left(strList4, (Len(strList4) - 4)) & ") AND "
strSQL = Left(strForm & strGender & strEthnic, (Len(strForm & strGender & strEthnic) - 5))
DoCmd.OpenForm "PupilSearch", acFormDS, "", strSQL, , acNormal
This works fine aslong as all three listboxes have at least one value selected. However if any of the listboxes are left unselected then I get an 'Invalid procedure call or argument' error on the line underneath the Next line of the section relating to the first listbox that was left unselected.
Any ideas on how to modify the code so that if one of the listboxes is left unselected the code bypasses that section and only combines the values from the selected listboxes.
I have tried using if statements with listbox = "" and isnull(listbox) but I still get the same error.