Cascading Combo Box Data Disappears

[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?

I would post a link to my database but the site isn't allowing me.

Thanks in advance for anyone's help.

 

[pbaldy]

Are the combos all bound to fields in the table? Without code, that's the only way they're going to display a value when you return to the record. You can attach your db here if you want:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[CJ_London]

I suspect you have omitted to mention that you are using a continuous form or datasheet

if so, try this link

http://www.access-programmers.co.uk/forums/showthread.php?t=275155