I'm helping out with a database at work and we have a need for a complex filter function. Due to our 'low-to-no tech' user base, the perfect solution (the built in 'filter by form' function) is neither obvious nor intuitive enough.
The approach we want to take is something like this (very simplified version - there are significantly more fields in the real version, which led us to rule out a number of possible approaches to a solution).
The left side of the screen shows a selection of fields from the main table and the right hand side mirrors these fields for selecting search/filter criteria.
Our first attempt had these search fields named in the format "FilterField1" set with no control source. The idea was that the user would set the criteria as they wanted them and then hit the search button. The button would run a macro that would be a series of ApplyFilter commands:
ApplyFilter where condition: [Field1]=[forms]![frmFilterTest]![FilterField1]
ApplyFilter where condition: [Field2]=[forms]![frmFilterTest]![FilterField2]
ApplyFilter where condition: [Field3]=[forms]![frmFilterTest]![FilterField3]
ApplyFilter where condition: [Field4]=[forms]![frmFilterTest]![FilterField4]
This sort of worked in that the user could select the values they wanted and hit search to return the correct results. However, if they then changed their search parameters and hit search again, Access recalled the original values and not the new ones. It made no difference whether the exiting filter was removed (by selecting 'remove filter' from the tool bar) - the only way to reset the parameters stored in MS Access' memory was to close and reopen the form.
We made a variety of increasingly complex attempts (such as putting the search criteria in a subform, storing the criteria in their own table, etc.), but suffice it to say we made no progress. I'm sure that the answer is going to be reasonably simple, and probably subtle, but I'll be damned if I can figure it out or find an answer through Google.
Any suggestions?
One further criteria - we want to avoid modules if possible. Macros are reasonably easy to explain to someone who may need to make changes in the future, but real code is beyond the 'I can learn this' psychological barrier for most non-techies'
Edit: corrected some of the more glaring typos - shouldn't have written this when I was so tired....
The approach we want to take is something like this (very simplified version - there are significantly more fields in the real version, which led us to rule out a number of possible approaches to a solution).
The left side of the screen shows a selection of fields from the main table and the right hand side mirrors these fields for selecting search/filter criteria.
Our first attempt had these search fields named in the format "FilterField1" set with no control source. The idea was that the user would set the criteria as they wanted them and then hit the search button. The button would run a macro that would be a series of ApplyFilter commands:
ApplyFilter where condition: [Field1]=[forms]![frmFilterTest]![FilterField1]
ApplyFilter where condition: [Field2]=[forms]![frmFilterTest]![FilterField2]
ApplyFilter where condition: [Field3]=[forms]![frmFilterTest]![FilterField3]
ApplyFilter where condition: [Field4]=[forms]![frmFilterTest]![FilterField4]
This sort of worked in that the user could select the values they wanted and hit search to return the correct results. However, if they then changed their search parameters and hit search again, Access recalled the original values and not the new ones. It made no difference whether the exiting filter was removed (by selecting 'remove filter' from the tool bar) - the only way to reset the parameters stored in MS Access' memory was to close and reopen the form.
We made a variety of increasingly complex attempts (such as putting the search criteria in a subform, storing the criteria in their own table, etc.), but suffice it to say we made no progress. I'm sure that the answer is going to be reasonably simple, and probably subtle, but I'll be damned if I can figure it out or find an answer through Google.
Any suggestions?
One further criteria - we want to avoid modules if possible. Macros are reasonably easy to explain to someone who may need to make changes in the future, but real code is beyond the 'I can learn this' psychological barrier for most non-techies'
Edit: corrected some of the more glaring typos - shouldn't have written this when I was so tired....
Last edited:
