Early in my career as a programmer of databases that used Microsoft® Access, I learned how to put parameters in a query, simply by placing words inside square brackets in the criteria row in the query designer. For example, [Enter the required Dept Code]. Then, when you run the query within MS Access, it asks for data input, using the words as a prompt.

(article continues after sponsor spot)

Well, it did not take me long to want to make this simpler. For one, if you had 2 or more parameters they were asked for one at a time. How could I get them to both be asked for at the same time in 2 text boxes?

Then I saw an article on using a form for this.

Here is the style I have developed for myself.

• Create a form with fields for the various parameters you will need to pass to the query. These can include text boxes, combo boxes, list boxes, check boxes, etc. Anything that will allow a single value to be determined.
• Create a working query, complete with parameters. Test the query thoroughly.
• Change the parameters from the [enter the code] style to this style: [forms]![myform]![myfield]
• Put a button on the form that will run the query. You can even use the button wizard to do this.

That's it. And, no VBA coding was required.

The form method also allows you to do more thorough validity check, but using any methods available to forms, including some VBA.

As an added bonus, you can use the query without the form. It will revert to a regular parameter query, asking for parameters with the names of the fields.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved.