Programmatically Adding Items to Lists or Combo Boxes in Access 2007

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn several different ways to fill a list box or a combo box in Microsoft Office Access 2007 programmatically. (12 printed pages)

Office Visual How To

**Applies to:**2007 Microsoft Office system, Microsoft Office Access 2007

Ken Getz, MCW Technologies, LLC

May 2009

Getting items into a list box or combo box from a data source is elementary in Microsoft Office Access 2007. Sometimes, though, you must put items that are stored outside of a table into a list box. If you work in Visual Basic or other VBA-hosted environments, or in Office Access 2002 or later, you can use the AddItem method. But Access list boxes in versions prior to 2002 do not support AddItem. This article demonstrates all of the different techniques that you can use to add items to a ListBox or ComboBox control in Office Access 2007.

Programmatically Adding Items to Lists in Access

Watch the Video

Length: 11:56 | Size: 12.50 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the sample database

List boxes and combo boxes in Access prior to Access 2002 did not support the AddItem method that Visual Basic programmers typically use. To make it easy for you to get bound data into list and combo boxes, the Access developers originally did not supply a simple technique to load unbound data. Instead, you had to do one of the following to place data into an Access list or combo box:

Programmatically build the RowSource string yourself, which is easy to do, but only works in the simplest situations.
Call a list-filling callback function, which works in any situation, but requires some complex code.

This article discusses both of these methods, as well as how to use the AddItem method of ListBox and ComboBox controls that is available in the latest versions of Access.

You might wonder why you would ever need either of the more complex techniques to fill your list or combo box since you can always pull data from a table, query, or SQL expression directly into the control. The problem is that you do not always know beforehand what data you are going to need, and the data that you do need might not be stored in a table. Or, you might need to load the contents of an array into the control but do not need to store the data permanently.

Prior to Access 2002, you had no choice but to create a list-filling callback function or to modify the RowSource property of the control yourself. In current versions of Access, you can still use those techniques, but it is often easier to use the AddItem method.

This article shows you how to use each technique to modify the contents of a list or combo box while your application runs. The first example modifies the value of the RowSource property, given that the RowSourceType property is set to Value List. The second example covers list-filling callback functions. The final example shows how to use the AddItem method of the control.

Before you examine the code, experiment with each form in the sample database, FillList.accdb.

First, open the database and open the first sample form, frmAddItem. Change the contents of the list box by choosing either Days or Months from the option group on the left. Try both settings and change the number of columns to get a feel for the method. Figure 1 shows the form set to display month names in two columns.

Figure 1. frmAddItem uses the AddItem method to fill the list box at runtime

Form uses AddItem to fill the list box at runtime

Next, open the form frmRowSource in the sample database. Change the contents of the list box by choosing either Days or Months from the option group on the left. This form looks and reacts the same as the previous form, but it uses a different technique with a lot more code to fill the list.

Now open the sample form frmListFill. Select a weekday from the first list box to display the date of that day this week, plus the next three instances of that weekday in the second list box. Figure 2 shows the form with the date, Wednesday, April 1, 2009, selected.

Figure 2. frmListFill uses a list-filling callback function to fill its lists

Form uses a callback function to fill its lists

When you use a list-filling callback function, you must set the control's RowSourceType property to the name of the function. Do not include an equal sign or parentheses when you specify the function. (Functions called this way must meet strict requirements, as discussed in the next section.) Figure 3 shows the properties sheet for the list box on frmListFill, including the RowSourceType property with the name of the list-filling function.

Figure 3. Set the RowSourceType property to specify the list-filling function

Set RowSourceType property to specify the function

Now that you have experimented with each of the forms, investigate the code in each of the respective modules to understand how each technique works.

Calling the Additem Method

Start by looking in the code module for frmAddItem, where you can add items to a ListBox or ComboBox control by simply calling the AddItem method of the control. (You can remove items from the control by calling its RemoveItem method and specifying the item number or text to remove.) This technique is by far the simplest, and should be your first choice.

Selecting an option in the Fill Choice group runs the following code.

Private Sub grpChoice_AfterUpdate()
    Dim strList As String
    Dim intI As Integer
    Dim varStart As Variant

    lstAddItem.RowSourceType = "Value List"
    
    ' Clear out the list.
    lstAddItem.RowSource = vbNullString
    lstAddItem.ColumnCount = 1
    grpColumns = 1
    
    Select Case Me.grpChoice
        Case 1  ' Days
            ' Get last Sunday's date.
            varStart = Now - WeekDay(Now)
            ' Loop through all of the week days.
            For intI = 1 To 7
                lstAddItem.AddItem Format(varStart + intI, "dddd")
            Next intI
                
        Case 2  ' Months
            For intI = 1 To 12
                lstAddItem.AddItem Format( _
                  DateSerial(2009, intI, 1), "mmmm")
            Next intI
    End Select

    Me.txtFillString = lstAddItem.RowSource
