Why does requery on combobox callback function not requery?

[George Allen]

I have a callback function that correctly fills a combobox (A) with two-column data on load. I have a second combobox (B) that acts as a filterchoice for the first. When the combobox B is updated, I have a AfterUpdate event to requery the combobox A. However, the process doe snot change a thing in combobox A. Watching the code run through I see the function correctly create the new array needed, but the function never goes to the acLBGetValue section to change the data.

Option Compare Database
Option Explicit
    Dim chkitm As Variant
    Dim ProvID As Integer
    Dim rs_Provider As New ADODB.Recordset
    Dim rs_Measure_Class As New ADODB.Recordset
    Dim rs_Measure As New ADODB.Recordset
    Dim rs_OPPE As New ADODB.Recordset
    Dim FilterCrit As String
    
    Dim DWConn As New ADODB.Connection
    Dim strCxn As String

    Dim StrSQL As String
    Dim StrSQLMeasClass As String
    Dim StrSQLMeas As String
    Dim StrSQLOPPE As String

    Dim AddSw As Boolean
    Dim editsw As Boolean
    
    Dim Entries As Integer

    
    Dim PrvChk As Variant

    Dim MID As Integer
    Dim PID As Integer
    Dim Provider_Assigned_List As String
    

Public Function DW_Connect()


    
    strCxn = "Provider='SQLOLEDB';Data Source=vhaomadmg;Database=NWIPerfMon;Integrated Security=SSPI;"
    
    Set DWConn = New ADODB.Connection
    
    DWConn.Open strCxn
    
   
End Function
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 ProvCount As Integer

    
    Const TWIPS = 1440
   
    Select Case code
        
        Case acLBInitialize                ' Initialize.
            DW_Connect
            
            rs_Provider.CursorLocation = adUseServer
            
            If Not IsNull(Me.cmb_Svc_Section) Then
                StrSQL = "Select * from dim.Provider where InactivationDate is null and ServiceSection like '" & Me.cmb_Svc_Section.Column(0) & "';"
            Else
                StrSQL = "Select * from dim.Provider where InactivationDate is null and ServiceSection is not null;"
            End If
            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
            
            ProvCount = 0
                
            For chkitm = 0 To rs_Provider.RecordCount - 1
                Provider_Assigned_List(ProvCount, 0) = rs_Provider.Fields("ProviderSID")
                Provider_Assigned_List(ProvCount, 1) = rs_Provider.Fields("StaffName")
                ProvCount = ProvCount + 1
                rs_Provider.MoveNext
            Next chkitm
            
       
        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 = ProvCount
            
        Case acLBGetColumnCount    ' Get number of columns.
            Fill_Assigned_Provider_List = 2
            
        Case acLBGetColumnWidth    ' Column width.
            Select Case col
                Case 0:
                    Fill_Assigned_Provider_List = 0
                Case 1:
                    Fill_Assigned_Provider_List = 3 * TWIPS
            End Select

            
        Case acLBGetValue                    ' Get data.

                Fill_Assigned_Provider_List = Provider_Assigned_List(row, col)

        Case acLBEnd
            DWConn.Close
            rs_Provider.Close
            Erase Provider_Assigned_List
            
    End Select
End Function

Private Sub cmb_Svc_Section_AfterUpdate()
    Me.cmb_Provider.Requery
    
End Sub

[ADezii]

Haven't you cleared the contents of the Array as indicated in Code Line #4?

Case acLBEnd 
  DWConn.Close 
  rs_Provider.Close 
  Erase Provider_Assigned_List

As an afterthought, it may be some form of Initialization problem, try:

Application.Echo False

With Me![cmb_Provider]
  .RowSourceType = "Fill_Assigned_Provider_List"
  .RowSource = ""
End With

Application.Echo True

[George Allen]

Well, that got halfway there. Now I am able to change box B and see new data in box A, but then if I change box B again, I end up with nothing in box A. I used your RowSourceType suggestion.

When I go through the second time the code jumps out when it hits the Redim statement at line #64. Is this a problem with Redim twice?

[George Allen]

Well, I fixed my own issue, but I think it was because there is something wrong with the code still, I just found a workaround. What I did was to stop building a new criteria string when I come in like I was:

            If Not IsNull(Me.cmb_Svc_Section) Then 
                StrSQL = "Select * from dim.Provider where InactivationDate is null and ServiceSection like '" & Me.cmb_Svc_Section.Column(0) & "';" 
            Else 
                StrSQL = "Select * from dim.Provider where InactivationDate is null and ServiceSection is not null;" 
            End If

And substituted that with a new set IF statement in the build of the array to capture the records I wanted based on the criteria filter in box A:

            For chkitm = 0 To rs_Provider.RecordCount - 1
                If (Not IsNull(Me.cmb_Svc_Section) And rs_Provider.Fields("ServiceSection") = Me.cmb_Svc_Section) Or (IsNull(Me.cmb_Svc_Section)) Then
                    Provider_Assigned_List(ProvCount, 0) = rs_Provider.Fields("ProviderSID")
                    Provider_Assigned_List(ProvCount, 1) = rs_Provider.Fields("StaffName")
                    ProvCount = ProvCount + 1
                End If
                rs_Provider.MoveNext
            Next chkitm

I wish I knew why the other version did not work, but I don't.

[ADezii]

I've reduced your Code to a simpler State while maintaining similar functionality. This was done in an effort to eliminate some basic possibilities as to why the Code is not working. I am able to Requery the List Box as many times as I like without any adverse effects. I've attached my Demo for you to view, hopefully giving you some insight into the problem. Download the Attachment if you are interested.

What I did was to stop building a new criteria string when I come in like I was:

But this would not account for

Case acLBGetValue                    ' Get data.

not being evaluated, would it?

[George Allen]

Thanks for the help

[ADezii]

You are quite welcome.