INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using list boxes for Criteria

How to use Selected Items from ListBox as query criteria by MakeItSo
Posted: 14 Nov 03

Problem:
You have a multi-select ListBox on a form and you want to get all Fields from a table or query that match your Selection in the listbox.

Solution:

If you want to initiate the field search with e.g. a Command button, adapt the following code to your needs:


Sub YourSearchButton_Click()

Dim ctlList
Dim sSql as String

Set ctlList = Me.YourListbox
'*******************************
sSQL="SELECT * FROM YourTable Where "

For Each Lmnt In ctlList.itemsSelected
    sSQL=sSQL & "Surname= '" & ctlList.Itemdata(Lmnt) & "' OR "
Next

sSQL=Left(sSQL,Len(sSQL)-3) 'Remove last 'OR' with space
'********************************
DoCmd.RunSQL sSQL


Variation of this code is:

'********************************
sSQL="SELECT * FROM YourTable WHERE Surname IN ('"

For Each Lmnt In ctlList.itemsSelected
    sSQL=sSQL & ctlList.Itemdata(Lmnt) & "', '"
Next

sSQL=Left(sSQL,Len(sSQL)-3) & ")" 'Remove Last comma and single quote and add closing bracket
'*********************************

This code is of course valid for UPDATE, INSERT INTO or DELETE statements too, and only needs to be adapted respectively.

For those who need more complex statements:
Create a new query in query designer, switch to SQL view, copy/paste the statement into your code, make adaptions (esp. you might want to remove Forms![YourForm]! and set " & me!Whatever & "), where necessary.


Special thanks to PeteJohnston for the code variation.

MakeItSo

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close