multiselect problem

[angie]

I have a query based on another query with multiple criteria. I want my
query to return records based on a multiselect listbox in a form. How can I
achieve that?
I have looked up the subject in other questions that have been answered and
from what I understand I have to use sql (my knowledge is limited). The
problem is that most answers concern reports, not queries, and I do not know
where I have to place the sql to filter the query. i assume that i will have
to use the code in the event procedure of a command button.
Pls help me with that!

 

[Douglas J. Steele]

Actually, you need to use VBA to change the SQL of your query.

Let's assume your query (named MyQuery) currently looks like

SELECT Field1, Field2, Field3
FROM MyTable

and you want to limit that based on which values in Field3 equal which ever
values were selected in listbox MyListbox.

Your VBA would look something like:

Dim qdfCurr As DAO.QueryDef
Dim strSelected As String
Dim strSQL As String
Dim varSelected As Variant

If Me.MyListbox.ItemsSelected.Count = 0 Then
' Nothing selected in the listbox: nothing needs to be done
Else
' Loop through all of the selected rows, adding their value to strSelected
' as a comma-separated list
For Each varSelected In Me.MyListbox.ItemsSelected
strSelected = strSelected & Me.MyListbox.ItemData(varSelected) & ", "
Next varSelected
' Remove the extra ", " from the end of strSelected
strSelected = Left(strSelected, Len(strSelected) - 2)
' Build the revised SQL for your query
strSQL = "SELECT Field1, Field2, Field3 " & _
"FROM MyTable " & _
"WHERE Field3 IN (" & strSelected & ")"
' Update the query to use the new SQL
Set qdfCurr = CurrentDb().QueryDefs("MyQuery")
qdfCurr.SQL = strSQL
qdfCurr.Close
End If

That assumes Field3 is a numeric field (and that the bound field in
MyListbox returns a number). If it's text, change

strSelected = strSelected & Me.MyListbox.ItemData(varSelected) & ", "

to

strSelected = strSelected & """" & Me.MyListbox.ItemData(varSelected) &
""", "

That's four double quotes in a row in front, and three double quotes in a
row after.

 

[Klatuu]

It does take SQL and it does take a fair amount of coding. To build a Where
string based on the selections in a multi select list box, here is function
that does exactly that:

Just pass the name of the control to the function like this:

strCriteria = BuildWhereCondition(Me.MyListBox.Name)

Now, here is a trick you can use to help write your SQL if you are not
comfortable with doing it by hand (or if you are lazy like me and want the
computer to do the grunt work)

Build your query using the query builder.
Switch to SQL view. Copy and Paste the SQL into your VBA module. You will
have to clean it up a bit with quotes. Now, you can add the results of the
query as the where condition, but you will have to add the word WHERE.


Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function

 

[angie]

i have pasted the following code to the event procedure of a command button
in my form and i get the error "user defined type not defined". what could i
have done wrong?
moreover, my list box contains text values. i have made the correction you
pointed out but its is marked in red. perhaps i should use only one row?

Ο χÏήστης "Douglas J. Steele" έγγÏαψε:

 

[angie]

i get a runtime error 3131 = syntax error ith FROM clause and the cursor
point to the "qdfCurr.SQL = strSQL" line of the code. have i done something
wrong with the query?


Ο χÏήστης "Douglas J. Steele" έγγÏαψε:

 

[Douglas J. Steele]

What's the SQL that it's complaining about?

Right before the qdfCurr.SQL = strSQL line, put

Debug.Print strSQL

When the code runs (and you get the error), go to the Immediate Window
(Ctrl-G) and see what's printed there.

Copy what's there into your reply if you're still having problems.

 

[Evi]

Make sure that you have the correct References. (In any module go to Tools,
References).

I need
Microsoft DAO 3.6 Object Library on my Antique2000 to make this work.
Evi