ComboBox: Using VBA to Programmatically Populate the List

ComboBox: Using VBA to Programmatically Populate the List

This article discusses applying VBA to Windows MS Access 2007 form controls.

A ComboBox is a Windows control made of two parts: a text portion and a list. A text box is used to display a selection made from a list of items. A list box displays a list of items, usually in one column, but a list box can also be configured to display more than one column.

In my experience, the simpler a ComboBox is the easier it is to create and to manipulate. But if you want a ComboBox to be versitale and powerful, you will want multi-columns and some underlying VBA.

When creating a ComboBox to be used in a form in Access, the RowSourceType property can be set at design time by choosing “Table/Query”, “Value List”, or “Field List”, in the Property Sheet. Once Access knows what type of source to look for, you then set the RowSource—the name of the actual item that contains the values that will be used to populate the list.

There may be times when you want to change the RowSource on-the-fly, for example, using a filter to narrow-down the values in the list. This can be accomplished by using VBA in either a user-defined function or a query.

Example

The following example sets the RowSource property to a Select Query string. The query acts as a filter to narrow down the list to a certain ProductType instead of all the Products in the ZProductDesigns table. The value of the ProductType is provided by the ProductType control on the form.

Private Sub ProductTypeFilter_cmd_Click()
Dim strFilter, strProjectType As String
‘ ProductType is the group that the Product belongs to…
‘ This is the string for the Select query…
strFilter = “SELECT ZProductDesigns.RecId, ZProductDesigns.Product, ZProductDesigns.RecDate” _
& ” FROM ZProductDesigns WHERE ZProductDesigns.ProductType = ‘” & _
ProductType & “‘” & ” ORDER BY ZProductDesigns.Product, ZProductDesigns.RecDate;”
GotoItem_cbx.RowSource = strFilter
End Sub

Prime Wardrobe: Try Before You Buy