A common technique that I use to make a more professional looking user interface for filtering data for a report in an MS Access database, is to create a form to ask for criteria for the report. One example would be a report that is used to show data for a particular time period.
Create a query to feed data to the report. Add any necessary parameters (like the “between [start_date] and [end_date]” style) and test.
Create the report, using the query from step 1 as the record source. Test the report thoroughly. Obviously, you will have to answer the parameter questions manually, one at a time.
Create a form with a text box for the starting date and another text box for the ending date. Use the button wizard to add a button that will preview the report created in step 2.
Change the parameters in the query to this style:
Between [forms]![myform]![txtStartDate] and [forms]![myform]![txtEndDate]
making sure you use the actual names for the form “myform” and the text boxes “txtStartDate” and “txtEndDate”.
That’s it. And, no VBA coding was required.
(you may need to put some code behind the button that will verify the values in the various fields before starting the report, this would require VBA – you could also use a combo box to collect data for the report criteria)
Richard Killey is a Computer Database Programmer specializing in MS Access database development. Visit http://www.databaselessons.com to read other articles of interest, and to download sample MS Access databases.
Article Source: http://EzineArticles.com/?expert=Richard_Killey
This is the principle of how to use an Access Form as the parameter input instead of the direct and raw prompt that an Access Query provides.
If you are going to use a Form, it makes sense to have various controls to make this a more flexible input form (several parameter options) that makes this in essence a criteria search tool for a report.
If you do this however, you increase the likely hood of using either and Access Macro or VBA code to enhance and validate data entry values and control navigation.
The question you must ask to justify whether you stick with the standard ‘out of the box‘ Access Query prompt or opt for the richer but user-developed Access Form is “Are you going to be the sole user or will they be multiple users involved who are not so Microsoft Access savvy?“