Multiple Selection in listbox that filters form - Access 2003

Hi Experts,

I have a multiple select list box on a pop-up form.  The list box contains the following columns: EmployeeID, FirstName And LastName.  

I need the database user to be able to select all the employees they are interested in and click a command button on the pop-up form  to filter my main form.

The user can now update the filtered records and use the command buttons on the main form to cycle through them, until every record has been updated.  Once the update is completed the user would need to remove the filter by selecting another command button on the main form.

I have no programming experience and need to know what code should be inserted in the click event property of the command button on the pop-up form to filter the main form and what code would I need to remove the filter?

My thanks in advance.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming your form is already bound to the dataset, you can use this:

Me.Filter = "Your Filter Here"
Me.FilterOn = True

to turn "on" your filter. To turn it "off":

Me.FilterOn = False

As to your Filter: this is a traditional WHERE clause without the "where" ... in other words, if you want to filter your form for all Employees selected in your listbox, you'd do this:

Dim var As Variant
Dim sFilter As String

For Each var in Me.MyLIstbox.ItemsSelected
  sFilter = Me.MyListbox.ItemsSelected(0, var) & "," & sFilter
Next var

sFilter = Left(sFilter, Len(SFilter)-1)

Me.Filter = "EmployeeID IN(" & sFilter & ")"
Me.FilterOn = True

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gordonr1Author Commented:
Hi there,

I'm getting the following error when I click the command button on my pop-up form:

Wrong number of arguments or invalid property assignment (Error 450)?

Jeffrey CoachmanMIS LiasonCommented:
<I have no programming experience>
Well... what you have asked for requires "Some" programming experience.

LSM's code *will* work for you.
"Some" programming experience is needed becaues you have to substitute all of you object names for the ones has has used.
...and to troubleshoot any errors
...and to customize it for your database

Try to get it to work.

In the mean time, here are two of the most popular links on the subject:

Good Luck

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Thanks for stepping in Jeff ...

As Jeff said, you'd need to substitute the names of your project's Objects (Forms, Listbox, table Columns etc) for the placeholder's I've included.

Best way to locate an error is to use "breakpoints" ... in your code, place your cursor in the first or second line of code, then press F9 ... this should color the line (normally Red). Now run your code (i.e. click the button, etc) and you'll be thrown into the VB Edit window. You can then see exactly where the error occurs.

YOu can also view the values of variables, controls etc in this mode by using the Immediate Window (Ctrl + G will get you there). To view the value of a listbox named lstCustomers, you'd do this in the Immediate Window:


and press the Enter key ...

To step through your code during Break mode, press the F8 key.

gordonr1Author Commented:
Thanks for the advice guys!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.