Refresh Dependant Combo Box Contents

Introduction.

Creating a combo box on a data entry or editing screen is straightforward. Click the Combo Box tool in the toolbox, follow the wizard to select the required fields from the source table or query, choose the target field on the form, and you’re done. Well, almost—some fine-tuning on the combo box’s property sheet is often needed. You may need to adjust column widths, set the Total List Width, configure the List Rows property, enable 'Limit to List', and handle the On Not in List event to prevent entry of values not defined in the combo box.

In some cases, a form may have multiple combo boxes, with the contents of one depending on the selection made in another. In such situations, it’s more effective to filter the second combo box so that it only displays items related to the current selection in the first, rather than showing all possible items. This way, the user can choose from a shorter, more relevant list instead of scrolling through an unnecessarily long one.

A few lines of code in the On Click event procedure of the first combo box can achieve this, saving the user time and effort, and making the form more efficient and user-friendly.

Preparing for the Test Run.

We will use three tables from the Northwind.mdb sample database for our test run. This database is a frequent go-to for ready-made data in our examples. If you are unsure where to locate it on your PC, refer to the Page: Saving Data on Form Not in Table for location details.

Note: For new Microsoft Access users, this database offers plenty of sample tutorials and materials to help you learn the basics of database design. Once you outgrow it—or simply want a fresh challenge—feel free to explore the examples and guides on this site. I aim to keep you engaged with practical tips and projects for a long time.

Import the following tables from the Northwind sample database into your current project:

• Categories

• Products

• Order Details

Next, open the Order Details table, select Form from the Insert menu, choose AutoForm: Columnar from the wizard, and click OK. Save the form as Order Details.

Before making any modifications, let’s review the form’s current layout. Open it in Form View. You’ll notice there is already a combo box for the ProductID field.

Click the combo box, scroll through the list, and try reading some of the items. If you’re anything like me, you may find that many product names are unfamiliar, particularly those containing accented characters or diacritical marks. One thing is certain, however: they belong to different Categories (which we’ve already imported) but are all mixed together in a single list.

Change the Form Design

1. We will create an additional combo box to present the data in a more organized way. Let’s proceed with the design changes to the form.

2. If the form is currently open in Normal View, switch to Design View from the View menu.
   If you prefer your form to have a heading, expand the Form Header section, insert a label, change its caption to Order Details, and adjust the font size to make it stand out as a proper heading.

3. Next, select all the controls and labels—except the OrderID control and its label—and drag them downward to create space for a new combo box above the ProductID combo box.

4. To select the controls: click on an empty area of the form, hold down the mouse button, and drag over all the controls except the OrderID field and label. Once you’re sure all the required controls are selected, release the mouse button. Hover the pointer over the selected group until it changes to a hand icon, then click and drag it downward to create space for the new field and label. This new combo box will display data from the Categories table.

5. If the Toolbox is not visible, enable it from the View menu. Ensure the Control Wizards button (in the Toolbox) is activated.

   • Click the Combo Box tool in the Toolbox and draw a combo box above the ProductID combo box, to match the earlier one's size.

   • From the displayed list, select the Categories table and click Next.

   • Click the >> button to select both fields from the Categories table, then click Next twice.

   • On the final screen, ensure that the option 'Remember the value for later use' (in our code) is selected, then click Finish.

   

6. We do not need to create a new field in the Order Details table to store the value selected from this combo box. Instead, the Product Category Code entered by the user in this Unbound combo box will be used to filter the contents of the Products combo box. Only the item chosen from the Products combo box will be stored in the Order Details table.

   1. Select the new combo box, open the Property Sheet (View → Properties), and set the Name property to cboCat.

   2. Adjust the label’s size so it matches the other labels on the form.

   3. Open the form’s code module (View → Code).

   The Form Class Module VBA Code

7. Copy and paste the following code into the VBA Module:

   Private Sub cboCat_Click()
   Dim xsql0 As String, xsql2 As String, xsql As String
   Dim crit As String
   
   xsql0 = "SELECT DISTINCTROW [ProductID], " & "[ProductName] FROM Products WHERE ("
   xsql2 = " ORDER BY [ProductName];"
   crit = "[CategoryID] = " & cboCat & ") "
   xsql = xsql0 & crit & xsql2
   
   Me.ProductID.RowSource = xsql
   Me.ProductID.Requery
   End Sub

8. Save and close the VBA Window (File -> Close and Return to Microsoft Access). Save the Order Details Form with the changes.
9. Open the Form in the normal view. Click and select an item from the new Combo box, say Meat/Poultry.
10. Now, click the Products Combo box. The List of items appearing there belongs to the Meat/Poultry Category only. Experiment with other Categories of items also.

How it works.

Although the combo box we created displays only a single column (the description), the Combo Box Wizard actually selected two fields — CategoryID and CategoryName — from the Categories table. If you check the Column Widths property in the property sheets of both combo boxes, you will notice that the width of the first column is set to 0. This hides the CategoryID value while still allowing us to select it by clicking the description. Showing the description is preferable, as it is easier for the user to understand which item is being selected.

When a selection is made in the combo box, the CategoryID corresponding to that item is stored in the control’s value. We then use this CategoryID to filter the related products from the Products table by constructing a SQL string with CategoryID as the criterion. This SQL string becomes the Row Source for the Products combo box.

If you open the Products table and examine its data, you will notice that the category description is also included there. Clicking one of the category fields reveals that it is itself a combo box. This combo box — created during table design — uses the Categories table as its source but does not perform any filtering.

To confirm, open the Products table in Design View, select the CategoryID field, and open the Lookup tab in the property sheet. You will see similar settings to those in our form’s combo box: the Row Source is the Categories table, and the first column (the numeric code) is hidden by setting its width to 0, allowing only the category name to be displayed.

Note: Suggestions for improvements or alternative approaches to achieve the same results are welcome.

• Progress Bar on Form
• Progress Meter
• Keyboard Shortcuts
• Find or Filter Data on Form
• Who is Online