How to select Records in an Access Subform from a Combo Box

[Gordon Padwick]

A form contains controls, one or more of which can be other forms. A form that contains another form is known as a main form. A form contained by a main form is known as a subform. A subform itself can contain other forms, sometimes known as subsubforms. This article deals only with main forms and their immediate subforms.

The books about Access I’ve read contain some useful information about subforms, but don’t adequately cover the matter of selecting records in a subform from a combo box in a main form. This article attempts to remedy that situation.

After a brief introduction to forms in Access, the article describes how to use the Forms Wizard to create a form that contains a subform in which records in that subform can be accessed by using navigation buttons. The article then describes how to provide a combo box on the main form that can be used to access subform records.

The article assumes a moderate knowledge of using Access interactively and some familiarity with Visual Basic for Applications (VBA) code.
Note: This article was prepared using Access 2003. There could be some minor differences when using later Access versions.

Introducing Subforms
The Northwind sample database from Microsoft contains examples of forms that contain subforms. The Orders form in the Northwind database shown here is a typical example of a subform.

The record source for the Orders main form is the Orders Qry query that contains information about each order from the Orders table and has a one-to-many relationship between the Customers table and the Orders table.

The source object for the subform is the Orders Subform form, the record source of which is the Orders Details Extended query. That query contains information about the products included in each order from the Order Details table, product names from the Products table, and a calculated field that shows the extended price of each item (taking account of the quantity of each item and the specified discount). There is a one-to-many relationship between the Products table and the Order Details table.

When the Orders main form first opens, it shows information about the customer specified for the first order returned by the Orders Qry query; the subform shows information about each item in that order. You can click the navigation buttons at the bottom of the main form to display information about other orders. In the case of orders that contain more than the three items for which there is space in the subform, you can use the scroll bar that appears at the right side of the subform to scroll through the items.

One problem with this database as it stands is there’s no easy way to select information about a specific order. Suppose you need information about orders from B’s Beverages. You might expect to be able to enter that customer name in the Bill To combo box to access the relevant information. But that doesn’t happen. The only way to access a information about a specific order seems to be to use the navigation buttons at the bottom of the form to step through the order records until you reach the one you want.

This article deals with that problem, showing how to use a combo box in a main form to easily access detailed information in a subform. The article uses a much simpler database than Northwind in order to focus on the relevant topics.

More About Subforms
There are significant differences between a form that is opened directly and a form that is opened as a subform object in a main form. You don’t need to be concerned with these differences in the cases of simple forms and their subforms, but you must understand them when you venture into more complex cases.
Consider the Orders Subform form in the Northwind database. Open that form in design view directly from the database window, right click the button at the left end of the horizontal ruler at the top of the design view of the Orders form to display a context menu and choose Properties in that menu to display the form’s properties. With the Data tab selected, you’ll see the Record Source for the form is Orders Qry. With the Event tab selected, you’ll see a long list of available events.

Now look at the properties of the subform in the Orders main form. With the design view of the Orders form still displayed, right-click the button at the left end of the ruler at the top of the subform to display the subform’s properties. With the Data tab selected, you’ll see a different set of properties; instead of Record Source, there is Source Object which, in this case, is Orders Subform. The Link Child Fields and List Master Fields properties control how the subform is linked to the main form. With the Event tab selected, you’ll see a different set of available events; instead of the long list of available events, there are only two, On Enter and On Exit, neither of which were in the list of available events when you opened the form directly.

Creating a Simple Form that Contains a Subform
Start by creating a database that contains two tables, one with data for a main form and the other with data for a subform. The Publishers example that follows contains a table named Publisher that contains the names of book publishers, and a table named Book that contains book titles and author names. The intent is to be able to identify books from each publisher.

Create the Publisher table with two fields: PublisherID (primary key) and PublisherName (text). The primary key is essential for this table. Enter a few names of publishers into the table. As you enter publisher names, Access automatically provides a primary key for each publisher.
Note: In general, avoid using names that include spaces for tables, fields, queries, and other objects. Although spaces are acceptable, they are likely to give you trouble while you develop a database.

Create the Book table with four fields: BookID (primary key), Title (text), Author (text), and PublishedID (long integer). The primary key is not essential for this table, but it is good practice to provide a primary key. The PublisherID field acts as a foreign key in the relationship to be established between the two tables. Enter information (the book title and the names of the authors) about several books from the publishers named in the Publisher table making sure at least some of the publishers have two or more books listed.
Note: In this example, the same name is used for the primary key in the first table and the foreign key in the second table. Although this is common practice, it is not required. The primary key and foreign keys can have different names.

After creating and saving the two tables and entering some data, close any open table and then choose Tools | Relationships to open the Relationships window. Display the Book and Publisher tables in that window. Drag from the PublisherID field in the Publisher table to the PublisherID field in the Book table to establish a relationship between those tables. In the Edit Relationships window that opens automatically, verify that the correct fields are selected and that the relationship type is One-To-Many. Click the Enforce Referential Integrity check box to select it, and then click Create to create the relationship and close the Edit Relationships window. The Relationships window should now show the established relationship between the two windows. Close the Relationships window.

Now you are ready to create a master form and its subform. Do this:

1. In the database window, choose Forms and click New to open the New Form window.
2. Select Form Wizard and click OK to display that wizard.
3. Open the Tables/Queries list to display the available tables and queries.
4. Select the table or query you want to use as the record source for the main form, the Publisher table in this case.
5. Move the names of the available fields that you want to have on the main form to the Selected Fields column. In this case, click >> to move both available fields.
6. Reopen the Tables/Queries list and select the table or query you want to use as the record source for the form that will be the source object for the subform, the Book table in this case.
7. Move the names of the available fields that you want to have on the subform to the Selected Fields column. In this case, double-click the Title, Author, and PublisherID fields to move them—you don’t need the BookID field at this time.
8. Click Next to display the next Form Wizard window.
9. Choose how you want to view the data. In this case, select Publisher in order to make that the main form. Near the bottom of the window, choose Form with subform(s).
10. Click Next to display the next Form Wizard window.
11. Choose a layout for the forms. In this case, choose Tabular.
12. Click Next to display the next Form Wizard window.
13. Choose a style for the forms. In this case, choose Standard.
14. Click Next to display the next Form Wizard window.
15. If you want to have names for your forms that are different from those suggested, replace the suggested names. The names suggested by the wizard are likely to include spaces; remove those spaces or replace them with underscores.
16. Click Finish to accept all the choices you have made in the Forms Wizard and close the wizard. Access displays the form and subform you have created, as shown here.

You can click the navigation buttons at the bottom of the main window to select publishers, one after the other. As each publisher is selected, the books from that publisher are displayed in the subform. The forms wizard creates a subform that has space for only two book records. In cases in which there are more than two books for a publisher, you can use the vertical scroll bar at the right side of the subform to scroll through the books or you can use the navigation buttons immediately below the subform to access book records that are not initially displayed.

Look carefully at the form, as shown in the preceding illustration. A part of a field is displayed at the right side of the subform. Use the horizontal scroll bar at the bottom of the subform to scroll to the right. You’ll see that the field partially displayed is the PublisherID field. It’s there because you specified that field in the forms wizard (step 7 of the preceding procedure). That field has to be included because the subform needs to be aware of it in order to link to the main form. However, you probably don’t want that field displayed in the subform because the PublisherID field is already displayed in the main form.

To correct this problem, close the main form, select the Book Subform form in the Database window, and open that form in design view. Select the PublisherID label and press Delete to erase it. Select the PublisherID text box and press Delete to erase it. Save and close the Book Subform form. Reopen the Publisher form in form view. Now the subform does not contain the partially displayed PublisherID field, as shown here.

Before proceeding further, look at the subform’s properties. With the main form, including its subform, displayed in design view, right-click the button at the left end of the subform’s horizontal ruler to display the context menu. Choose Properties in the context menu to display the Properties window. Select the Data tab to display the properties shown here.

The important properties to notice are Link Child Fields and Link Master Fields. To display more information about these properties, click on one of the field values; a button with an ellipsis (three dots) as its caption appears at the right of the value. Click that button to display the Subform Field Linker window such as this.

The Link Child Fields property identifies the field in the subform that links to the main form. The Link Master Fields property identifies the field in the main form that links to the subform. In this example, both fields have the same value because the primary key field in the table that is the record source for the main form has the same name as the foreign key in the table that is the record source for the subform.

If other fields are available for use as the Link Child Fields or Link Master Fields properties (they are not in this case), you can change the values either in the Properties window or the Subform Field Linker window.
You can create a form containing a subform without using the Link Child Fields and Link Master Fields properties, as subsequently described.
Creating a Form Containing a Subform Without Using Link Child Fields and Link Master Fields Properties.

