Combo & List Box Criteria's

[David777]

Hi Everyone,

I have a combo box and a list box on a form. I want to choose an option from the combo box and a number of options from the list box, then, open a form based on the criteria. I can get them to work sepately, but not together. Here is my code.
When I click the Find command button, I get a prompt for a parameter value for the list box.
Private Sub cmdFind_Click()

Dim strDocName As String
Dim Criteria As String
Dim Criteria2 As String
Dim i As Variant

strDocName = "frmHelpDesk"

'Assign Value In The Department ComboBox
Criteria2 = "[Department]=" & Me![cboDepartment]

'ListBox Values
If [lstStatus].ItemsSelected.Count > 0 Then

' Build criteria string from selected items in list box.
Criteria = ""
For Each i In Me![lstStatus].ItemsSelected
If Criteria <> "" Then
Criteria = Criteria & " OR "
End If
Criteria = Criteria & "[Status]=" & Me![lstStatus].ItemData(i)
Next i
Else

MsgBox "Please select a Status."
Exit Sub
End If

Criteria = Criteria2 & " and " & Criteria

DoCmd.OpenForm strDocName, , , Criteria

End Sub

Thanks for any help,

David

 

[llkhoutx]

Try building your query in the QBE frame, convert it to SQL to see what it looks like and then build it accordingly.