Main form with 2 subforms linking 2nd sub form to the first subform

[Kent Bestle]

I am wanting to create a form that has two subforms. The Main form is for Orders. Each order may have multiple line items. That is what the first subform is showing in datasheet view, the line items of an order. The Master/child is based on SO#. So far so good. Now I want to add another subform that shows work orders(SFO#'s) for each of the orders line items in the first subform. Master/Child based on SFO#. The second subform when using the property settings to define the Master/child link does not show any of the first subform fields as a possible Master. The drop down only shows fields in the Main form. If this can be done, what I want to happen is when a user clicks on a SFO# field on the first subform I want the second subform to show the records from the SFO line items.
My other approach was since each form is based on a separate query I thought that I could just make reference to the SFO field in a parameter criteria. i.e. Forms!Mainform!SO# for the first subform to the Main. And for the second subform Forms!Mainform!Subform1.Form!SFO#. As you can see I have struggled to find the right syntax to reference the first subform in the parameter query.
Not sure it is possible to do this with either of these approaches or if either one will be functionally effective, meaning not real slow, but I appreciate any guidance toward achieving my goal.

[aytee111]

You do not need to follow the wizard, you can make up any fields to link. The way I do it is to add a hidden textbox to the main form which is then used as the linking field (SFO#), when the user selects an order detail record, populate the main form with the SFO and requery the second subform.

[Kent Bestle]

The SFO field does not exist in the table that the Main form is based on.

[aytee111]

Like I said, doesn't have to.

[Kent Bestle]

So are you saying that when a user clicks in the SFO field on the first subform, set the value of an unbound textbox on the mainform to the SFO# and then requery the subform?

[aytee111]

Yes, that will work. As long as that field on the main form is the one used to link the second subform.

[orange]

Here is an older, free MS Access video from Datapig that may help with the details of subforms.

It's older, but concepts are great.

[Kent Bestle]

That did the trick. Thanks