choose items in listbox, pass to query

[johnboy7676]

I have a listbox on a form, whose recordsource is a field ([field1])
of tblSomeTable. I want user to be able to choose one or more items
(usually only one, occasionally 2 or 3, but probably never more than
3), and use those selections for a query which will be used for
another Form and a report.

So that the queries criteria will be
SELECT Field1,
FROM tblSomeTable
WHERE (((Field1)="item1" Or Field1="item2" Or Field1="item3" ));

(assuming there were 3 items selected)

I have Access 2002 Developers Handbook, which has an exmple of a
picklist, but it gets over my head, I'm afraid.

Thanks for any suggestions

 

[Klatuu]

Modify this for your needs.

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

 

[johnboy7676]

Thanks for the reply. I played with this for a while, but I'm not
really sure where it goes. In a standard module? (strControl As
String) I'm guessing is the name of the listbox control on the form?

I put it on the onclick event of a button on the form (leaving out
Private Function.... and setting ctl to my listbox), and strwhere
*is* the items I choose on my listbox, so that part of it works, but I
can't quite figure out what to do with the strwhere.....how do I plug
strwhere into the criteria for the query?

thanks

 

[Klatuu]

Put the function in the form's module.
Yes, you can just pass it the name of the list box. I did it that way
because the form had 8 list boxes.

I can't really tell you exactly how to use it, but what it does is create a
Where clause based on the selections in the list box. You will have to add
the name of the field you are comparing on.

You use it as the query's criteria.