Video: Find a record on a form by selecting a value from a list

By using the Combo Box wizard, you can quickly create a drop-down list that lets you find a record on a form. For example, to find a record on a form that displays employee contact information, you might select an employee's name from a combo box at the top of the form, and then the form would display that employee's record.

Note:  The Combo Box wizard is disabled for web compatible forms, so the procedure in this video can only be used on client forms. If you're not sure what kind of form you're working with, look at its icon in the Navigation Pane. Web-compatible objects are distinguished by a small globe on the object's icon.

In the video

Add the combo box to the form

1. In the Navigation Pane, right-click the form and then click Layout View.

2. On the Design tab, in the Controls group, click the down arrow in the Controls gallery, and make sure the Use Control Wizards option is selected.

3. In the Controls gallery, click the Combo Box control, and then click in the form header where you want to place the combo box.
   
   If Access displays a Security Notice dialog box at this point, click Open to start the wizard.
   

4. On the first page of the Combo Box wizard, select Find a record on my form based on the value I selected in my combo box. If you do not see this option, see the section, If the Find a record... option is not available.

5. On the next page of the wizard, in the Available Fields list, select the field you want to display in the combo box, and use the arrow button to add it to the Selected Fields list.
   

Note:  You can add multiple fields to the combo box, but only the first field will be used for finding the record. Make sure that the field you choose uniquely identifies each record. If you choose a field such as Last Name, and there are multiple records with the same last name, Access will only find the first record that matches that last name, even if you have added First Name to the combo box. A good technique is to create a calculated field in the form's record source that concatenates two or more fields together, and then use that calculated field as the combo box value. For more information about creating calculated fields, see the See Also section of this article.

1. On the next page, the wizard shows how the list will look when it's created.

   • Make sure the Hide key column check box is selected.

   • Adjust column widths if necessary, and scroll down the list to make sure all the values fit.

2. On the next page of the wizard, type a label for the combo box, such as "Find contact".

3. Click Finish.
   
   Access places the combo box on the form. Make sure that it is displayed in the form header. If necessary, use the Insert, Split or Merge commands on the Arrange tab to create suitable cells in the form header for the new combo box.

1. On the Home tab, in the Views group, click Form View. Test the combo box to make sure it works the way you expect.

Top of Page

Sort the values in the combo box

In most cases, the values in the combo box will not be sorted. To add the sorting you want, use the following procedure:

1. On the Home tab, in the Views group, click Layout View.

2. Select the combo box.

3. If the Property Sheet is not already displayed, press F4 to display it.

4. On the All tab of the Property Sheet, In the Row Source property box, click the Build button .

Access opens the query builder. The field you chose in step 5 of the preceding procedure is displayed in its own column in the query grid.

1. In the Sort row of the field that you chose for the combo box, select the sort order you want (Ascending or Descending).

2. Click Close on the ribbon, and then click Yes to save the changes.

3. On the Home tab, in the Views group, click Form View. Test the combo box to make sure it works the way you expect.

Top of Page

If 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 on the first page of the wizard, 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:

1. Click Cancel to close the wizard.

2. Right-click the list box or combo box that you created earlier, and then click Delete.

3. If the Property Sheet is not already displayed, press F4 to display it.

4. Select Form from the list at the top of the property sheet.

5. 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.

6. Follow one of these procedures based on what you want to do:

   • Option 1: 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.

   1. Click in the Record Source property box, and then click the drop-down arrow that appears.

   2. 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 Introduction to tables or Introduction to queries.

   3. 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.

      • Option 2: 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.

   4. 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 Introduction to queries. 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.

   5. On the Design tab, in the Close group, click Save As.

   6. In the Save As dialog box, type a name for the query in the Save 'Queryn' to: box.

   7. Make sure that Query is selected in the As box, and then click OK.

   8. On the Design tab, in the Close group, click Close.

   9. 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.

   10. 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.

Top of Page