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.