After Update Synchronizing Two Combo Boxes

The AfterUpdate property is triggered after the form or control is updated. If you have not read about the BeforeUpdate property, it is important to know that the BeforeUpdate property must trigger and pass, then the record is saved (which may have issues due to constraints contained within the database itself), and only then can the AfterUpdate property trigger. But, after some values are updated, you may need to react to the newly saved value.

In this example, a common problem with combo boxes is addressed. There are times when you display the values in one combo box based on a value selected in another combo box.

Private Sub cboCategory_AfterUpdate()

'Create a new rowsource for the Product Combo Box 'after a category is selected 'Verify that the user selected a category If Len(cboCategory) > 0 Then With cboProduct

'Set the Product Rowsource to find 'only products in the selected category .RowSource = _

"SELECT ProductID, ProductName " & _ "FROM Products WHERE CategoryID = " & cboCategory 'Repopulate the Product Combo Box

.Requery

'Add Dazzle by displaying list for the user .SetFocus .Dropdown End With End If End Sub

The example above assumes that there are two combo boxes, cboCategory and cboProduct. cboCategory has a rowsource that uses the Category table (straight from the table or a query), and retains the CategoryID as the BoundColumn.

cboProduct can initially be based on the entire Product table, but after you select a value from cboCategory, then the rowsource for cboProduct is recreated. After cboCategory is updated, there is a check to ensure that a nonblank value is chosen (don't forget that updating a value from something to nothing is still an update), and then an SQL statement is created to extract only the records that contain a CategoryID that matches the CategoryID from cboCategory. (If you are not familiar with creating SQL statements, see Chapter 14 for more information.)

After the rowsource property of the dependent combo box is created and set, the work is not completed. You still need to instruct Access to repopulate the data, which is the job of the Requery method. If you omit this step, then the combo box will continue to display the same information as it did before.

Finally, for an extra touch of fun and excitement for the user, the dependent combo box is opened to display the new population of Product items. One thing to note is that the DropDown method can only be executed on the combo box that has the focus.

0 0

Post a comment