Why is Listbox callback function acLBGetColumnWidth getting called too many times?

[George Allen]

I have been experimenting wiht using the Listbox Callback function to solve an issue I have with my db tool. The function below connects ADO to a SQL server database and table and reads those members of the table that fit the criteria and then fills in two columns in my listbox.

So far, it appears to init well, filling the array properly. What I am having a problem with is the control calls with the code acLBGetColumnWi dth (5) three times. the first two are correct, getting the sizes of the two columns, but then it calls a third time, with a -1 row and a 0 column and then crashes telling me I have an error, "Fill_Provider_ List may not be a valid setting....." yada yada.

Any ideas?

Function Fill_Assigned_Provider_List(fld As Control, ID As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Static Provider_Assigned_List() As Variant
    Static RowCount As Integer
        
    Const TWIPS = 1440

    Select Case code
        
        Case acLBInitialize                ' Initialize.
            DW_Connect
            
            rs_Provider.CursorLocation = adUseServer
            StrSQL = "Select * from dim.Provider where InactivationDate is null;"
            rs_Provider.Open StrSQL, DWConn, adOpenKeyset, adLockOptimistic, adCmdText
            
            ReDim Preserve Provider_Assigned_List(rs_Provider.RecordCount, 1) As Variant

            
            Fill_Assigned_Provider_List = True
            rs_Provider.MoveLast
            rs_Provider.MoveFirst
            
            fCount = 0

            For chkitm = 0 To rs_Provider.RecordCount - 1
                If Not IsNull(rs_Provider.Fields("ServiceSection")) And Not IsNull(rs_Provider.Fields("ProviderSID")) Then
                    fCount = fCount + 1
                    Provider_Assigned_List(fCount, 0) = rs_Provider.Fields("ProviderSID")
                    Provider_Assigned_List(fCount, 1) = rs_Provider.Fields("StaffName")
                End If
                rs_Provider.MoveNext
            Next chkitm
            RowCount = fCount
        Case acLBOpen                        ' Open.
            Fill_Assigned_Provider_List = Timer
            
        Case acLBGetFormat
            Fill_Assigned_Provider_List = -1
            
        Case acLBGetRowCount            ' Get number of rows.
            Fill_Assigned_Provider_List = RowCount
            
        Case acLBGetColumnCount    ' Get number of columns.
            Fill_Assigned_Provider_List = 2
            
        Case acLBGetColumnWidth    ' Column width.
            Select Case col
                Case 0:
                    Fill_Assigned_Provider_List = 1
                Case 1:
                    Fill_Assigned_Provider_List = 3 * TWIPS
            End Select

            
        Case acLBGetValue                    ' Get data.
            If row > 0 Then
                Fill_Assigned_Provider_List = Provider_Assigned_List(row + 1, col)
            End If


        Case acLBEnd
            rs_Provider.Close
            DWConn.Close                    ' End.
            
    End Select
End Function

[George Allen]

Never mind. I found my errors. As I played more wit hthe code it turned out I was reading one record past the end of my recordset. Basically, the "fcount = fcount + 1" code needed to move to follow the two feed lines. This got rid of my null first record and then also helped me land on the right record in the end. See, All I needed to do was try asking someone else and feel embarassed, to figure it out.

[ADezii]

I'm not sure if this is related to your problem, or if it even makes a difference, but shouldn't the Explicit MoveLast/MoveFirst operation be done prior to redimensioning the Provider_Assign ed_List() Array? I am referring to Code Lines 11 and 13 which I swapped. It is just that some Recordsets you must traverse prior to getting an accurate Record Count. If this is not it, let me know and I'll look into the matter further.

'***************************** Code intentionally omitted *****************************
                                                                                     '*
Case acLBInitialize                ' Initialize.                                      *
  DW_Connect                                                                         '*
                                                                                     '*
  rs_Provider.CursorLocation = adUseServer                                           '*
  strSQL = "Select * from dim.Provider where InactivationDate is null;"              '*
                                                                                     '*
  rs_Provider.Open strSQL, DWConn, adOpenKeyset, adLockOptimistic, adCmdText         '*
                                                                                     '*
  rs_Provider.MoveLast: rs_Provider.MoveFirst                                        '*
                                                                                     '*
  ReDim Preserve Provider_Assigned_List(rs_Provider.RecordCount, 1) As Variant       '*
                                                                                     '*
  Fill_Assigned_Provider_List = True                                                 '*
                                                                                     '*
'***************************** Code intentionally omitted *****************************

[George Allen]

Don't think that really matters since the Redim and the Move.First/Last are of two different items.

[ADezii]

What I actually was referring to was the fact that the 1st Dimension of the Provider_Assign ed_List() Array has a Record Count as its number of elements. Some Recordsets do not accurately return Counts unless they are traversed. This was not the case prior to Redimensioning the Array. Just thought that this may be contributing to the problem, but in any event you figured it all out. Nice job!