CODEWITHSUNDEEP
ms-accessms-access-2010
spacitron
spacitron

Reputation: 459

MS Access making navigation options on form NOT mutually exclusive

I'm creating a navigation form where some of the navigation buttons simply apply filters to the subform. Problem is right now each option is exclusive, i.e. I can select staff either by branch OR by job title. How can I make the options NOT exclusive so that I can apply multiple filters at once?

EDIT just to add. I have no knowledge of VBA so I'm trying to do this using the graphical interface and of macros. If it can't be done using these tools then fine, I'll find a different solution.

Upvotes: 0

Views: 178

Answers (2)

PowerUser
PowerUser

Reputation: 11781

This solution requires a moderate amount of VBA (I can't think of a solution that wouldn't require it). Store the user's choices in module level variables and then apply your filters using a master ApplyFilters subroutine.

For example, give each checkbox an AfterUpdate event. This event will do 2 things:

  1. Set the module level variable with the user's selection
  2. Start the ApplyFilters sub

Since all the user's choices are now stored in module level variables, the ApplyFilters can see them all. It will:

  1. Take all the module level variables and creates a master string (hint, if you need a Placeholder, use 1=1)
  2. Apply that string as your subform's filter.

Other notes: Accessing your subform's controls from the main form is simple. To change your subform's filter to the string NewFilter, try:

 Forms!MyMainFormsName!MySubFormsName.Filter=NewFilter

Upvotes: 1

Gord Thompson
Gord Thompson

Reputation: 123549

If you replace the .Filter property on a form (or subform) with a new value then the previous filter goes away. If you append a new clause onto an existing .Filter string, e.g. by changing...

[Branch]="Main"

...to...

[Branch]="Main" AND [Title]="Manager"

...then the new filter applies both criteria.

Upvotes: 1

Related Questions