Hello all!
Let me just preface this with the fact that I am a complete newbie. I majored in showtunes. I am learning as I go, but considering I'd never looked at Access till 3 weeks ago I think I'm catching on.
There is a long story leading to this point, but I've been asked to create a query where people can multi select parameters. I made 2 listboxes - one for 'Subjects' and one for 'Categories.'
I found a VBA code that I could read, and modified it to my purposes.
Option Compare Database
Private Sub Okay_2_Click()
Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim Crit As String
Dim ctl As Control
Dim ctl2 As Control
Dim Itm As Variant
Dim Itm2 As Variant
' Build a list of the selections.
Set ctl = Me![LstSubject]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Build a list of the selections.
Set ctl2 = Me![LstCategory]
For Each Itm2 In ctl2.ItemsSelected
If Len(Crit) = 0 Then
Crit = Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
Else
Crit = Crit & "," & Chr(34) & ctl2.ItemData(Itm2) _
& Chr(34)
End If
Next Itm2
If Len(Crit) = 0 Then
Itm2 = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect")
Q.SQL = "Select * From BabyData Where [SUBJECT] In(" & Criteria & _
") And [Category] In(" & Crit & _
");"
Q.Close
' Run the query.
DoCmd.OpenQuery "MultiSelect"
End Sub
This does not give me any errors- however, it also doesn't give me any results. I'm not sure what I am missing here- if someone could let me know what I should be looking at, I'd be very grateful.
Thank you!
Tatum
Let me just preface this with the fact that I am a complete newbie. I majored in showtunes. I am learning as I go, but considering I'd never looked at Access till 3 weeks ago I think I'm catching on.
There is a long story leading to this point, but I've been asked to create a query where people can multi select parameters. I made 2 listboxes - one for 'Subjects' and one for 'Categories.'
I found a VBA code that I could read, and modified it to my purposes.
Option Compare Database
Private Sub Okay_2_Click()
Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim Crit As String
Dim ctl As Control
Dim ctl2 As Control
Dim Itm As Variant
Dim Itm2 As Variant
' Build a list of the selections.
Set ctl = Me![LstSubject]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Build a list of the selections.
Set ctl2 = Me![LstCategory]
For Each Itm2 In ctl2.ItemsSelected
If Len(Crit) = 0 Then
Crit = Chr(34) & ctl2.ItemData(Itm2) & Chr(34)
Else
Crit = Crit & "," & Chr(34) & ctl2.ItemData(Itm2) _
& Chr(34)
End If
Next Itm2
If Len(Crit) = 0 Then
Itm2 = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If
' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("MultiSelect")
Q.SQL = "Select * From BabyData Where [SUBJECT] In(" & Criteria & _
") And [Category] In(" & Crit & _
");"
Q.Close
' Run the query.
DoCmd.OpenQuery "MultiSelect"
End Sub
This does not give me any errors- however, it also doesn't give me any results. I'm not sure what I am missing here- if someone could let me know what I should be looking at, I'd be very grateful.
Thank you!
Tatum