In a previous article, we looked at Creating Event Procedures in Microsoft Access. The article described how we would use the AfterUpdate event procedure of an Access Combo Box control. Here we detail how to create and use the VBA code that will perform the action of searching for the record chosen in a combo box or list box.
If we take a look at a Microsoft Access form that includes a Combo Box for searching records it will look like the following example screen:
Displaying the Combo Box that will search for a record in the form
In the combo box, you will see a list of Customer Names that are stored in the Customer table. The list displays the names in alphabetical order to enable the database user to quickly find and select a value in the list.
If we go into design view of the database form and look at the properties of the combo box control we will notice several settings:
The RowSource type and RowSource of the combo box
The RowSource type is set to Table/Query, which tells us that the data for the combo box control is coming from either a table, query or SQL statement which is specified in the RowSource. We can see in this case that the RowSource is defined as an SQL SELECT statement. We can click on the Build button (...) to take a closer look at this.
When we click on the build button, it displays the Query Builder window, which allows us to view the query in design view. Here we see the query includes the strCustomerName field from the Customer table, and we see that the data is sorted into Ascending order:
Showing the SQL SELECT statement used as the RowSource for the Combo Box
By viewing the query in SQL View, we can see the statement that is used for the RowSource:
SQL SELECT statement used for the RowSource of the combo box
If we now close the query builder and return to the form design view, we can check what happens in the After Update event property of the combo box. The AfterUpdate event occurs after changed data in a control or record is updated. Within a record, changed data in each control is updated when the control loses the focus or when the user presses ENTER or TAB. When the focus leaves the record or if the user clicks Save Record on the Records menu, the entire record is updated, and the data is saved in the database:
The After Update event property
If we now examine the VBA code behind this event (click on the build (...) button to access the Visual Basic Editor), we will see how the search works:
''''''''''''''''''''''''''''''''''''''''''' ' The AfterUpdate event procedure used in ' ' the Find Customer combo box search. ' ''''''''''''''''''''''''''''''''''''''''''' Private Sub cboFindCustomer_AfterUpdate() 'Moves to Customer Name text box and 'finds the record of whatever name is selected in the combo box DoCmd.ShowAllRecords Me!txtCustomerName.SetFocus DoCmd.FindRecord Me!cboFindCustomer 'Set value of combo box equal to an empty string Me!cboFindCustomer.Value = "" End Sub
The code performs the following actions:
See these other articles for other methods of searching information in a Microsoft Access form: