Screenshot below shows a combo box for displaying a list of suppliers. To achieve the drop down effect, two most important events must be set up. First is what to display when the form is loaded. Second is after a dropdown value is selected, what to display on the form.

Below is the Suppliers combo box drops down and displays 2 fields.

To create the Suppliers combo box, open your Northwind Access database and create a new form and a combo box control. The essential combo box properties can be defined by using the values shown below. Other property values can be left with the default ones.

Main controls on the Form

Open the Northwind database and create a new Form, then create the following controls.

The items listed below are commonly used Properties for a Combo Box control that should be set up and used by the Form Load event of the Access Form control.

1. Form Load event

1. ItemData

   When a form is loaded, the first thing we need to consider is displaying a default value in the combo box so that the user can have a feel about what the data are contained in the drop down.

   To set up the default value, use the following VBA code. The following code automatically selects the first row in a Combo Box when the form loads when Column Heads property is set to No.

   Me.cmbSupplier.DefaultValue = Me.cmbSupplier.ItemData(0)

   When Column Heads property is set to Yes, use the ItemData(1) as the column heads is element 0 (the first row in the list of values).

   Me.cmbSupplier.DefaultValue = Me.cmbSupplier.ItemData(1)

   The ItemData property returns the data in the bound column for the specified row in a combo box. The row number in the combo box contains the data you want to return. Rows in combo (also list boxes) are indexed starting with zero. So to return the item in the first row of a combo box, you specify 0 for the row index argument, e.g. ItemData(0).

   ItemData(n) contains data for every column of the row, but their visibility is based on the Column Widths property you set up. Here you can only see Company Name and Phone because column widths are 0cm;4cm;2cm;0cm;0cm, which indicates that only data in the second and third column are visible in the combo box.

   Note that the column headings and what the first row is are defined by the query provided to the combo box in Row Source property.

   Query:

   SELECT SupplierID, CompanyName, Phone, ContactTitle, ContactName FROM Suppliers ORDER BY SupplierID;

2. Row Source and Row Source Type

   These two properties tell Access how to feed the data to the combo box.

   For example, our suppliers' Row Source Type is set to Table/Query by using the Row Source below.

   Query:

   SELECT SupplierID, CompanyName, Phone, ContactTitle, ContactName FROM Suppliers ORDER BY SupplierID;

   Please note that Row Source Type can also be Value List or Field List.

3. Bound Column

   When you make a selection from a combo box, the BoundColumn property tells Microsoft Access which column's values to use as the value of the ComboBox control.

   

   Normally we bound a column that contains ID values to the combo box so that the selected ID value can be passed on to other sub routines or modules to retrieve relevant data for the corresponding ID in a table. In our example, the combo box contains SupplierID that can be obtained in On Change event subroutine, then is used to query Contact Title, Contact Name, and Phone number for the supplier with this particular ID.

   In VBA, to obtain the value in the bound column, the following syntax can be used interchangeably.

   Me!cmbSupplier
   Me.cmbSupplier
   Me!cmbSupplier.Value
   Me.cmbSupplier.Value

   The value set in the BoundColumn property corresponds to the columns listed in the SQL SELECT query in the Row Source property of the ComboBox control.

   Setting the BoundColumn property to 1 stores the value of the first column SupplierID in the ComboBox cmbSupplier.

   For more information, see Microsoft page about BoundColumn here https://docs.microsoft.com/en-us/office/vba/api/Access.ComboBox.BoundColumn

4. Column Width

   Hide the first, fourth, and fifth column, and display the second and third column.

   0cm;4cm;2cm;0cm;0cm

5. Column Count

   This property must be consistent with the number of columns in the query for Row Source property.

   

6. Limit To List

   Combo boxes and list boxes are a great way to control user experience by limiting the values that a user can choose to a known list.

   The Limit To List property tells Access 2010 whether to allow a user to select a value that is not in the list. This property can either be set to Yes or No. If the property is set to Yes, then Access will not allow the user to enter a value that isn't in the list. If a user tries to enter a value that is not contained in the combo box, the following message will appear:

7. Column Head

   Set to Yes to show column heading for the displayed/visible columns.

8. Obtain a column value

   When we need access an individual value for a column stored in the selected record/row from the ComboBox, we use Column property in VBA.

   Me!cmbSupplier.Column(0) gives the value for SupplierID (the first column).
   Me!cmbSupplier.Column(1) gives the value for CompanyName (the second column).
   Me!cmbSupplier.Column(2) gives the value for Phone (the third column).
   Me!cmbSupplier.Column(3) gives the value for ContactTitle (the fourth column).
   Me!cmbSupplier.Column(4) gives the value for ContactName (the fifth column).

   Note that MS Access uses zero-based numbers to refer to columns in the Column property. That is, the first column value is referenced by using the expression Column(0), the second column value is obtained by using the expression Column(1), and so on. This is the same as the indexing for ItemData property. However, the BoundColumn property uses 1-based numbers to refer to the columns. This means that if the BoundColumn property is set to 1, the value stored in the combo box is from the first column of the selected row (use ItemData to reach that row).

After defining these Properties values above, the VBA for Form Load event becomes very simple as below.

Private Sub Form_Load()
    Me.cmbSupplier.DefaultValue = Me.cmbSupplier.ItemData(1)
    Me.txtContactTitle.Value = Me!cmbSupplier.Column(3)
    Me.txtContactName.Value = Me!cmbSupplier.Column(4)
    Me.txtPhone.Value = Me!cmbSupplier.Column(2)
End Sub

2. On Change event

In VBA, using On Change event subroutine can greatly improve the usefulness of a ComboBox to display extra data for the record selected in the ComboBox.

In our example, we want to display the selected supplier's contact person's name, his/her title, and phone number. This can be achieved by using On Change Event. This On Change event is triggered (code inside is run) when a value is selected/changed in the combo box drop down.

Private Sub cmbSupplier_Change()

    Dim strSQL As String
    Dim objDB As DAO.Database
    Dim rstSupplier As DAO.Recordset
    Dim lngSupplierID As Long
    Dim strCompanyName As String
    Dim strPhone As String
    Dim strContactTitle As String
    Dim strContactName As String
    
    lngSupplierID = Me!cmbSupplier.Value
    
    '' Get the supplier by feeding SupplierID
    strSQL = "SELECT SupplierID, CompanyName, Phone, ContactTitle, ContactName FROM Suppliers where SupplierID = " & lngSupplierID
    
    Set objDB = CurrentDb
        
    Set rstSupplier = objDB.OpenRecordset(strSQL, dbReadOnly)
    
    If (rstSupplier.RecordCount <= 0) Then
        MsgBox "Can't find any suppliers."
    Else
        lngSupplierID = rstSupplier!SupplierID
        strCompanyName = rstSupplier!CompanyName
        strPhone = rstSupplier!Phone
        strContactTitle = rstSupplier!ContactTitle
        strContactName = rstSupplier!ContactName
        strPhone = rstSupplier!Phone
        
        rstSupplier.Close
        objDB.Close
    End If
    
    '' Display the values
    Me.txtContactTitle.Value = strContactTitle
    Me.txtContactName.Value = strContactName
    Me.txtPhone.Value = strPhone
    
    Set rstSupplier = Nothing
    Set objDB = Nothing
End Sub

Happy Coding!


Copyright© GeeksEngine.com