databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Combo Box

Using a Microsoft Access Combo Box to Search for a Record:

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
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 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
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
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
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:

  • The ShowAllRecords method displays all records from the underlying query
  • The SetFocus method moves the focus to the control - txtCustomerName in the form
  • The FindRecord method executes the FindRecord action, which will find the first record after the current record that matches the criteria specified in the combo box. The record is then displayed in the form.
  • The final part of the code sets the value in the combo box equal to an empty string, leaving nothing displayed in the combo box.

See these other articles for other methods of searching information in a Microsoft Access form: