Recipe 7.5 Programmatically Add Items to a List or Combo Box

7.5.1 Problem

Getting items into a list or combo box from a data source is elementary in Access. Sometimes, though, you need to put things into a list box that you don't have stored in a table. In Visual Basic and other implementations of VBA-hosted environments, and in Access 2002 and later, this is simple: you just use the AddItem method. But Access list boxes in versions prior to 2002 don't support this method. How can you add to a list box items that aren't stored in a table?

7.5.2 Solution

Access list boxes (and combo boxes) in versions prior to Access 2002 didn't support the AddItem method that Visual Basic programmers are used to using. To make it easy for you to get bound data into list and combo boxes, the Access developers originally didn't supply a simple technique for loading unbound data. To get around this limitation, there are two methods you can use to place data into an Access list or combo box: you can programmatically build the RowSource string yourself, or you can call a list-filling callback function. Providing the RowSource string is easy, but it works in only the simplest of situations. A callback function, though, will work in any situation. This solution demonstrates both methods. In addition, this solution demonstrates using the AddItem method of ListBox and ComboBox controls, added in Access 2002.

One important question, of course, is why you would ever need either of the more complex techniques for filling your list or combo box. You can always pull data from a table, query, or SQL expression directly into the control, so why bother with all this work? The answer is simple. Sometimes you don't know ahead of time what data you're going to need, and the data's not stored in a table. Or perhaps you need to load the contents of an array into the control and you don't need to store the data permanently. Prior to Access 2002, you had no choice but to either create a list-filling callback function, or modify the RowSource property of the control yourself. Starting in Access 2002, you can also use the AddItem method to solve many list filling requirements.

The following sections walk you through using all three of the techniques for modifying the contents of a list or combo box while your application is running. 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.

7.5.2.1 Filling a list box by calling the AddItem method

Figure 7-6. The sample form, frmRowSource, displaying months in three columns

7.5.2.2 Filling a list box by modifying the RowSource property

7.5.2.3 Filling a list box by creating a list-filling callback function

Figure 7-7. Using list-filling callback functions to fill the lists on frmListFill

Figure 7-8. The properties sheet entry for the list-filling function

7.5.3 Discussion

This section explains the two methods for programmatically filling list and combo boxes. The text refers only to filling list boxes, but the same techniques apply to combo boxes. You may find it useful to open up the form module for each form as it's discussed here.

7.5.3.1 Calling the AddItem method

