Combox box value used in Query

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

I have a form with several LIST boxes (multi type) that when selected build
the Where string of my query. ALL works well. However, my users find it
cumbersome in some list boxes to navigate when thousands of choices exist in
a list. I am thinking of an additional Combo box that will allow the Auto
Expand option and allow them to type what they want and get to their
selection much quicker. I would toggle the Combo box On and the list Box Off
for that specific option. My users would lose Multi selection on that one
combo box. So, I used the Combo box as my criteria in the query and it
worked fine, except when nothing was selected in the combo box. Select
nothing, get nothing. How might I go about adding code to my Where string
used with the list boxes rather than using the criteria field in the query?
Any help appreciated. My combo box is named cboGroup.

On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 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!lstClass
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 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

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

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

strWhere = strWhere1

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

If Len(strWhere) > 0 And Len(strWhere3) > 0 Then
strWhere = strWhere & " AND " & strWhere3
Else
strWhere = strWhere & strWhere3
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
 
STILL AT IT; I get an error message (Object Not Supported) when I add the
following code for the combo box. I think it has to do with the
..ItemsSelected statement when used with a combo box. Though the value will
not vary because only one selection is allowed from the combo box, it should
still recognize the one selection. Code added at strWhere4. Any help
appreciated.

NEWER USER said:
I have a form with several LIST boxes (multi type) that when selected build
the Where string of my query. ALL works well. However, my users find it
cumbersome in some list boxes to navigate when thousands of choices exist in
a list. I am thinking of an additional Combo box that will allow the Auto
Expand option and allow them to type what they want and get to their
selection much quicker. I would toggle the Combo box On and the list Box Off
for that specific option. My users would lose Multi selection on that one
combo box. So, I used the Combo box as my criteria in the query and it
worked fine, except when nothing was selected in the combo box. Select
nothing, get nothing. How might I go about adding code to my Where string
used with the list boxes rather than using the criteria field in the query?
Any help appreciated. My combo box is named cboGroup.

On Error GoTo Err_cmdOK_Click
Dim varItem As Variant
Dim strWhere As String
Dim strWhere1 As String
Dim strWhere2 As String
Dim strWhere3 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!lstClass
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 = "[CO] IN (" & Left$(strWhere2, lngLen) & ") "
End If

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

lngLen = Len(strWhere3) - 1
If lngLen > 0 Then
strWhere3 = "[SupplierID] IN (" & Left$(strWhere3, lngLen) & ") "
End If
Click to expand...

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

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

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

If Len(strWhere) > 0 And Len(strWhere3) > 0 Then
strWhere = strWhere & " AND " & strWhere3
Else
strWhere = strWhere & strWhere3
End If
Click to expand...

If Len(strWhere) > 0 And Len(strWhere4) > 0 Then
 
Back
Top