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