14.3 Share an ADP from a Shared Network Folder

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 into a list box things that you don't have stored in a table. In Visual Basic and other implementations of VBA, this is simple: you just use the AddItem method. But Access list boxes 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

It's true: Access list boxes (and combo boxes) don'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 had to forego a simple method 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.

One important question, of course, is why you would ever need either of these methods 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.

The following sections walk you through using both of the methods 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.

7.5.2.1 Filling a list box by modifying the RowSource property

1. Open the form frmRowSource in 07-05.MDB.

2. 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 how this method works. Figure 7-6 shows the form set to display month names in three columns.

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

   

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

1. Open the form frmListFill in 07-05.MDB.

2. Select a weekday from the first list box. The second list box will show you the date of that day this week, plus the next three instances of that weekday. Figure 7-7 shows the form with Wednesday, March 14, 2001, selected.

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

   

3. To use this method, set the control's RowSourceType property to the name of a function (without an equals sign or parentheses). Functions called this way must meet strict requirements, as discussed in the next section. Figure 7-8 shows the properties sheet for the list box on frmListFill, showing the RowSourceType property with the name of the list-filling function.

   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 Modifying the RowSource property

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: 2,048 characters.

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(1995, 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 you shouldn't have a problem, because the size has been greatly expanded.

7.5.3.2 Creating a list-filling callback function

This technique, which involves creating a special Access Basic function that provides the information Access needs to fill your list box, is not well documented in the Access manuals. Filling a list using a callback function provides a great deal of flexibility, and it's not difficult.

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	Unknown.
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)    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 avarDates(0 To MAXDATES) As Variant    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                avarDates(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 = avarDates(lngRow)       Case acLBEnd          ' Just clean up, if necessary.          Erase avarDates    End Select    ListFill2 = varRetval End Function

Note that the array this function fills, avarDates, 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, avarDates 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 1,440.