End Sub

This code starts by setting the RowSourceType property of the control to the text “Value List”.

lstAddItem.RowSourceType = "Value List"

This step is critical. If you do not set the RowSourceType property correctly, either at design time or in your code, you cannot call the AddItem or RemoveItem methods.

Next, the code clears and resets the list’s formatting.

lstAddItem.RowSource = vbNullString
lstAddItem.ColumnCount = 1
grpColumns = 1

Then, depending on the choice you make, the code adds days of the week, or months of the year, to the ListBox control.

Select Case Me.grpChoice
    Case 1  ' Days
        ' Get last Sunday's date.
        varStart = Now - WeekDay(Now)
        ' Loop through all of the week days.
        For intI = 1 To 7
            lstAddItem.AddItem Format(varStart + intI, "dddd")
        Next intI

    Case 2  ' Months
        For intI = 1 To 12
           lstAddItem.AddItem Format( _
             DateSerial(2009, intI, 1), "mmmm")
       Next intI
End Select

To verify that the code is simply manipulating the RowSource property, the example displays the RowSource property in a TextBox control on the form.

Me.txtFillString = lstAddItem.RowSource

Even though it might seem to you that you are adding items to the control, what you’re actually doing is modifying the RowSource property of the control. That means that you are limited by the same size restrictions that apply when you set the RowSource property manually, which is around 32,000 characters.

Note
You might find it interesting to investigate the code module in frmRowSource; the code manually manipulates the RowSource property of a ListBox control. Generally, you can use the AddItem and RemoveItem methods to do this work for you in Access 2002 and later.

Building the Rowsource String Yourself

You can modify the RowSource property of a list box at any time by placing a semicolon-delimited list of values into it.

Note
Since Access fills the rows first, and then the columns, the ColumnCount property affects the outcome. To see how, modify the ColumnCount property on the sample form frmRowSource.

The sample form creates a list of the days in a week or the months in a year, based on the value and option group on the form. The code that performs the work looks similar to the following code.

Select Case Me.grpChoice
    Case 1  ' Days
        ' Get last Sunday's date.
        varStart = Now - WeekDay(Now)
        ' Loop through all of the week days.
        For intI = 1 To 7
            strList = strList & ";" & _
              Format(varStart + intI, "dddd")
        Next intI

    Case 2  ' Months
        For intI = 1 To 12
            strList = strList & ";" & _
              Format(DateSerial(2009, intI, 1), "mmmm")
        Next intI
End Select

' Get rid of the extra "; " at the beginning.
strList = Mid(strList, 2)
Me.txtFillString = strList

Depending on the choice in grpChoice, you end up with a string of days or a string of months. A string of days looks similar to the following.

Sunday; Monday; Tuesday; Wednesday; Thursday; Friday; Saturday; Sunday

A string of months looks similar to the following.

January; February; March; April; May; June; July; August; 
September; October; November; December

Once you build up the string, make sure that the RowSourceType property is set correctly, and then insert the new RowSource string.

lstChangeRowSource.RowSourceType = "Value List"
lstChangeRowSource.RowSource = strList

Calling a List-Filling Callback Function

The most flexible, complex, and powerful solution that you have to fill a list box or a combo box in Access is to call a list-filling callback function. The solution is not difficult, and it is not limited by the size of the RowSource property.

When you use the technique, you create a special function that provides Access the information it needs to fill your list or combo box. Access requests information about the number of rows, the number of columns, the width of the columns, the column formatting, and the actual data itself. Your function must react to these requests and return the information so that Access can fill the control with data. Access calls your function as it needs information to fill the control. (The sample form frmFillList uses two different functions to fill its two list boxes.)

To communicate with Access, your function must accept five specific parameters. Table 1 lists those parameters and explains the purpose of each.

Note
The parameter names are arbitrary and are provided here as examples only. The order of the parameters, however, is not arbitrary; they must appear in the order listed in Table 1.

Table 1. Required parameters for all list-filling functions

Argument	Data type	Description
ctl	Control	A reference to the control that you want to fill.
varId	Variant	A unique value that you assign in your code and that identifies the control that is being filled. Although you could use the same function for multiple controls by using this value, it is most often not worth the extraordinary trouble that doing so creates.
lngRow	Long	The row that is currently being filled (zero-based).
lngCol	Long	The column that is currently being filled (zero-based).
intCode	Integer	A code that specifies what type of information Access is requesting.

Access uses the final parameter, intCode, to let you know what type of information it is requesting. Access places a particular value in that variable, and it's up to your code to react to that request and to supply the necessary information as the return value of your function. Table 2 lists the possible values of intCode, the meaning of each, and the value your function must return to Access in response to each.

Table 2. Values for intCode, their meanings, and their return values

Constant	Meaning	Return value
acLBInitialize	Initialize the data.	Nonzero if your function can fill the list; Null or 0, otherwise.
acLBOpen	Open the control.	Nonzero unique ID if your function can fill the list; Null or 0, otherwise.
acLBGetRowCount	Get the number of rows.	Number of rows in the list; -1 if unknown (see the text for information).
acLBGetColumnCount	Get the number of columns.	Number of columns in the list (cannot be 0).
acLBGetColumnWidth	Get the column widths.	Width (in twips) of the column specified in the lngCol argument (zero-based); specify –1 to use the default width.
acLBGetValue	Get a value to display.	Value to be displayed in the row and column specified by the lngRow and lngCol arguments.
acLBGetFormat	Get the column formats.	Format string to be used by the column specified in lngCol.
acLBClose	Not used.
acLBEnd	End (when the form is closed).	Nothing.

Because almost all of your list-filling functions will be structured the same way, you might find it useful to start with the ListFillSkeleton function in basListFill in the sample database. ListFillSkeleton receives all of the correct parameters and includes a Select Case statement to handle each of the useful values of intCode. All you need to do is to change its name and make it return some real values. The ListFillSkeleton function looks similar to the following code.

Function ListFillSkeleton(ctl As Control, varId As Variant, _
 lngRow As Long, lngCol As Long, intCode As Integer) As Variant
    
    Dim varRetval As Variant

    Select Case intCode
        Case acLBInitialize
            ' Could you initialize?
            varRetval = True

        Case acLBOpen
            ' What's the unique identifier?
            varRetval = Timer

        Case acLBGetRowCount
            ' How many rows are there to be?

        Case acLBGetColumnCount
            ' How many columns are there to be?

        Case acLBGetValue
            ' What's the value for each row in each column to be?

        Case acLBGetColumnWidth
            ' How many twips wide should each column be?
            ' (optional)

        Case acLBGetFormat
            ' What's the format for each column to be?
            ' (optional)

        Case acLBEnd
            ' Just clean up, if necessary.
            ' (optional)

    End Select
    ListFillSkeleton = varRetval
End Function

For example, the following function from frmListFill, ListFill1, fills in a two-column list box on the form, with the second column hidden (its width is set to 0 twips). Each time that Access calls the function with acLBGetValue in intCode, the function calculates a new value for the date and then returns that value. The following is the source code for ListFill1.

Private Function ListFill1( _
 ctl As Control, varId As Variant, lngRow As Long, _
 lngCol As Long, intCode As Integer)
    
    Select Case intCode
        Case acLBInitialize
            ' Could you initialize?
            ListFill1 = True

        Case acLBOpen
            ' What's the unique identifier?
            ListFill1 = Timer

        Case acLBGetRowCount
            ' How many rows are there to be?
            ListFill1 = 7

        Case acLBGetColumnCount
            ' How many columns are there to be?

            ' The first column will hold the day of the week.
            ' The second, hidden, column will hold the actual date.
            ListFill1 = 2

        Case acLBGetColumnWidth
            ' How many twips wide should each column be?

            ' Set the width of the 2nd column to be 0.
            ' Remember, they're 0-based.
            If lngCol = 1 Then ListFill1 = 0

        Case acLBGetFormat
            ' What's the format for each column to be?
                
            ' Set the format for the first column so
            ' that it displays the day of the week.
            If lngCol = 0 Then
                ListFill1 = "dddd"
            Else
                ListFill1 = "mm/dd/yy"
            End If

        Case acLBGetValue
            ' What's the value for each row in each column to be?

            ' No matter which column you're in, return
            ' the date lngRow days from now.
            ListFill1 = Now + lngRow

        Case acLBEnd
            ' Just clean up, if necessary.
    End Select
End Function

The next example, which fills the second list box on the sample form, fills an array of values in the initialization step (acLBInitialize) and returns items from the array upon request. The following function, ListFill2, displays the next four instances of a particular day of the week. For example, if you choose Monday in the first list box, ListFill2 fills the second list box with the date of the Monday in the current week, along with the dates of the next three concurrent Mondays. The following is the source code for ListFill2.

Private Function ListFill2( _
 ctl As Control, varId As Variant, lngRow As Long, _
 lngCol As Long, intCode As Integer)
    
