So, I have two comboboxes on my form. One has a vlaue list (A), then other uses a callback function to populate (B) items based on the combo A selection. If I select something in A, then the function correctly populates B. I can do this over and over. However, if I select an item in B, then go back to A to select a different filter, the callback function does not populate B. One thing I noticed was that the combobox.value is still set to the selected value, but clearing that does not help. Ideas?
Code:
COMBOBOX A AFTER UPDATE
Private Sub cmb_Svc_Section_AfterUpdate()
Redraw_Form
Application.Echo False
With Me![cmb_Provider]
.RowSourceType = "Fill_Assigned_Provider_List"
.RowSource = ""
End With
Application.Echo True
End Sub
COMBOBOX B CALLBACK 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
StrSQL = "Select * from dim.Provider where InactivationDate is null and ServiceSection is not null;"
' rs_Provider.CursorLocation = adUseServer
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
ProvCount = 0
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
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
Comment