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

Creating Event Procedures in Microsoft Access:

If you need to automate an action from a single event of a control then you can write all of the necessary VBA code as an event procedure that is attached to the event property of that particular control. A procedure contains a series of Visual Basic statements that perform an operation or provide a calculation. An event procedure is a procedure that runs in response to an event initiated by the user or program code, or triggered by the system.

In the example shown below, you will see the code that is used to find a record after a user selects a record from the values contained in a Microsoft Access combo box:

'''''''''''''''''''''''''''''''''''''''''''
' 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 example shows how we can use the AfterUpdate event of a combo box. The After Update event will occur when the data in a record or in the control on a form has been updated. The data is updated when the control loses focus or when the user presses the Enter or Tab button on the keyboard.

With this example, you should choose to use an unbound combo box control on your form to allow the user to choose a value from the list (the combo box should have no control source associated with it). If the combo box was bound to a field in your database table, it would update the value in the field each time a user selected a value; instead, we want to go to (search for) a record in the table that contains the selected combo box value.

To display values in our Microsoft Access combo box or list box we can use the RowSource Type and RowSource properties. The RowSource Type property will tell Access where to get the data from to display in the control. This data can be displayed from either one of your database tables, a query, a list of items or a list of field names from a table, query or SQL statement. The RowSource property tells Microsoft Access the name of the object where the data is stored.

See How To: Use a Microsoft Access Combo Box to Search for a Record