Fill ListBox with Array contents

[Nick Coe \(UK\)]

Acc2k WinXPPro all service packs and updates applied.

When filling a listbox control on a form using a callback
function the listbox only enters the function once to
initialise and that's it. Even if true is returned it
doesn't re-enter to get values.

I'm using slightly modified code from Chapter7 frmTableList
from the ADH2K.
The original from the ADH works just fine when imported into
my mdb.
I've decompiled and imported all objects into a new mdb with
no success.

I'm damn sure I've screwed up or am missing something
obvious...

I've reproduced it as follows:
1) Create a new mdb.
2) Creat an unbound form.
3) Insert unbound list box on form called lstTestFunc.
4) Paste function below into form's code class.
5) Put function name (without =) as listbox's rowsource.
6) Put a breakpoint on the End Select of the code.
7) Pop the odd me.lstTestBox.Requery here and there in the
form events.
8) Open the form or requery it (alt-F9) to fire the listbox.

It only ever gives an intCode = 0.

code start*************
Public Function TestFillList( _
ctl As Control, varID As Variant, lngRow As Long, _
lngCol As Long, intCode As Integer) As Variant
' Fill a list box from an array
' For test purposes is predefined with Array function
' Nick Coe June 2004
' Based on Ch7 - frmListTables - ADH2000 Getz, Litwin &
Gilbert - Sybex

' These variables "hang around" between
' calls to this function.
Static sastrNames() As Variant
Static sintItems As Integer

Dim varRetval As Variant

On Error GoTo HandleErr

varRetval = Null
Select Case intCode
' Initialize
Case acLBInitialize
'sastrNames = saHelpFile
'Test function with dummy string values
ReDim sastrNames(4)
sastrNames = Array("1", "2", "3", "4", "5")

' Set up variable to hold the number of names.
sintItems = UBound(sastrNames)

' Tell Access that the list box is OK, so far.
varRetval = (sintItems > 0)

Case acLBOpen
' Get a unique ID number for control.
varRetval = Timer

Case acLBGetRowCount
' Get the number of rows.
varRetval = sintItems

Case acLBGetValue
' Get the actual data for the row.
varRetval = sastrNames(lngRow)

Case acLBEnd
' Clean up (release memory)
Erase sastrNames
End Select
ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case 9
'Ignore subscript out of range
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Fill List Box Test"
End Select
Resume ExitHere

End Function

Private Sub Form_Open(Cancel As Integer)
Me.lstTestFunc.Requery
End Sub
code end*****************

 

[Albert D. Kallal]

I'm damn sure I've screwed up or am missing something

obvious...

Yes...your fucntion never returns any values...so it does NOT
re-initalize....

Here is some code that will work:

Public Function TestFillList( _
ctl As Control, varID As Variant, lngRow As Long, _
lngCol As Long, intCode As Integer) As Variant

' Fill a list box from an array
' For test purposes is predefined with Array function
' Nick Coe June 2004
' Based on Ch7 - frmListTables - ADH2000 Getz, Litwin & Gilbert -Sybex

' These variables "hang around" between
' calls to this function.

Static sastrNames() As Variant
Static sintItems As Integer

Select Case intCode
' Initialize
Case acLBInitialize

'sastrNames = saHelpFile
'Test function with dummy string values
ReDim sastrNames(4)
sastrNames = Array("1", "2", "3", "4", "5")

' Set up variable to hold the number of names.
sintItems = UBound(sastrNames)

' Tell Access that the list box is OK, so far.
TestFillList = (sintItems > 0)

Case acLBOpen
' Get a unique ID number for control.
TestFillList = Timer

Case acLBGetRowCount
' Get the number of rows.
TestFillList = sintItems

Case acLBGetValue
TestFillList = sastrNames(lngRow)

Case acLBEnd
' Clean up (release memory)
Erase sastrNames
End Select
ExitHere:
Exit Function

HandleErr:
Select Case Err.Number
Case 9
'Ignore subscript out of range
Resume Next
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"Fill List Box Test"
End Select
Resume ExitHere

End Function

So, dump you me.listbox.requery in the on-load...as you do not want to do
that (it gets required anyway).

 

[Nick Coe \(UK\)]

Albert,

Of course! D@mn, I'm really making some stupid mistakes! Too
many long sessions at the keyboard I guess, at least I'd
hate to own up to being congenitally that stupid .

Thanks a lot, much appreciated.