Enable users to find a record by selecting a value from a list
When you design a form in Access, you can create a list box or a combo box that can be used to find a record when a value is selected from the list. This makes it easier for users to quickly find existing records without having to type a value into the Find dialog box.
Notes:
-
For this procedure to work, the form must be bound to a table or a saved query. Step 5 of the procedure contains more information about what to do if this is not the case.
-
This procedure might create code that will run only when the database is granted trusted status. For more information, see the article Decide whether to trust a database.
Create the list box or combo box
-
Right-click the form in the Navigation Pane, and then click Design View.
-
On the Design tab, in the Controls group, ensure that Use Control Wizards is selected.
-
In the Controls group, click List Box or Combo Box.
-
On the form, click where you want to put the list box or combo box.
-
On the first page of the wizard, click Find a record on my form based on the value I selected in my combo box/list box, and then click Next.
The Find a record... option is not available.
If the option Find a record on my form based on the value I selected in my combo box/list box is not displayed, it is likely because the form is not bound to a table or a saved query. The form must be bound to an object that is available in the Navigation Pane under Tables or Queries.
Use the following procedure to check whether a form is bound to a table or saved query:
-
Click Cancel to close the wizard.
-
Right-click the list box or combo box that you created earlier, and then click Delete.
-
If the Property Sheet task pane is not displayed, press F4 to display it.
-
Select Form from the list at the top of the property sheet.
-
On the Data tab of the property sheet, look at the Record Source property box. This box must contain the name of a table or saved query before you can use the procedure described in this article. If the box is blank, you can select an existing table or query as the record source, or you can create a new query to serve as the record source. If the box contains a SELECT statement, you have the additional option of converting that statement to a saved query. Once you have completed one of these tasks, the next time you run the Combo Box/List Box wizard, it will display the Find a record... option.
Follow one of these procedures based on what you want to do:
-
Select an existing table or query as the record source
-
If you know that there is an existing table or query in your database that contains the records you want to find, use this procedure to bind the form to that object.
-
Note: If the Record Source property box contains a SELECT statement, following this procedure will delete that statement permanently. You might want to copy the statement to a text file or other word processing program in case you want to restore it.
-
Click in the Record Source property box, and then click the drop-down arrow that appears.
-
Select one of the available tables or queries. If a suitable table or query is not available, you must create one before you can continue. For more information, see the articles Create a table and add fields or Create a simple select query.
-
Save the form, and then repeat the procedure at the beginning of this article. The wizard should now display the Find a record on my form based on the value I selected in my combo box/list box option.
-
-
Create a new saved query, or convert a SELECT statement to a saved query
If the data you want to find is contained in more than one table, you will need to bind the form to a query that selects the data from those tables.
-
In the Record Source property box, click the Build button .
Access opens the Query Builder.
-
If the Record Source property box originally was empty, the Query Builder displays the Show Table dialog box, and you can begin creating a new query. For more information, see the article Create a simple select query. When you are finished building the query, proceed with step 2.
-
If the Record Source property box originally contained a SELECT statement, that query is displayed in the Query Builder. Proceed with step 2 to convert it to a saved query.
-
-
On the Design tab, in the Close group, click Save As.
-
In the Save As dialog box, type a name for the query in the Save 'Queryn' to: box.
-
Make sure that Query is selected in the As box, and then click OK.
-
On the Design tab, in the Close group, click Close.
-
Click Yes to save the changes made to the query and update the property.
Access displays the form in Design view, and changes the Record Source property so that the new saved query name is displayed.
-
Click Save on the Quick Access Toolbar.
-
Repeat the procedure at the beginning of this article. The wizard should now display the Find a record on my form based on the value I selected in my combo box/list box option.
-
-
-
-
Follow the directions in the wizard. On the last page of the wizard, click Finish to display the list box or combo box in Design view.