Access Cookbook - Ken Getz [150]
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.
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