Create a form that collects parameters. Create a form that collects parameters for a report. You can use criteria in a parameter query in Access to restrict the set of records that the query returns. You may find the dialog boxes that are provided by a parameter query to be insufficient for your purposes. In such cases, you can create a form that better meets your parameter collection needs. This article explains how to create a form that collects query and report parameters.
This article assumes that you are familiar with creating queries and defining parameters in queries. At a minimum, you should be familiar with creating a select query before you continue.
This article provides examples of using parameters in queries. It does not provide a comprehensive reference for specifying criteria. For more information about how to create a select query, see the article Create a simple select query. For more information about queries in general, see the article Introduction to queries. For more information defining parameters in queries, see the article Use parameters to ask for input when running a query.
For more information about how to specify criteria in queries, see the article Examples of query criteria. Using a parameter in a query is as easy as creating a query that uses criteria. You can design a query to prompt you for one piece of information, such as a part number, or for more than one piece of information, such as two dates. For each parameter, a parameter query displays a separate dialog box that prompts you for a value for that parameter. In the Criteria row of a field for which you want a parameter applied, type the text that you want the parameter dialog box to display, enclosed in square brackets, for example:.
When you run the parameter query, the prompt appears in a dialog box without the square brackets. Note: A separate dialog box appears for each parameter prompt. In the second example, two dialog boxes appear: one for Start Date and one for End Date. You can use the preceding steps to add a parameter to any one of the following types of queries: Select, Crosstab, Append, Make-table, or Update.
If a WHERE clause already exists, check to see whether the fields for which you want to use a parameter prompt are already in the clause, and if not, add them.
You can also specify what type of data a parameter should accept. When you specify the data type that a parameter should accept, users see a more helpful error message if they enter the wrong type of data, such as entering text when currency is expected. Note: If a parameter is configured to accept text data, any input is interpreted as text, and no error message is displayed. In the Query Parameters dialog box, in the Parameter column, type the prompt for each parameter for which you want to specify the data type.
Make sure that each parameter matches the prompt that you use in the Criteria row of the query design grid. Although parameter queries feature a built-in dialog box that collects parameters, they provide only basic functionality. By using a form to collect parameters, you gain the following features:. The ability to provide a combo box or list box for parameter collection, which lets you pick from a list of available data values.
The following video shows how you can create a simple form to collect parameters for a query instead of using the dialog boxes normally associated with parameter queries. There are several ways you could approach this scenario, but we'll show just one technique using mostly macros.
Follow these steps to create a form that collects parameters for a report. Step 1: Create a form that accepts input. Step 2: Create a code module to check whether the parameter form is already loaded.
The following example expects two parameters to be provided and then applies the criteria to records in the Orders table:. This example requires the user to provide a job title and then uses that job title as the criteria for the query. Skip to main content. This browser is no longer supported. Download Microsoft Edge More info. Contents Exit focus mode. Please rate your experience Yes No.
Here is my form: When the user selects a species, Access uses the number generated by the option group called "speciesSelection" to lookup the species name in a table, and it works. QueryDefs "qryClinicalObservations" 'Supply the parameter value qdf. OpenRecordset End Sub I created my query using the wizard.
Here is a snapshot: In the criteria section, I can manually enter a species when prompted e. Is there an obvious mistake? It seems that Combo12 is not recognized. EDIT: Here is my new code. QueryDefs "qryClinicalObservations" 'Supply the parameter value qdf! Improve this question. Johnathan Johnathan 1, 3 3 gold badges 21 21 silver badges 29 29 bronze badges.
Looks like your combobox has 2 columns, the first one invisible, but your query returns only one column, so the visible combobox column is empty. Add a comment. Active Oldest Votes. Since that is an object assignment, use the Set keyword.
Set Forms! OpenRecordset If Combo12 and speciesSelection are both contained in the same form inputForm , you can use this instead Set Me! Improve this answer. HansUp HansUp Thank you very much for your answer!
I just realized that Combo 12 is in a subform called observationsSubform. Combo12 is now recognized, the drop-down menu works, but I can't see any entries. So, I'm almost there?
0コメント