Filtering records based on user selection from lstBox

[mvorous]

Access 2007, geered toward Access 2000 version...
I have a cont. form (Room, NursingStation, patientname etc.)
that I want filtered based on a selection a user makes from a list box "lstnursingstation" in the header section. They would pick a Nursing Station (from a list of 7) and the records would show only say "1st floor" in that particular field.
I used this code on an afterupdate from the list box; Me.RecordSource = "SELECT MainTable.* FROM MainTable WHERE (((MainTable.NursingStation) = lstnursingstation)) ORDER BY MainTable.Room;" Something isn't right of course as it doesn't filter as I expect it to.
Any tips? Thanks, mvorous

 

[pbaldy]

You would need the full form reference:

Forms!FormName.lstnursingstation

The listbox can not be multiselect.

 

[mvorous]

I used...
Me.RecordSource = "SELECT MainTable.* FROM MainTable WHERE (((MainTable.NursingStation) = Forms!maintable.lstnursingstation)) ORDER BY MainTable.Room;"
It prompted me with an "Enter parameter value... Forms!mainTable.lstnursingstation
?????

 

[pbaldy]

The form has the same name as the table? Can you post the db?

 

[mvorous]

Form name: mainmenu, Table name: maintable.
Do you mean to reply with the *.mdb file?
if so, how do I attach the file if my prompt is a URL address? Haven't done that as yet, sorry. thanks for your help

 

[mvorous]

pbaldy, Paul, thanks, your comments about the form name and table name pointed out that I used a reference to the table in my code instead of the form itself. Thank you for your help, it works great now.
mvorous

 

[pbaldy]

Sorry, I was working on a project; glad you got it sorted out.