Starting with Access 2002, 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, specifying the item number or text to remove.) This technique is by far the simplest and should be your first choice, given the option.

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 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(2004, 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 crucial: unless you've set the RowSourceType property correctly, either at design time or in your code, you won't be able to 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've made, 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 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(2004, intI, 1), "mmmm")
        Next intI
End Select

In order to verify that, under the covers, the code is simply manipulating the RowSource property for you, the example ends by displaying the RowSource property in a TextBox control on the form:

    Me.txtFillString = lstAddItem.RowSource

Beware that even though it appears that you're actually adding items to the control, what you're really doing is modifying the RowSource property of the control. As such, you're limited by the same restrictions as if you were setting the property manually (see the next section). Specifically, you're limited to the allowed size of the RowSource property, which was 2048 characters in Access 2002 (the size may be larger in your version of Access).

7.5.3.2 Modifying the RowSource property

If you're using Access 2002 or later, you won't want to use this technique. On the other hand, for earlier versions of Access, this can be a simple way to create unbound lists. If you set a list box's RowSourceType property to Value List, you can supply a list of items, separated with semicolons, that will fill the list. By placing this list in the control's RowSource property, you tell Access to display the items one by one in each row and column that it needs to fill. Because you're placing data directly into the properties sheet, you're limited by the amount of space available in the properties sheet (this value varies depending on the version of Access).

You can modify the RowSource property of a list box at any time by placing into it a semicolon-delimited list of values. The ColumnCount property plays a part, in that Access fills the rows first and then the columns. You can see this for yourself if you modify the ColumnCount property on the sample form (frmRowSource).

The sample form creates a list of either 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 like this:

Select Case Me.grpChoice
   Case 1  ' Days
      ' Get last Sunday's date.
      varStart = Now - WeekDay(Now)
      ' Loop through all the days of the week.
      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(2004, 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'll end up with either a string of days like this:

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

or a string of months like this:

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

Once you've built 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

If you intend to use this method, modifying the RowSource property, make sure you understand its main limitation: because it writes the string containing all the values for the control into the control's properties sheet, it's limited by the number of characters the properties sheet can hold.

If you're using a version of Access prior to Access 2002, you can use at most 2,048 characters in the RowSource property. If you need more data than that, you'll need to use a different method. If you're using Access 2002 or later you shouldn't have a problem, because the size has been greatly expanded. On the other hand, in those versions, you're better off using the AddItem method instead.

7.5.3.3 Creating a list-filling callback function

This technique, which involves creating a special function that provides the information Access needs to fill your list box, is not well documented in the Access help. Filling a list using a callback function provides a great deal of flexibility, and it's not difficult. This technique provides the greatest flexibility, and isn't limited by the size of the RowSource property.

The concept is quite simple: you provide Access with a function that, when requested, returns information about the control you're attempting to fill. Access "asks you questions" 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 provide the information so that Access can fill the control with data. This is the only situation in Access where you provide a function that you never need to call. Access calls your function as it needs information in order to fill the control. The sample form frmFillList uses two of these functions to fill its two list boxes.

To communicate with Access, your function must accept five specific parameters. Table 7-4 lists those parameters and explains the purpose of each. (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 7-4.)

Table 7-4. The required parameters for all list-filling functions

Argument	Data type	Description
ctl	Control	A reference to the control being filled.
varId	Variant	A unique value that identifies the control that's being filled (you assign this value in your code). Although you could use this value to let you use the same function for multiple controls, this is most often not worth the extraordinary trouble it causes.
lngRow	Long	The row currently being filled (zero-based).
lngCol	Long	The column currently being filled (zero-based).
intCode	Integer	A code that indicates the kind of information that Access is requesting.

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

Table 7-5. The values of intCode, their meanings, and their return values

Constant	Meaning	Return value
acLBInitialize	Initialize the data.	Nonzero if the function will be able to fill the list; Null or 0 otherwise
acLBOpen	Open the control.	Nonzero unique ID if the function will be able to 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

You'll find that almost all of your list-filling functions will be structured the same way. Therefore, you may find it useful to always start with the ListFillSkeleton function, which is set up to receive all the correct parameters and includes a Select Case statement to handle each of the useful values of intCode. All you need to do is change its name and make it return some real values. The ListFillSkeleton function is as follows:

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 in each row/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, unless you use
         ' an array whose memory you want to release).

   End Select
   ListFillSkeleton = varRetval
End Function

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

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 second column to 0.
         ' Remember, they're zero-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 when requested. This function, ListFill2, displays the next four instances of a particular day of the week. That is, if you choose Monday in the first list box, this function will fill the second list box with the date of the Monday in the current week, along with the dates of the next three Mondays. The source code for ListFill2 is:

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) 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 the array this function fills, adtmDates, is declared as a static variable. Declaring it this way makes it persistent: its value remains 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 "hang around" between calls to the function. If you fill an array with data for your control, it's imperative that you declare the array as static.

You should also consider the fact that Access calls the acLBInitialize case only once, but it calls the acLBGetValue case at least once for every data item to be displayed. In this tiny example, that barely makes a difference. If you're doing considerable work to calculate values for display, however, you should put all the time-consuming work in the acLBInitialize case and have the acLBGetValue case do as little as possible. This optimization can make a big difference if you have a large number of values to calculate and display.

There are three more things you should note about this second list box example:

• In the acLBEnd case, the function clears out the memory used by the array. In this small example, this hardly matters. If you are filling a large array with data, you'd want to make sure that the data is released at this point. For dynamic arrays (where you specify the size at runtime), Erase releases all the memory. For fixed-size arrays, Erase empties out all the elements.

• This example didn't include code for all the possible cases of intCode. If you don't need a specific case, don't bother coding for it. There was no need to set the column widths here, so there's no code handling acLBGetColumnWidth.

• At the time of this writing, there's a small error in the way Access handles these callback functions. Although it correctly calls the acLBInitialize case only once when you open a form that requires a control to be filled with the function, if you later change the RowSourceType in code, Access will call the acLBInitialize case twice. This doesn't come up often, but you should be aware that there are circumstances under which Access will erroneously call this section of your code more times than you intended. To solve this problem, you can use a static or global variable as a flag to keep track of the fact that the initialization has been done and opt not to execute the code after the first pass through.

In the list-filling callback function method, when Access requests the number of rows in the control (i.e., 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're filling the list box with the results of a query that returns a large number of rows, you won't want to perform the MoveLast method you'd need to find out how many rows the query returned—MoveLast requires Access to walk through all the rows returned from the query and would make the load time for the list box too long. Instead, respond to acLBGetRowCount with a -1. This tells Access that you'll tell it later how many rows there are. Then, in response to the acLBGetValue case, return data until you've reached the end. Once you return Null in response to the acLBGetValue case, Access understands that there's no more data.

This method has its pitfalls, too. Although it allows you to load the list box with data almost immediately, the vertical scrollbar won't be able to operate correctly until you've scrolled down to the end. If you can tolerate this side effect, returning -1 in response to acLBGetRowCount will significantly speed the loading of massive amounts of data into list 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 x 1,440.

You might wonder when you would use any of these techniques. In Access 2002 or later, your best bet is to use the AddItem method whenever possible. Under the covers, this method executes the same sort of code as if you were to modify the RowSource property value yourself. (You don't really need to ever 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 is limited in size. For large lists of values, perhaps with many columns, you may run out of space before you run out of data. In that case, you'll be required to use the list-filling callback function technique. If you're using Access 2000 or an earlier version, you'll need to use the list-filling callback technique for complex lists, or to create the RowSource property value in code yourself for simpler lists.