|[ Team LiB ]|
Recipe 7.5 Programmatically Add Items to a List or Combo Box
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?
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.
18.104.22.168 Filling a list box by calling the AddItem method
22.214.171.124 Filling a list box by modifying the RowSource property
126.96.36.199 Filling a list box by creating a list-filling callback function
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.
188.8.131.52 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
184.108.40.206 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.
220.127.116.11 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.)
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.
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 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.
|[ Team LiB ]|