Why does Access want me to enter a parameter value?
Sometimes when you open an Access object (such as a table, query, form, or report), Access displays the Enter Parameter Value dialog box. Access displays this dialog box when you open an object that contains an identifier or expression that Access can't interpret.
In some cases, this is the behavior that you want. For example, the creator of the database might have created a query that lets you enter information every time that the query runs, such as a start date or an employee ID number. Such a prompt might resemble this:
However, in other cases you don't want this dialog box to appear. For example, you might have made some changes to the design of your database. Now, when you open an object, Access displays the dialog box unexpectedly with a message you don't understand.
This article provides you with procedures to investigate why Access may be requesting a parameter value and how you can stop the requests.
Note: This article doesn't apply to Access web apps – the type of database you design with Access and publish online to Office 365 or SharePoint. See Create an Access app for more information.
Stop the request for a parameter value
To stop the Enter Parameter Value dialog box from appearing, you must inspect any expressions that are associated with the object that you're working with, and find the identifier that is causing the dialog box to appear. Then, you must correct the identifier or the syntax of the expression that contains the identifier.
Find links to more information about expression syntax in the See Also section.
When the unwanted Enter Parameter Value dialog box appears, note the identifier or expression that's listed in the dialog box. For example, "SomeIdentifier" as shown in the following screenshot.
Then, select Cancel and continue with one of the following procedures, depending on the type of object that you're opening when the dialog box appears. The procedures provide general guidelines for inspecting the expressions in different types of objects. However, the specific changes that you must make depend on the structure of your database.
Inspect expressions in a query
Typographical errors in queries are a frequent cause of unwanted parameter prompts. As mentioned earlier, when a query is designed to ask for a parameter value when it is run, the Enter Parameter Value dialog box appears by design. However, if you're sure that the query shouldn't be asking for a parameter value, use this procedure to inspect the query for incorrect expressions.
-
Right-click the query in the Navigation Pane, and then click Design View.
-
Inspect the identifiers and expressions in the Field row and in the Criteria rows, and determine whether any of the text matches the text that was displayed in the Enter Parameter Value dialog box. In particular, if one of the cells in the Field row contains a value such as Expr1: [identifier], this might be the source of the parameter prompt.
Find links to more information about how to create parameter queries in the See Also section.
Inspect expressions in a report
If the Enter Parameter Value dialog box appears when you open a report, follow these steps to inspect the report's properties:
-
Right-click the report in the Navigation Pane, and then click Design View.
Note: Before continuing, determine if any of the controls on the report display a green triangle in their upper-left corner. The triangle means that Access can't evaluate an identifier or expression in the Control Source property of that control. If any controls display the green triangle, pay particular attention to those controls as you continue through these steps.
-
If the Property Sheet task pane isn't displayed, press F4 to display it.
-
In the Property Sheet task pane, select the All tab.
-
Select a control that displays data on the report (such as a text box, check box, or combo box). If any controls display the green triangle mentioned in step 1, click one of those controls first.
-
In the Property Sheet task pane, inspect the Control Source property for the identifier that was displayed in the Enter Parameter Value dialog box, and then modify the expression if necessary.
-
Repeat steps 4 and 5 for other controls on the report, until you find the expression that is causing the problem.
-
If you still can't find the problem, check for any incorrect expressions in the Group, Sort, and Total pane:
-
If the Group, Sort, and Total pane isn't displayed, on the Design tab, in the Grouping & Totals group, select Group & Sort.
-
If "Group by expression" or "Sort by expression" is displayed on a line in the Group, Sort, and Total pane, select the word "expression" to open the Expression Builder, where you can examine the expression and modify it if necessary.
-
Inspect expressions in the underlying query of a form
If the Enter Parameter Value dialog box appears every time that you open a form, the incorrect expression might be in the underlying query. Examine the underlying query to find the incorrect expression.
-
Right-click the form in the Navigation Pane, and then select Design View.
-
If the Property Sheet task pane isn't displayed, press F4 to display it.
-
Make sure that Form is selected in the list at the top of the Property Sheet task pane, and then select the All tab.
-
Examine the Record Source property. If it contains the name of a query, or an SQL statement, then one of the identifiers in the statement may be incorrect, and causing the Enter Parameter Value dialog box to appear. Select the Record Source property box, and then click the Build button
. -
Use the procedure in the section Inspect expressions in a query to find the incorrect expression.
Important: To save your changes to the query when you're finished, close the query, and then save the form before switching back to form view. Otherwise, any changes that you made to the query will be lost.
Inspect expressions in a combo box or list box on the form
An incorrect expression in the Row Source property of a combo box or list box control can cause the Enter Parameter Value dialog box to appear. In some cases, the dialog box doesn't appear until you try to use the control. Use this procedure to inspect the Row Source property of the control:
-
Right-click the form in the Navigation Pane, and then select Design View.
-
Click the combo box or list box that you want to inspect.
-
If the Property Sheet task pane isn't displayed, press F4 to display it.
-
Select the Data tab, and then examine the Row Source property and determine whether any of the text matches the text in the Enter Parameter Value dialog box.
Inspect expressions in a table
If Access displays the Enter Parameter Value dialog box every time that you open a table, the incorrect expression is most likely in the Row Source property of a Lookup field in that table.
-
Right-click the table in the Navigation Pane, and then select Design View.
-
To determine if a field is a Lookup field, select the field name, and then under Field Properties, select the Lookup tab. If the tab contains a Row Source property box, then the field is a Lookup field. Examine the Row Source property. If it contains an SQL statement, then one of the identifiers in the statement may be incorrect, and causing the Enter Parameter Value dialog box to appear.
Note: A table can have multiple Lookup fields, so be sure to check the Lookup tab for each field.
No comments:
Post a Comment