Synchronizing Two Combo Boxes Using AfterUpdate
It has become rather popular to synchronize combo boxes (often called cascading combo boxes). The value selected from the first combo box updates the second combo box so that it contains only records related to the item selected in the first combo box. In the following example (ComboBox.accdb in the
chapter download), the row source of the first combo box (cboMainCategory) is set to tlkpCategory and the second combo box (cboSubCategory) needs the row source to display the values from tlkpSubCategory where the foreign key for each subcategory matches the values of the MainCategorylDs. This is a common function when there is a one-to-many relationship between two tables. In other words, each main category can have many subcategories (as one sales rep has many customers) but only certain subcategories are valid for a given main category.
One way to synchronize the combo boxes is to add the following snippet of code to the AfterUpdate event of the first combo box (cboMainCategory):
Private Sub cboMainCategory_AfterUpdate()
' bind data to the second combo box based on the value selected
If IsNull(Me.cboMainCategory) Then Me.cboSubCategory.RowSource = ""
Else
Me.cboSubCategory.RowSource = _
"SELECT SubCategoryID, SubCategoryName " _ & "FROM tlkpSubCategory " _
& "WHERE MainCategoryID = " & Me.cboMainCategory
End If
End Sub
While the row source of cboSubCategory is changed dynamically, the initial value of cboSubCategory can be set to equal nothing. This ensures that the user cannot select a value from the second combo box unless there is a value selected in the first combo box. If the user can scroll through records on the form, you'll also want to use this code in the form's On Current event to make sure that the row source of the second combo box is reset every time the record changes. For that, we used the following snippet:
|
Private Sub Form_Current() | |
|
' bind data to the second combo |
box based on the value selected |
|
' leave second combo box empty if there is nothing in the first combo box | |
|
If IsNull(Me.cboMainCategory) Then | |
|
Me.cboSubCategory.RowSource |
= "" |
|
Else | |
|
Me.cboSubCategory.RowSource |
= _ |
|
"SELECT SubCategoryID, |
SubCategoryName " _ |
|
& "FROM tlkpSubCategory |
11 |
|
& "WHERE MainCategoryID |
= " & Me.cboMainCategory |
|
End If | |
|
End Sub | |
Of course, there are a myriad other enhancements to add, such as having the drop-down lists display automatically. But for now, we're focused on the functionality of synchronizing combo boxes. Figure 10-13 shows the property configuration for the second combo box, cboSubCategory.
Although only the value of the subcategory is displayed, there are two fields listed in the Row Source. And if you open the Row Source query builder, you will see that all three fields of the table, tlkpSubCategory, are included in the query. You can understand why it is critical for the query to include the foreign key from tlkpCategory because that is the field that links the two combo boxes and provides the filter for the second combo box list. Comparing the SQL statement for the row source to the query behind it is a good way to learn what makes this work so that you can apply the principles elsewhere. You can also use this example to demonstrate that the value for the Column Count can be based on the Select statement in the Row Source (2 fields) or on the underlying query (3 fields). The critical part is to ensure that the bound columns and column widths match the field configuration.
i^g] Sync Combo Boxes X Property Sheet
^ Selection typ e: Combo Boh
Cascading Combo Boxes ^subcategory
Main Category promotional . |
_ Decimal Places Auto
Subcategory Icirtificates Tf Visible Yes I — Column Count 2 Column Widths Column Heads No List Rows 5 _|_List Width_2'
Figure 10-13
An alternative method for handling synchronized combo boxes is to code the row source of the second combo box to point to the value in the first combo box. If the name of the form with the combo boxes is frmMain, for example, instead of writing the preceding code, you could just make the Row Source of cboSubCategory the following:
Select SubCategoryID, SubCategoryName From tlkpSubCategories Where i MainCategoryID = Forms!frmMain!cboMainCategory
One of the easiest ways to create that type of Select statement is to use the Query Builder for the Row Source. After you have the correct properties, the trick is to make sure that the cboSubCategory field is refreshed with the new set of valid values. A simple Me.cboSubCategory.Requery statement will work. For this example, you would put that code in the cboMainCategory_AfterUpdate event and also in the Form_Current event.
The downside to this alternative method is that if you rename frmMain, you have to remember that you hard-coded the name in the cboSubCategory row source and that will have to be updated to use the form's new name.
Regardless of the method, you can use cascading combo boxes for a multitude of purposes. This example was simple, and it can easily be modified and incorporated into complex scenarios. Remember that you can add additional rows to the row source and use them to auto-fill text boxes or as links to other objects. You can also display additional fields in the combo box to assist users in selecting the correct record. Keep in mind that the List Width can be wider than the control width, so there is ample room to briefly display data.
Combo boxes are a great way to help speed up the data entry processes for your user. In Access 2007, Allow Value List Edits and List Items Edit Form enable you to eliminate some of the code that we used to capture data-entry errors. Of course, you can still use the Not In List event to trap user input and provide special handling. The combo box is definitely worth spending some time learning about so that you can leverage its versatility and benefits.
Property Sheet X
Selection type: Combo Box
I cboSubCategory
Property Sheet X
Selection type: Combo Box
I cboSubCategory
|
Control Source |
t 1 — 1 |
|
Row Source |
SELECT SubCategoryID, SubCategoryName FROM |
|
Row Source Type |
Table/Query |
|
Bound Column |
1 |
|
Limit To List |
Yes |
|
Allow Value List Edits |
No |
|
List Items Edit Form | |
Inherit Value List_No
Inherit Value List_No
Average user rating: 1 stars out of 3 votes
Post a comment