Access 2010: Selecting unique records across multiple tables with combo boxes

This quick example will show you how to make a set of filtered hierarchical combo box selectors for records across multiple tables within a tabbed navigation form. In other words, if your data is divided up across multiple tables joined by relationships, you can use this method to quickly find your record without having to use a series of multiple embedded sub forms to reach that record. This requires a little bit of VBA, as Access doesn’t offer any GUI features to achieve this.

At the end of this tutorial, you’ll have an Access from which looks like the image below, where the combo boxes are selecting filtered values out of separate tables. The combo boxes Trial and Individual will only show values which are filtered by the combo box selections above them. This is pretty useful if you have huge data sets which are too big to scroll through in just a single combo box.

Example Database

For this example we’ll use a small database comprised of three tables:

Table: Project

ID	Project
1	Project A
2	Project B

Table: Trial

ID	ProjectID	Trial
1	1	Trial 1
2	1	Trial 2
3	2	Trial 3
4	2	Trial 4

Table: Individual

ID	TrialID	Individual
1	1	1
2	1	2
3	2	3
4	2	4
5	3	5
6	3	6
7	4	7
8	4	8

The relationships between these tables are illustrated here:

The layout for the forms looks like this:

Where the three combo boxes are named project, trial, and individual, and they are located within the form Example_Navigation_Subform, which is the target form for tab 1 within the Navigation Form.

Transform the combo boxes into hierarchical selectors

Combo box: Project

• In Design mode, select the Project combo box, on the properties select: Data > Row Source > … to open the query editor for this combo box. Now use the following image as your query:

• Also, make sure to set your query to a Snapshot query instead of Dynaset. This will make your queries refresh much faster. The Dynaset query type is only necessary when you want the query to be able to pass edits down to its underlying table.

• Now that the query is set, go into Data > Bound Column and set it to 1. This will make the value returned by the combo box the ID value of the selected item.

• Next, go to Format > Column Count and set to 2. Two columns will now be displayed in the box, ID and Project

• And then Format > Column Widths and set to 0;1. This will cause the box to only display the second column from your query, so when you select a project name it will return the associated ID for that name.

Combo box: Trial

Now select the Trial combo box, and get to the query design: Properties > Data > Row Source > …

• Now set your query up like this:

• Again, set this query to a Snapshot .

• The important part of this is the Criteria for the ProjectID column. Use the code [Forms]![Navigation Form]![NavigationSubform].[Form]![Project] to tell the query to get the criteria value from the selected ID value in the Project combo box.

  • When working with tabbed navigation forms, notice that the ![NavigationSubform].[Form] part of the statement points towards the form which is currently opened in your tab. In other words, if you have a function which uses this line and you open the navigation form to a tab which does not contain the target item then run your function, Access will not be able to find it.

• Now set up your combo box properties for Trial similar to how the Project combo box was.

  • Data > Bound Column = 1.
  • Format > Column Count = 3.
  • Format > Column Widths = 0;1;0.

• Now the Trial combo box will only show selections that are filtered by the Project Combobox. However, you need to add some VBA here to make sure that when you edit the value in the Project combo box, the row source query in the Trial Combo box will automatically refresh.

  • Select the Project combo box
  • Properties > Event > After Update > … > Code Builder
  • Now enter the following VBA code so that it looks like image below: [Forms]![Navigation From]![NavigationSubform].[Form]![Trial].Requery

  

• Now when you select a new value in the Project combo box, the query which defines the row source values in the Trial combo box will automatically update with the new Project ID.

Combo box: Individual

Repeat the same steps above for the Individual combo box to add another level to your selector.

• select the Trial combo box, and get to the query design: Properties > Data > Row Source > …

• Now set your query up like the image below, where Criteria = [Forms]![Navigation Form]![NavigationSubform].[Form]![Trial].

• Again, set this query to a Snapshot .

• Now set up your combo box properties for Trial.

  • Data > Bound Column = 1.
  • Format > Column Count = 3.
  • Format > Column Widths = 0;1;0.

• Add some VBA to make it automatically refresh the query under the Individual combo box.

  • Select the Trial combo box
  • Properties > Event > After Update > … > Code Builder
  • Now enter the following VBA code so that it looks like image below: [Forms]![Navigation From]![NavigationSubform].[Form]![Individual].Requery

Create a query which is automatically updated by the combo box selectors

Now that your combo boxes are complete, you can make a small query which will automatically update when you select a new Individual from the combo box selectors.

Create the query

• Create a query called ResultsQuery with the following criteria for Individual ID: [Forms]![Navigation From]![NavigationSubform].[Form]![Individual].

• Now add this query to your tabbed form, with the properties:
  
  • Source Object: Query.ResultsQuery

• Finally, add some VBA code to the Individual combo box to make the results query automatically refresh when a new individual is selected.
  
  • Select the combo box Individual in design mode.
  • go to: Properties > Event > After Update > … > Code Builder
  • In the new function, enter: [Forms]![Navigation From]![NavigationSubform].[Form]![ResultsQuery].Requery

---

Now your done! Now you can use the combo boxes to select specific records which are spread across multiple tables, and the results of your selection will automatically be shown in the Results query. You can download the example accdb file here to get a better look at the code.