choose items in listbox, pass to query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

johnboy7676@yahoo.com - 29 Feb 2008 20:19 GMT

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

Reply to this Message

Klatuu - 29 Feb 2008 20:37 GMT

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

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 13 lines]
>
> Thanks for any suggestions

Reply to this Message

johnboy7676@yahoo.com - 29 Feb 2008 21:50 GMT

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

>Modify this for your needs.
>
[quoted text clipped - 26 lines]
>
>End Function

Reply to this Message

Klatuu - 29 Feb 2008 22:15 GMT

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.

Signature

Dave Hargis, Microsoft Access MVP

> 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
[quoted text clipped - 38 lines]
> >
> >End Function

Reply to this Message