Why does switching between subforms change records displayed?

Question

I have a form with 2 subforms

The main form has a 1:many relationship w Subform1 and Subform1 has a 1:many relationship w Subform2.  The Link Master Fields & Link Child Fields properly synchronize data between all 3 forms.

There is 1 bit of strange behavior in the following circumstance

1. There are multiple records in the table referenced by Subform1 for a record referenced by the Main form
2. I select (click) a record in Subform1 & the corresponding records appear in Subform2
3. If I then click a record in Subform2, the record in Subform1 CHANGES - & since the 2 subforms synchronize correctly, the selected record in Subform2 now also CHANGES to a DIFFERENT record than was displayed in Subform2 when I selected it

Note: If I start off by selecting a record in Subform2 w/o first selecting a record in Subform1, the record I select stays selected, which is the desired behavior.

I can get around the bizarre behavior programmatically by adapting the code in "ACC: Using Code to Dynamically Synchronize Two Forms (95/97)" (http://support.microsoft.com/kb/149940/en-us), but why should I need to?

Answer 1

I can get around the bizarre behavior programmatically by adapting the code in "ACC: Using Code to Dynamically Synchronize Two Forms (95/97)" (http://support.microsoft.com/kb/149940/en-us), but why should I need to?  

Because, as Ken suggested, you have two separate subforms linked to the main form. For what you want, you would need nested subforms and you can't nest a subform on a continuous form. 

So, to keep subform 2 in sync with subform 1 you can't link subform 2 to the main form, or even to subform 1. You have to synchronize them.

Answer 2

I'm not completely clear as to how your form and subform is set up.  It sounds from your description as though both subforms are independently housed within the same parent form.  For this they would have to be correlated.  The easiest means of doing this is via a hidden unbound control in the parent form whose ControlSource is a reference to the key of the first subform, and whose name is the LinkMasterFields property of the second subform.  This is an inefficient method of correlation, however, and a far better solution in terms of performance is to base the second subform on a query which references the key of the first subform as a parameter.  The second subform is requeried in the Current event procedure of the first subform.

The other option, and the simplest to implement, is for the second subform to be nested within the first subform, but this requires the latter to be in single form view.

You'll find examples of both solutions as CorrelatedSubs.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file uses data from Northwind to show the order dates per customer in one subform, and the order details per order in the second subform.  In one example both subforms are in continuous forms view and correlated as described above so that navigating to an order date in the first requeries the second; in the other the second subform is nested within the first, which is in single form view and includes an unbound combo box to navigate to an order from those made by the customer currently selected in the parent form.