Cascading Combo Box Not Showing Data

[mfrish]

Hello, I have a 3-level cascading combo box in a split form in the struture:


Operating Process -> System - > Equipment Class


This form is bound to a table with a list of Assets.


The reference tables are setup as follows:


refOperProc
....OperProcID (PK)
....OperProcName
refSystem
....SystemID (PK)
....SystemName
....OperProcID
refEquipClass
....EquipClassID (PK)
....EquipClassName
....SystemID


The comboboxes work fine and show me only those options associated with the previous selection with my VBA code setup as follows:


Private Sub cboOperProcID_AfterUpdate()
Me.cboSystemID.RowSource = "SELECT refSystem.systemid, refsystem.systemname FROM refsystem " & _
" WHERE OperProcID = " & Nz(Me.cboOperProcID) & _
" ORDER BY SystemName"
Me.cboSystemID.SetFocus
Me.cboSystemID.Dropdown
End Sub


I can edit record #1 with no problem, and all three combo boxes show appropriate values. However, I am running into a problem when I edit record #2, then go back to view record #1. Record #1 seems to have lost the values previously input. They still exist in the bound table so I know they are being deleted, but they are not being shown in the combo box. So the user thinks that they didn't input enough data into that record.


How do I get the combo boxes to display the current values?


14412


Attached is my database. Thanks in advance for anyone's help.

[jonh]

You are only setting the rowsource when a value changes. So, if you change a value on one record and then move to another record the options in the lists wont match up properly. I.e. if the value of the control is 5 but there is no 5 in the list (because it's displaying the wrong values) the control will still have a value (5) but its text will appear blank.

Ensure all controls rowsources are in sync using the form's current event.

[mfrish]

You are only setting the rowsource when a value changes. So, if you change a value on one record and then move to another record the options in the lists wont match up properly. I.e. if the value of the control is 5 but there is no 5 in the list (because it's displaying the wrong values) the control will still have a value (5) but its text will appear blank.

Ensure all controls rowsources are in sync using the form's current event.

Thanks! That worked.