Losing Info In Combo Boxes

[E.Q.]

Greetings,
I want to make it easier for users to select milestones based on training
objectives. The final result should be a filtered list in a combo box,
cboObjID

I have a main form (frmTracking) and subform (subTrainedOn). The main form
contains a combo box cboTrainingAs. I've tried to use that value to filter
the value in an unbound cbo in the subform. I've been able to do this by
including the code Me.subTrainedOn.Form!cboPlantArea.Requery in the
cboTrainingAs After UPdate event. (The SQL is Select
tblPlantArea.chrPlantArea from tblPlantArea WHERE
(((tblPlantArea.chrWorkGroup)=[forms].[frmTracking].[cboTrainingAs])) Order
By tblPlantArea.chrPlantArea)

I then wanted to use the results to filter another subform cbo by adding
Me.cboObjID.Requery to cboPlantArea "after update" event.

The SQL for cboObjID looks like this:
Select tblTrainingObjective.lngObjID,[chrPlantArea&": "&[chrObjectiveName}
as Concant FROM tblTrainingObjective
WHERE
(((tblTrainingObjective.chrPlantArea)=[forms]![frmTracking].[subTrainedOn].[form]![cboPlantArea]));

This works with regards to getting the display to work for data entery. The
problem is that I lose data as I page through the records using the
navigation buttons. The entry in the unbound cbo remains the same and the
entry in cboObjID blanks out.

Can this be corrected?

Peace.
EQC

How do I get

 

[Beetle]

You need to add code to requery the combo boxes in the OnCurrent event of the
form also. This will fire each time you change records. You may want to put
it in the current event of both the main form and the subfom just to make
sure everything gets updated as needed.

 

[E.Q.]

I tried adding me.cboOBJID.requery and similar code for the other combo
boxes, on the form_current event for both the form and subform, but that
didn't seem to work.
For example, if record no. 1 receives a value of "Sisinfection" from the
dropdown list in cboPlantArea then cboObjID will correctly populate with two
choices (displayed as "Disinfection: Hypochlorite" and "Disinfection:
Bisulfite"). I select on of those, move on to record two, change the plant
area to another option (say "Secondary") and will again get the proper
choices in cboObjID for the "Secondary" plant area. But when I page back to
record no. 1 on the subform I have "Secondary" remaining in cboPlantArea and
cboObjID remains blank.
I'd like cboPlantArea to keep the value implied by the value in cboObjID for
existing records but to be null and to be determined by a change on the main
form for new records.
Is that possible? If so, what would the code look like?
Peace.
EQC