I have code attached to the OK button on a form which has two list boxes. The first list box is a general category and the second list box is for a more specific category (example: Category 1 = Type of Vehicle, Category 2 = Manufacturer).
Currently I have this all set up for just one category, in this case my more specific category. I am trying to modify my code to work the same way it is but with two list boxes. I have tried appending to what I have but I can't seem to get it to work, suggestions?
Below is what I have and what works for ONE category:
Private Sub OK_Click()
On Error GoTo Err_OK_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tblIssues2"
'Build the IN string by looping through the listbox
For i = 0 To LstCategory.ListCount - 1
If LstCategory.Selected(i) Then
If LstCategory.Column(0, i) = "* ALL RECORDS *" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LstCategory.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Category] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryIssues2"
Set qdef = MyDB.CreateQueryDef("qryIssues2", strSQL)
'Open the query, built using the IN clause to set the criteria
'DoCmd.OpenQuery "qryIssues2", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.LstCategory.ItemsSelected
Me.LstCategory.Selected(varItem) = False
Next varItem
Exit_OK_Click:
Me.Visible = False
Exit Sub
Err_OK_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Exit Sub
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_OK_Click
End If
End Sub
Currently I have this all set up for just one category, in this case my more specific category. I am trying to modify my code to work the same way it is but with two list boxes. I have tried appending to what I have but I can't seem to get it to work, suggestions?
Below is what I have and what works for ONE category:
Private Sub OK_Click()
On Error GoTo Err_OK_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM tblIssues2"
'Build the IN string by looping through the listbox
For i = 0 To LstCategory.ListCount - 1
If LstCategory.Selected(i) Then
If LstCategory.Column(0, i) = "* ALL RECORDS *" Then
flgSelectAll = True
End If
strIN = strIN & "'" & LstCategory.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Category] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'If "All" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If
MyDB.QueryDefs.Delete "qryIssues2"
Set qdef = MyDB.CreateQueryDef("qryIssues2", strSQL)
'Open the query, built using the IN clause to set the criteria
'DoCmd.OpenQuery "qryIssues2", acViewNormal
'Clear listbox selection after running query
For Each varItem In Me.LstCategory.ItemsSelected
Me.LstCategory.Selected(varItem) = False
Next varItem
Exit_OK_Click:
Me.Visible = False
Exit Sub
Err_OK_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Exit Sub
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_OK_Click
End If
End Sub