Get Value From List Box

[crhodus]

I've been searching on the forum, but I'm still not sure what I need to do.

First of all, I created a list box that gets it data from one of my tables. I set the multi select property to Extended. This part works correctly.

I want the user to be able to select multiple counties from the list box. After the user has selected these counties, he/she will then click on a button that will launch a report and pass the county names that were selected into the query of the report.

I'm not sure how to get the name of the counties, that the user selected, from the list box. Can someone help lead me in the right direction?

Thanks!

 

[R. Hicks]

Leave the Control Source property for the listbox empty (blank).

Use the example below to build code to be executed using the On Click event of your cmdButton:

Private Sub cmdYourButtonName_Click()
Dim varItem As Variant
Dim strWhere As String

For Each varItem In Me![YourListBoxName].ItemsSelected
strWhere = strWhere & "NameOfFieldinTable =" & Me![YourListBoxName].Column(0, varItem) & " Or "
Next varItem

strWhere = Left(strWhere, Len(strWhere) - 4) 'Remove the last " Or "

DoCmd.OpenReport "YourReportName", , , strWhere

End Sub

Change the names of all of the controls in the example above to the actual names of your controls.

HTH
RDH

[This message has been edited by R. Hicks (edited 09-25-2001).]

 

[crhodus]

Thanks for your help!

Whenever I click on my button, I get a Run-Time Error '3075' Syntax error (missing operator)in query expression.

The error occures in this line of code:
DoCmd.OpenReport "rptCenter", , , strWhere

I think that I don't have the query that is in the report rptCenter correct. Is this line of code, from rptCenter, correct?
WHERE (((tblWCenter.WCNum)=[strWhere]));

 

[R. Hicks]

That line does not appear correct. Can you email the app to me? I will take a quick look to see where the problem is.

Please zip the app if you can.

ricky@athree.com

RDH

 

[crhodus]

I formatted the code that you posted a little
I've gotten the form to put the counties, selected from the list box, into strWhere so that it will display them in this format:
'Pike', 'Montgomery', 'Smith'

For the SQL statement in my report, I have

WHERE (((MCStop.STWCName) In (" & strWhere ")));

I'm not sure what the correct syntax is to pass the string value from strWhere into a SQL statement.

 

[R. Hicks]

Check your email ....

I sent the db back to you. See if this does what you want. You did not need to place anything in the query for the criteria, the Dynamic SQL being built from the form handles this for you.

HTH
RDH