Use combo box for query parameters

[Maxine1010]

Hi,

I've searched through the site for previous postings to no avail, so I was hoping someone could solve what I think is probably quite simple.

I have a combo box (cmbWard) on a form, I want to be able to select a ward from this combo box, click on a command button and transfer this selection to a list box (lstWard). I will then use the selected wards in the list box as parameters for a query/ filter.

I tried using additem but this doesn't exist in Access so I'm stumped...help...please......!!

Thanks

 

[Mile-O]

If you have the listbox on the form already then dispense with the combobox and command button and set the listbox's MultiSelect property to Simple. Set the list's RowSource to whatever you originally had as the combo.

 

[Maxine1010]

If you have the listbox on the form already then dispense with the combobox and command button and set the listbox's MultiSelect property to Simple. Set the list's RowSource to whatever you originally had as the combo.

Thanks for that the list works fine, however now when I run the query for some reason it doesn't recognise the list box as a search parameter. In the select query I refer to the criteria as

like "*" &[Forms]![frmReport]![lstWard]

but it doesn't pull anything out.

It works when multiple selection is off.

what am I missing?

 

[Mile-O]

Because a listbox (with a multi select) has multiple options, you need to build the query with VBA using the ItemsSelected property and the IN keyword.

I've attached a DAO example

 

[Maxine1010]

Because a listbox (with a multi select) has multiple options, you need to build the query with VBA using the ItemsSelected property and the IN keyword.

I've attached a DAO example

Thanks for that Mile-O-Phile. I have played around with my database all afternoon trying to incorporate the code in your example which was very good (may I add!) But I still don't seem able to conquer this problem. I have attached a small sample database and was hoping you wouldn't (or anyone else for that matter) mind taking a look. The tables, queries and forms of interest are:

Tables: MRSATB and WardList
Query: qryReport
Form: frmReport

I want the query to return all of the fields in MRSATB using the parameters shown on frmReport.

I am extremely grateful for all your help! Thanks!