Running a parameter query off more than one multi-select list box

  • Thread starter Thread starter Barkley via AccessMonster.com
  • Start date Start date
B

Barkley via AccessMonster.com

I have finally been able to create a form with a multi-select list box and a
command button where a click of the command button opens a query with the
selections in the multi-select list box as its criteria. So far so good...
now I am wanting to use more than one multi-select list box as the criteria
for the query.

The code I used to successfully run a query off a multi-select list box is as
follows (with Command6 being the name of my command button, TESTQ being the
name of my parameter query, MyTable being the name of the table my list box
gets its values from, MyTableID the name of the field my list box gets its
values from, and List4 being the name of my multi-select list box):

Private Sub Command6_Click()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("TESTQ")

For Each varItem In Me!List4.ItemsSelected
strCriteria = strCriteria & "MyTable.MyTableID = " & Chr(34) _
& Me!List4.ItemData(varItem) & Chr(34) & "OR "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

strSQL = "SELECT * FROM MyTable " & _
"WHERE " & strCriteria & ";"

qdf.SQL = strSQL

DoCmd.OpenQuery "TESTQ"

Set db = Nothing
Set qdf = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End Sub


Given the above code, does anyone know how I can add more than one multi-
select list box to use as criteria for my parameter query?

All suggestions are appreciated…

Thanks,

Barkley
 
Barkley said:
I have finally been able to create a form with a multi-select list box and a
command button where a click of the command button opens a query with the
selections in the multi-select list box as its criteria. So far so good...
now I am wanting to use more than one multi-select list box as the criteria
for the query.

The code I used to successfully run a query off a multi-select list box is as
follows (with Command6 being the name of my command button, TESTQ being the
name of my parameter query, MyTable being the name of the table my list box
gets its values from, MyTableID the name of the field my list box gets its
values from, and List4 being the name of my multi-select list box):

Private Sub Command6_Click()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("TESTQ")

For Each varItem In Me!List4.ItemsSelected
strCriteria = strCriteria & "MyTable.MyTableID = " & Chr(34) _
& Me!List4.ItemData(varItem) & Chr(34) & "OR "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

strSQL = "SELECT * FROM MyTable " & _
"WHERE " & strCriteria & ";"

qdf.SQL = strSQL
DoCmd.OpenQuery "TESTQ" [snip boiler plate code]
Given the above code, does anyone know how I can add more than one multi-
select list box to use as criteria for my parameter query?
Click to expand...


I think you can just do the same kind of thing with the
other list box and connect the two sets of criteria with
" And " or " Or " depending on what you want to accomplish.

FYI, it would make for a less lengthy criteria if you used
the IN operator:

For Each varItem In Me!List4.ItemsSelected
strCriteriaA = strCriteriaA & "," & Chr(34) _
& Me!List4.ItemData(varItem) & Chr(34)
Next varItem
strCriteria1 = "MyTable.MyTableID IN(" _
& Mid(strCriteriaA,2) & ")"

For Each varItem In Me!ListXX.ItemsSelected
strCriteriaB = strCriteriaB & "," & Chr(34) _
& Me!ListXX.ItemData(varItem) & Chr(34)
Next varItem
strCriteria1 = "MyTable.somefield IN(" _
& Mid(strCriteriaB,2) & ")"

strSQL = "SELECT * FROM MyTable " & _
"WHERE " & strCriteriaA & " And " & strCriteriaB
 
Hi,
I have been struggling with getting a single multi-select list box to work as criteria for a query. My query is called Copy. The table where the query and my multi-select box get their data is called Doctors, and I would like to use the ID as criteria. My multi-select box is called List68.
I used your code, edited it to my specifications, and I keep getting an error 3464 message. I was wondering if you or anyone could help me out. I have been struggling with this for way too long. Thanks.
Here is the code that I have.

Private Sub Command6_Click()
On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Copy")

For Each varItem In Me!List68.ItemsSelected
strCriteria = strCriteria & "Doctors.ID = " & Chr(34) _
& Me!List68.ItemData(varItem) & Chr(34) & "OR "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)

strSQL = "SELECT * FROM Doctors " & _
"WHERE " & strCriteria & ";"

qdf.SQL = strSQL

DoCmd.OpenQuery "Copy"

Set db = Nothing
Set qdf = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End Sub
 
Back
Top