Const MAXDATES = 4

    Static varStartDate As Variant
    Static adtmDates(0 To MAXDATES - 1) As Date
    Dim intI As Integer
    Dim varRetval As Variant

    Select Case intCode
        Case acLBInitialize
            ' Could you initialize?

            ' Do the initialization. This is code
            ' you only want to execute once.
            varStartDate = Me.lstTest1
            If Not IsNull(varStartDate) Then
                For intI = 0 To MAXDATES - 1
                    adtmDates(intI) = _
                      DateAdd("d", 7 * intI, varStartDate)
                Next intI
                varRetval = True
            Else
                varRetval = False
            End If
            
        Case acLBOpen
            ' What's the unique identifier?
            varRetval = Timer

        Case acLBGetRowCount
            ' How many rows are there to be?
            varRetval = MAXDATES

        Case acLBGetFormat
            ' What's the format for each column to be?
            varRetval = "mm/dd/yy"

        Case acLBGetValue
            ' What's the value for each row in each column to be?
            varRetval = adtmDates(lngRow)

        Case acLBEnd
            ' Just clean up, if necessary.
            Erase adtmDates
    End Select
    ListFill2 = varRetval
End Function

Note that ListFill2 fills an array called adtmDates that is declared as a static variable. When you declare an array as a static variable, its values remain available between calls to the function. Because the code fills the array in the acLBInitialize case but doesn't use it until the multiple calls in the acLBGetValue case, adtmDates must persist between calls to the function. If you fill an array with data for your control, it is imperative that you declare the array as static.

Read It

When you use the list-filling callback function technique, remember that Access calls the acLBInitialize case only once, but it calls the acLBGetValue case at least once for every data item to display. Although that makes little difference in small examples similar to the one in this article, it can make a big difference if you have a large number of values to calculate and display. In other words, if you must do a considerable amount of work to calculate values for display, put all of the time-consuming work in the acLBInitialize case and confine the acLBGetValue case to as little as possible.

There are a few more things that you should note about the list box example in this article:

In the acLBEnd case, the function clears out the memory that the array uses. Although clearing out the memory hardly matters in this small example, when you fill a large array with data, make sure that you release the data at this point. For dynamic arrays, where you specify the size at run time, Erase releases all of the memory. For fixed-size arrays, Erase empties all of the elements.
This example does not include code for all of the possible cases of intCode. If you don't need a specific case, don't bother coding for it. If, for example, you don't need to set the column widths, don't add code to handle the acLBGetColumnWidth value.
In the list-filling callback function method, when Access requests the number of rows in the control (that is, when it passes acLBGetRowCount in intCode), you'll usually be able to return an accurate value. Sometimes, however, you won't know the number of rows or won't be able to get the information easily. For example, if you want to fill a list box with the results of a query that returns a large number of rows, you probably won't want to use the MoveLast method to find out how many rows the query returned. Why? Because MoveLast requires Access to walk through all of the rows that the query returns, and that would make the load-time for the list box too long. Instead, respond to acLBGetRowCount with a value of -1, which tells Access that you'll tell it later how many rows there are. Then, in response to the acLBGetValue case, return data until you reach the end. Once you return Null in response to the acLBGetValue case, Access understands that there is no more data. There is one drawback; although the method loads the list box with data almost immediately, the vertical scrollbar will not operate correctly until you scroll down to the end. If you can tolerate this side effect, returning a value of -1 in response to acLBGetRowCount significantly speeds up the process when you must load massive amounts of data into list controls and combo box controls.
To provide values for the acLBGetColumnWidth case, you can specify a different width for each column based on the lngCol parameter. To convert from inches to twips, multiply the value by 1,440. For example, to specify a 1/2-inch column, return 0.5 * 1,440.

Now that you have seen all of the different ways to programmatically fill a list, you might wonder when you would use each of the techniques. In Access 2002 or later, the best strategy is to use the AddItem method whenever possible. Although it may not be obvious to the casual observer, the method executes similar code to the code that it executes when you modify the RowSource property value yourself. (You don’t really ever need to modify the RowSource property manually; in Access 2002 or later, calling the AddItem and RemoveItem methods does the same sort of thing for you.) Remember, however, that the RowSource property value has size restrictions. For very large lists of values, perhaps with many columns, you might run out of space before you run out of data. In that case, you must use the list-filling callback function technique.

Explore It

Access 2007 Sample: Programmatically Adding Items to Lists or Combo Boxes
Access Developer Center
Access 2007 Resource Center
Getting the Most Out of Visual Basic for Applications
ComboBox.AddItem Method
ComboBox.RowSource property
RowSourceType Property (User-Defined Function) - Code Argument Values

Programmatically Adding Items to Lists or Combo Boxes in Access 2007

Code It

Read It

Explore It

Additional resources