I have 2 tables AREA and AREA_DETAIL and a form with two DBCombos bound to the tables. In the form_load routine I open the both tables and bind dbcArea. In the dbcArea_Change event, I filter the records from AREA_DETAIL to only show the records associated with the Area selected in dbcArea. The recordset refreshes properly when the user clicks dbcArea, but the results shown in dbcArea_Detail are wrong: every record from the table AREA_DETAIL appears in the list.
I have tried binding dbcAreas_Details in the Form_Load event and refilling and refreshing it at various points, all to no avail. I can do what I need to do by closing the recordset and reopening it each time dbcArea is clicked, but that is very inefficient.
Private Sub Form_Load()
rsAreas.Open "Select IdAREA, AREA From AREAS Order By AREA", cnSICC, adOpenKeyset, adLockOptimistic
rsAreas_Detail.Open "Select IdDETAIL, IdAREA, DETAIL From Areas_Detail Order By DETAIL", cnSICC, adOpenKeyset, adLockOptimistic
If rsAreas.RecordCount <> 0 Then
Set .RowSource = rsAreas
.ListField = "AREA"
.BoundColumn = "IdAREA"
Set .DataSource = Adodc1
.DataField = "IdAREA"
MsgBox "Areas have not yet been added to the database.", vbExclamation
Private Sub dbcArea_Change()
If dbcArea = "" Then Exit Sub
rsAsignaturas.Filter = "IdAREA = " & dbcArea.BoundText
If rsAreas_Detail.RecordCount = 0 Then
MsgBox "No details exist for the area " & dbcArea & ".", vbExclamation
Set dbcAreas_Details.RowSource = Nothing
Set .RowSource = rsAreas_Details
.ListField = "DETAIL"
.BoundColumn = "IdDETAIL"
.DataField = "IdDETAIL"