If you want to save the preceding example, use Windows Explorer to make a copy of the database file, giving the copy an appropriate name. Use the copy as you proceed.

Open the main form (Publisher) in design view, right-click the button at the left end of the form’s horizontal ruler, and choose Properties in the context menu to display the form’s properties in the Properties window. Expand the list of objects at the top of the Properties window and select Book Subform to display the subform’s properties. In the Data tab, delete the values of the Link Child Fields and Link Master Fields properties. Save the form.

Switch to form view. Now, when you use the navigation buttons at the bottom of the main form, the selected publisher changes but nothing happens in the subform. When you scroll down the subform or click that form’s navigation buttons, you’ll see all the available books, without regard to the selected publisher. This behavior is caused by there being no linkage between the two forms—because you have removed the values of the Link Child Fields and Link Master Fields properties.

To create a new way to display the correct books in the subform, close the Publisher form, then create a new query. That query should contain the fields to be displayed in the subform and the field to be used as a foreign key. Define a criterion for the foreign key field that says the value of that field must correspond to the value of the primary key in the main form, as in the Query By Example (QBE) window shown here.

The corresponding SQL code is:

SELECT Book.Title, Book.Author, Book.PublisherID
FROM Book
WHERE (((Book.PublisherID)=[Forms]![Publisher]![PublisherID]));

Change to the Datasheet view of the query and you’ll see this dialog box:

because the Publisher form is not available to provide a value for the specified criterion. In the text box, enter one of the values for the PublisherID in the Publisher table and click OK. The Datasheet view of the query should now correctly display books from the publisher corresponding to the PublisherID value you entered. This verifies that the new query is working correctly.

Save the new query, giving it a name such as BooksCriterion, and then close the query. From the Database window, display the subform in design view. Right-click the button at the left end of the horizontal ruler to display the context menu, and click Properties in that menu to display the Properties window. Choose the Data tab in that window. Change the value of the Record Source property to the name of the query you just created, such as BooksCriterion. Save and close the query.

Open the Publisher form in form view. The form should open with the first publisher selected and with the subform displaying titles of the books from that publisher. Use the navigation buttons at the bottom of the main form to select various publishers. With each publisher selected, the subform should display the titles of books from that publisher. If that doesn’t happen, one possibility is that the criterion defined in the new query is incorrect. For example, the field name in the criterion condition might not match the actual field name in the main form.

You might be wondering what is the purpose of specifying the linkage between the main form and the subform in this manner when it only achieves the same result as the initial example that used the Link Child Fields and Link Master Fields properties to define the linkage. You would likely not use this technique to define functionality that the forms wizard can create automatically. However, the method of creating a linkage between a main form and subform just described demonstrates that more flexibility is available to you than the Link Child Fields and Link Master Fields pair of properties can provide. Subsequently in this article, you’ll see how to do something that can’t be done using the Link Child Fields and Link Master Fields properties.

Improving Form Layout and Performance
You don’t have to accept the layout automatically created by the forms wizard. To change the layout of the main form, display that form in design view and employ the usual techniques to make such changes as the size of the form, the size and position of controls on the form, and the fonts in which text is displayed.
To make changes to the layout of the subform, first select the subform, and then select controls on that subform. Alternatively, close the main form, then select the subform in the database window and open it in design view. There, you can make changes in the usual manner and save the form. Those changes will subsequently be in effect when you reopen the main form.

When you create a form such as Publishers and subform such as BookSubform using tables as their record sources, the names of publishers are displayed in the main form in the order they occur in the table that is the form’s record source. Similarly, the order in which titles of books are displayed in the subform is the order in which those books are listed in the table that is the subform’s record source. You probably would prefer to have publisher names and book titles listed in alphabetical order.

To list books in alphabetical order, open the query you just created. Select design view and set the Sort property of Title to Ascending. Save and close the query.

To list publishers in alphabetical order, create a new query that contains the fields from the Publishers table to be used in the main form, PublisherName and PublisherID in this case. Set the sort order of PublisherName to Ascending. Save and close the query. Open the main form, Publisher, in design view and display its properties. Change the form’s Record Source to the name of the query you just created.

When you subsequently display the Publisher form in form view and use the navigation buttons to move from one publisher to the next, you’ll see the publishers’ names in alphabetical order. The books listed for each publisher will also be in alphabetical order.

