• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1496
  • Last Modified:

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.

G

0
gordonr1
Asked:
gordonr1
  • 2
  • 2
1 Solution
 
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
0
 
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)?


0
 
Jeffrey CoachmanMIS LiasonCommented:
gordonr1,
<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:
http://allenbrowne.com/ser-50.html
http://www.mvps.org/access/reports/rpt0005.htm

Good Luck
:-)

JeffCoachman
0
 
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:

?Me.lstCustomers

and press the Enter key ...

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

0
 
gordonr1Author Commented:
Thanks for the advice guys!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now