Problems recalling fields....

[tim.breeding]

OK, I came upon this a few days ago and I thought I had it solved. It was solved as long as my forms weren't using to different values in the primary combo box.

I've built a little cascading combo box system and it's acting a little strangely. So I have a combo box that's populated by a table:

Projects(table)
Project_id...... Project_Name
.....3............... HU TAR
.....5 .............. DDU TAR

Etc…

There's another table:

Tbl_ewo_numbers

Project_id..... EWO_Num
...3................HU-01
...3................HU-02
...5............... DDU-01
...5................DDU-02

Etc….

Both tables contain an Autonumber column as I am storing numbers in the main table.

So, when you select a project from the first combo, there is code in the after_update section that sets the query for the next combo boxes:

Private Sub unit_AfterUpdate()
..Dim unit_des As Integer

..On Error Resume Next

..unit_des = Me.unit.Column(0)

..work_package.RowSource = "SELECT tbl_workpkg_wo.WorkPackage, tbl_workpkg_wo.wp_id,
........tbl_workpkg_wo.equip_id, tbl_workpkg_wo.WorkOrder " & _
........"FROM tbl_workpkg_wo " & _
........"WHERE (((tbl_workpkg_wo.project_id)= " & unit_des & "));"

..cti_ewo_num.RowSource = "Select tbl_ewo_numbers.ewo_num, tbl_ewo_numbers.id " & _
........."FROM tbl_ewo_numbers " & _
........."WHERE tbl_ewo_numbers.project_num = " & unit_des & " AND
.........tbl_ewo_numbers.ewo_num_used=False " & _
........."ORDER BY tbl_ewo_numbers.id;"

End Sub

It works fine, but when you close the form and reopen it. The cti_ewo_num and work_package combo boxes are blank. If you check the tables the values are stored correctly. But they're not showing up on the form. I put GoSub unit_AfterUpdate() in the Form_Load and Form Current sections to try and reset the rowsource for the two combo's. That worked as long as all my records used the same Project_id. Any clue?

I'm baffled as to how to relieve this annoyance.

Link To DB

Here is a link to the db. If you cycle through the records in frm_ewo_main and go to the last record which is DDU you'll notice what I'm talking about Work Package and EWO Number will be messed up. PW is 6100