One remaining problem isn’t obvious when you’re working with tables that contain only a few items, but immediately becomes evident when you have large tables. Suppose the Publishers table lists hundreds or even thousands of publishers. It would be very tedious to use navigation buttons to find a specific publisher. This is the problem I came across while creating a database that links thousands of items.

One solution to the problem just described is to use a combo box in the main form to select the detailed information to be displayed in the subform. If you set the Auto Expand property of the combo box to yes, you can simply enter the first few characters of an item’s value into the combo box and the box will find the first item that starts with those characters.

Using a Combo Box to Select Items in the Main Form
The forms wizard doesn’t provide a way to create a main form that has a combo box from which items to be displayed in a subform can be selected. A different approach is necessary.

At this point, you might want to save the database, then use Windows Explorer to create another copy of the database you created if you followed the steps described in the first part of this article. The subsequent text assumes you have done that.

In the new version of the database, open the Publisher form in design view. Delete the PublisherID field and its associated label. Select the PublisherName field, choose Format | Change To | Combo Box. Give the combo box an appropriate name such as SelectPublisher.

The next steps define appropriate properties for the combo box.

1. Right-click on the combo box to display its context menu.
2. Select Properties to display the properties window for the combo box.
3. Select the Format tab.
4. Set the Column Count property to 2 (because the combo box should contain the PublisherID and Publisher fields).
5. Set the Column Widths property to 0;5 (to hide the first column).
6. Select the Data tab.
7. Set the Bound Column property to 1 (that’s the second column that contains the name of the publisher).
8. Delete the value of the Control Source.
9. Click the Row Source property, then click the triangle that appears at the right end of the value space to see a list of available sources. Select the name of the query on which the main form is based.
10. Set other format properties to match your preferences.
11. Save and close the form.
12. Revise the existing BooksCriterion query. That query should contain the fields you want to display on the subform and the field that links the subform to the main form, as shown here.

The corresponding SQL code is:

SELECT Book.Title, Book.Author, Book.PublisherID
FROM Book
WHERE (((Book.PublisherID)=SelectedPublisherID()));

Notice that the criterion for the PublisherID field is now specified as a function. Select Modules in the Database window. Select an existing code module or create a new one. Enter this function:

Function SelectedPublisherID()
    SelectedPublisherID = Forms!Publisher!SelectPublisher.Column(0)
End Function

The value returned by that function is derived from the first column of the combo box in the Publisher form, the column that contains PublisherID values. Save the function.

When you select a publisher name in the combo box on the main form, you need to update the subform so that it uses the updated query. Do this by creating a procedure that responds to the combo box’s AfterUpdate event. With the main form open in design view, display the Properties window for the combo box, and select the Event tab. Click on After Update; a button with an ellipsis (three dots) appears at the right. Click the ellipsis button; Access displays the Choose Builder dialog box. Select Code Builder in that dialog box and click OK; Access displays the code window in which a skeleton procedure has been automatically created. That skeleton procedure consists of:

Private Sub SelectPublisher_AfterUpdate(Cancel As Integer)
 
End Sub

Add a single statement into the skeleton so that it becomes:

Private Sub SelectPublisher_AfterUpdate(Cancel As Integer)
    BookSubform.Requery
End Sub

Note: VBA requires that the subform name does not contain a space. If the name contains a space, such as Book Subform, VBA code requires the name does not contain a space. If the subform name contains a space, replace that space with an underscore.

When you execute the Requery method on an object, the subform in this case, Access reruns the query on which the object is based, thus updating the object.
Save the database, then close the code window.

Select Forms in the Database window and display the Publisher form in form view. You should now be able to select a publisher name in the combo box and see information about books from that publisher in the subform.

You no longer need navigation buttons, record selectors, and dividing lines in the main form, so you can delete them. To simplify and improve the appearance of the main form, switch to the design view of the form, display the Properties window, select the Format tab, and set the Record Selectors, Navigation Buttons, and Dividing Lines properties to No.

Save the form and return to the form view and use the combo box to select a publisher. The form you see should be similar to that shown here.

The navigation buttons that remain in this form apply to the subform; they can be used to access any records in that subform that are not initially displayed.
I hope the information presented in this article gives you a more complete understanding of subforms than you might have previously gleaned from other sources and that you now know how to use a combo box in a main form to access records in a subform.

Gordon Padwick