Ensure more accurate results in your Access database by limiting combo box choices

 

by Julie Duncan

Application:

Microsoft Access2002/2003/2013

Operating System:

Microsoft Windows

Download:

http://download.elijournals.com/office/201602/teachers.zip

Nothing strikes fear in the heart of those responsible for data accuracy more than having to open that data up to others’ use. Thankfully, Microsoft Office includes several tools for limiting—or at least controlling—what multiple users can (or can’t) do to a document. In Access, combo boxes decrease the opportunities for data errors because users click options with a mouse rather than by manually entering information. Still, users can mistakenly click the wrong option. Fortunately, you can further reduce errors by limiting combo box choices based on previous selections.

To limit combo box options in our database, we’ll:

  • Create a form with two combo boxes for user input.
  • Connect the combo box controls so the combo box choices in the second box depend on the option chosen in the first combo box.
  • Create a macro to refresh the data in the second combo box every time the user chooses another option in the first combo box.

 

As a user makes selections in successive combo boxes in your database, often the later selections are limited based on the prior selections. Say, for instance, you are looking up teachers in a database. Once you’ve indicated that you are looking for a first-grade teacher, there’s no need to display the Kindergarten and third-grade teachers as well. Limiting the options in the database such that you can only choose from the first-grade teachers will make your choice much simpler. We’ll show you how to whittle down the combo box choices for users as they go, as shown in Figure A.

A:

ime17c04A1ime17c04A2

Our list of teachers changes based on the grade the user selects.

Create a form

We’ll create a basic form with two combo boxes. One will allow the user to select the grade a teacher teaches, and the next combo box will allow the user to select the teacher’s name. The second combo box will display only the names of the teachers in the grade chosen in the first combo box.

Note: To follow along with our example, click on the URL given at the beginning of this article to download the sample database, teachers.mdb.

To create a new form and add a combo box:

  1. Open the sample database in Access. Select the Create tab, and, in the Forms area, click on Form Design to create a new form in Design View. (In earlier versions of Access, click Forms in the Database window’s Objects pane and double-click on Create Form In Design View.)
  2. In the Form Design Tools context tabs, choose the Design tab. In the Controls area, click on Combo Box, as shown in Figure B.
  3. Click in the left side of the new form’s design window and click on Open when the Security dialog box warning pops up. The Combo Box Wizard opens.
  4. Click on the I Want The Combo Box To Look Up The Values In A Table Or Query option button (if necessary) and click Next.
  5. Select Table: tblGrades from the list box and click Next.
  6. Click the double arrow button to select both the GradeID and GradeName fields to the Selected Fields list box. Click Next.
  7. Choose GradeName from the first dropdown list and click Next twice.
  8. Type Grade: in the text box and click Finish.

 

B:

ime17c04B_withMarkup

Use the Controls area of the Design tab to insert a combo box.

To name and format your combo box:

  1. From the Form Design Tools context tabs, choose the Design tab. In the Tools area, click on Property Sheet.
  2. Select the All tab if necessary.
  3. Click in the Name property’s text box and replace the default text withcboGradeNumber. Close the Property Sheet.
  4. Click and drag your combo box and label, if necessary, to ensure that the text displays properly and that the two items don’t overlap.

 

To add a second combo box:

  1. Click the Combo Box button on the Design tab again.
  2. Click on the right side of the form design window to begin the Wizard. Click Next.
  3. Select Table: tblTeachers from the list box, then click Next.
  4. Add the TeacherLast field to the Selected Fields list box by clicking on it and th[...]
 
Join NowClose
Return to the ExcelSkillsSociety's homepage