Using Multi-Select ListBox for a Parameter Query

[kopy]

I am trying to use a multi-select listbox as criteria for a parameterized query. I've tried the code on the Access Web with no success. My revision of their code looks like this:

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set frm = Forms!frmQBF
Set ctl = frm!lbJobCategory

strSQL = "Select * from [tblExposure/PatientData] where [JobCategory]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " Or [JobCategory] = "
Next varItem

'Trim the end of strSQL
strSQL = Left$(strSQL, Len(strSQL) - 20)

Me![txtJobCat].Value = strSQL

As suggested on the Acces Web I write strSQL to a textbox on the form with the listbox. I then reference the textbox in the ctieria of a field in the query. The result is no records selected.

Any help with this will be greatly appreciated.

Thanks, Kopy

 

[dhookom]

You can't use a value in a text box like this. I'm not sure where that code is pushing you but I seem to recall recent private discussions amoung the Access MVPs regarding the function.

It would help some to see the SQL of your query. You can search through the FAQs here for a solution or find a generic listbox function with sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]