Using Combo Box and Multi List Box on Form - Send Parameters to Qu

[NEWER USER]

I am struggling using a Combo Box and Multi List Box on the same form and
sending Parameters to my query. I have tried using code to build my WHERE
clause and all works well with the List box only. When I add code for the
Combo Box, I get a message that Object or Method not Supported. I have tried
adding the Combo Box criteria (reference to cboGroup on form) directly on the
Criteria row of the query and this works as long as I select BOTH a value in
Combo Box and List Box. If I select a value in List Box only, I get an error
in WHERE claus. If I select a value in Combo Box only, it returns nothing.
How might I go about correcting my code for the Combo Box so the WHERE claus
is built using a Combo Box and List Box together or independent of one
another? So far I have...


On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim lngLen As Long
Dim strDelim As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strDoc As String
Dim strDoc1 As String

With Me!lstGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere1 = strWhere1 & strDelim & .ItemData(varItem) & strDelim & ","
End If
Next varItem
End With

lngLen = Len(strWhere1) - 1
If lngLen > 0 Then
strWhere1 = "[GroupID] IN (" & Left$(strWhere1, lngLen) & ") "
End If

With Me!cboGroup
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
strWhere2 = strWhere2 & "'" & strDelim & .ItemData(varItem) &
strDelim & "',"
End If
Next varItem
End With

lngLen = Len(strWhere2) - 1
If lngLen > 0 Then
strWhere2 = "[SDescription] IN (" & Left$(strWhere2, lngLen) & ") "
End If

strWhere = strWhere1

If Len(strWhere) > 0 And Len(strWhere2) > 0 Then
strWhere = strWhere & " AND " & strWhere2
Else
strWhere = strWhere & strWhere2
End If

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT qryQuickOrder.* FROM qryQuickOrder "
strSQL = strSQL & " WHERE " & strWhere
'*** delete the previous query
db.QueryDefs.Delete "qryQuickOrder1"
Set qdf = db.CreateQueryDef("qryQuickOrder1", strSQL)
'*** open the query
DoCmd.SetWarnings False

strDoc = "qryMakeTableQuickOrder"
strDoc1 = "qupdOrderRankings"

DoCmd.OpenQuery strDoc, acNormal, acEdit
DoCmd.OpenQuery strDoc1, acNormal, acEdit
DoCmd.Close
DoCmd.Requery ""
DoCmd.SetWarnings True


ANY HELP Appreciated!!

 

[Arvin Meyer [MVP]]

It might help to take a look at:

http://www.accessmvp.com/Arvin/Combo.zip