Create a useful Access combo box with essential features fast by using VBA code.

Create MS Access Combo Box essential properties by VBA code

We have detailed the essential properties to set up for a Combo Box. In this article, we're going to create the same Combo Box and functions the same way but by using VBA codes with a List Box as a useful feature that is often used together with control box.

If you don't like to manually set up everything in the Properties for a Combo Box in Access, you can use VBA code to achieve the same effect. In this way, the combo box is unbound to any column upfront but its row source is bound dynamically by using VBA code.

Below is the Suppliers combo box drops down and displays 2 fields and a correlated List Box control. The List Box displays products for the selected supplier in the Suppliers combo box.

Northwind Suppliers combo box with correlated list box

Main controls on the Form

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

Type	Name
Combo Box	cmbSupplier
Text Box	txtContactTitle
Text Box	txtContactName
Text Box	txtPhone
List Box	lstProduct

Northwind Suppliers combo box design with list box' sheet

We use VBA to set up property values for the combo box which works exactly the same way as the one manually set up here.

1. Form Load event

Below is the VAB code for the Form Load where the last 2 lines are the correlated List control lstProduct.

One useful extension to the Combo Box usage is to display a correlated change in List control where it displays related values of a supplier. For example, when a different supplier is selected in the drop down, the list of products supplied by this supplier automatically change in the List control.

Sub Form_Load()
    Dim strSQL As String
    Dim strSQL2 As String
    
    strSQL = "SELECT SupplierID, CompanyName, Phone, ContactTitle, ContactName FROM Suppliers order by SupplierID"

    Me!cmbSupplier.RowSource = strSQL
    Me!cmbSupplier.RowSourceType = "Table/Query"
    Me!cmbSupplier.ColumnCount = 5
    Me!cmbSupplier.ColumnWidths = "0cm;4cm;2cm;0cm;0cm"
    Me!cmbSupplier.BoundColumn = 1
    Me!cmbSupplier.ColumnHeads = True
    Me!cmbSupplier.LimitToList = True
    Me!cmbSupplier.DefaultValue = Me.cmbSupplier.ItemData(1)
    Me.txtPhone.Value = Me.cmbSupplier.Column(2)
    Me.txtContactTitle.Value = Me.cmbSupplier.Column(3)
    Me.txtContactName.Value = Me.cmbSupplier.Column(4)
    
    '' Query for the correlated products by selected supplier
    strSQL2 = "select p.ProductID, p.ProductName, c.CategoryName " & _
        " from Products As p " & _
        " inner join Categories As c On p.CategoryID = c.CategoryID where p.SupplierID = " & Me!cmbSupplier.Value & _
        " order by c.CategoryName"
    
    '' Feed the List control with a query
    Me!lstProduct.RowSource = strSQL2
    Me!lstProduct.RowSourceType = "Table/Query"
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.

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

3. After Update event

After supplier is changed to a different one in the Suppliers drop down, the Products List control is refreshed to show products supplied by this supplier.

Here the supplier is changed to 'New Orleans Cajun Delights' and the products supplied automatically updated in the list control.

Northwind Suppliers combo box with correlated List control

'' Update the List control when a supplier is changed.
Private Sub cmbSupplier_AfterUpdate()
    Dim strSQL As String
    Dim lngSupplierID As Long
    
    lngSupplierID = Me.cmbSupplier.Value
        
    strSQL = "select p.ProductID, p.ProductName, c.CategoryName " & _
        " from Products As p " & _
        " inner join Categories As c On p.CategoryID = c.CategoryID where p.SupplierID = " & lngSupplierID & _
        " order by c.CategoryName"
        
    Me!lstProduct.RowSource = strSQL
    Me!lstProduct.RowSourceType = "Table/Query"
    Me!lstProduct.ColumnCount = 3
    Me!lstProduct.ColumnWidths = "0cm;4cm;4cm;"
    Me!lstProduct.BoundColumn = 1
    Me!lstProduct.ColumnHeads = True
    Me!lstProduct.DefaultValue = Me.lstProduct.ItemData(1)
End Sub

Other Recent Articles from the MS Access category:

1.	Examples of MS Access DateDiff function used in query and VBA code
2.	MS Access DateDiff function
3.	How to find out your computer name and username by VBA
4.	Examples of MS Access DatePart function
5.	MS Access DatePart function
6.	Examples of MS Access DateAdd function
7.	MS Access DateAdd function
8.	IIF function basics - the CASE statement of MS Access
9.	MS Access Date Expression
10.	Solved: MS Access error "The text is too long to be edited"
11.	Create MS Access Combo Box essential properties manually
12.	How to do text search in MS Access programmatically
13.	Solved - the size of the Access query result is larger than the maximum size of a database (2 GB)
14.	How to easily get a list of field names in MS Access
15.	How to count distinct records in MS Access
16.	How to do transaction based processing in MS Access
17.	How to open a document (local/network file or web page) from MS Access
18.	How to use ADOX to create unique composite index - the VBA approach
19.	How to do cross-table update queries in MS Access - the right way
20.	Three efficient ways to get the number of records by using VBA
21.	How to create a composite unique index (not as a primary key) in MS Access
22.	Use VBA to get the correct number of records in a Recordset object
23.	Disable Access Prompt when a record is changed, table deleted, or action queries run
24.	How to hide and unhide a MS Access object
25.	How to return multiple values from a VBA function (Part 3)
26.	How to return multiple values from a VBA function (Part 2)
27.	How to return multiple values from a VBA function (Part 1)
28.	Three ways to programmatically duplicate a table in MS Access by VBA
29.	Create a DLL by CSharp or VB.Net for VBA
30.	How to correctly reference and call a DLL
31.	How to register a C# or VB.Net DLL
32.	Email address validation by Regular Expressions using VBA
33.	Fix MS Access error: Query must have at least one destination field
34.	How to unselect radio buttons in MS Access after it has been selected
35.	How to Change Query Timeout Value for MS Access SQL Queries
36.	What is Northwind Traders database

	Custom Search