Forms/subforms and multiple filters

[matttan]

I'm trying to set up a 'find record' form, which will allow users to select from a range of criteria, and filter the results of a subform based on those criteria.

I'll try and explain a bit better...

I have four fields - department, staff, day, shift - all combo boxes. Users will need to be able to populate any or all of those fields to filter the underlying subform/query.

At the moment, I've set the subform filter to all of those combo boxes (ie, "Staff - tmpStaff AND Dept = tmpDept etc etc"). This of course is an all or nothing approach.

Is there a way to set up my form so that the user can select any one of those fields, and filter just on that field, building up to any combination of those fields.

Thanks for the help!!!

 

[Auriga]

Would it not be easier to use filter by form?

 

[matttan]

How should I do that, keeping in mind that users will have little or no familiarity with a typical Access environment?

 

[Auriga]

Being a relative beginner in Access myself, I can only share what has worked for us.

We set up the form in the way that we wanted, ensuring that data was read only and then created a customised toolbar with filter by form and toggle filter on and off as the only elements. We set the toolbar to show both icons and text, and wrote captions that make sense to our staff.

Hope this helps.

 

[matttan]

Thanks for the thoughts. I've managed to work out a solution. The underlying query for the subform has selection criteria being: Like "*" & [combobox name] & "*", which will ensure it allows all through if left blank. Then, each combo box has an event attached for after update, which is essentially "subform.requery". I started using sendkeys F9, but thought the more specific requery would be better practice.

It works a gem!