One sub form linked to another

[Paul]

I have a basic form. On it are two sub forms. The top sub form has a
list of records. When a user clicks on a new row within the top sub
form. I want the RecordSource of the bottom sub form to change
according to some data in the row of the top sub form.

Main Form Name: Form1
Top Sub Form Name: frmT1_Datasheet
Bottom Sub Form Name: frmT2_Datasheet

The code in frmT1_Datasheet is:

Private Sub Form_Current()
[Forms]![Form1]![frmT2_Datasheet].Form.RecordSou rce = _
"SELECT * FROM tblT2 WHERE ID = " & Me![Number]
End Sub

IT DOES WORK! HOWEVER...it does not work when first loading Form1. I
get the message, "Run-time error '2455': You entered an expression
that has an invalid reference to the property Form/Report".

I'm guessing that it is because frmT2_Datasheet is not loaded yet.
After it is loaded it works fine.

Is there another way to do this? Or is there a work around?

[Larry Linson]

Your description leads me to believe that the RecordSource of the Form
embedded in the first Subform Control is related one-to-many to the data in
the main Form, and that the RecordSource of the Form embedded in the second
Subform Control is related one-to-many to the data in the Form embedded in
the first Subform Control. An appropriate approach to handling such data
would be to embed the second Subform Control in the form embedded in the
first Subform Control, rather than embedding it in the main Form. That will
likely avoid the error you describe.

The expectation would be that the Fields listed in LinkMasterField s are in
the RecordSource of the main Form and the LinkChildFields are in the
RecordSource of the Form embedded in the Subform Control. However, you can
use a reference to a Control -- perhaps you could use this rather than
trying to set the RecordSource of the Form embedded in the Subform Control?

Larry Linson
Microsoft Access MVP

"Paul" <pw****@hotmail .com> wrote in message
news:b8******** *************** ***@posting.goo gle.com...

I have a basic form. On it are two sub forms. The top sub form has a
list of records. When a user clicks on a new row within the top sub
form. I want the RecordSource of the bottom sub form to change
according to some data in the row of the top sub form.

Main Form Name: Form1
Top Sub Form Name: frmT1_Datasheet
Bottom Sub Form Name: frmT2_Datasheet

The code in frmT1_Datasheet is:

Private Sub Form_Current()
[Forms]![Form1]![frmT2_Datasheet].Form.RecordSou rce = _
"SELECT * FROM tblT2 WHERE ID = " & Me![Number]
End Sub

IT DOES WORK! HOWEVER...it does not work when first loading Form1. I
get the message, "Run-time error '2455': You entered an expression
that has an invalid reference to the property Form/Report".

I'm guessing that it is because frmT2_Datasheet is not loaded yet.
After it is loaded it works fine.

Is there another way to do this? Or is there a work around?

[Paul]

Thanks for your response Larry. I don't completely understand your
post. However, I have a few comments.

First of all, the main form (Form1) has nothing to do with the two sub
forms. It is just a holder for the two sub forms. So there is no
linkage between Form1 and the two sub forms.

Second, both sub forms (frmT1_Datashee t and frmT2_Datasheet ) are
datasheet forms. So I cannot embed T2 into T1; unless you meant into
the header or footer of the form. I would prefer not to do that. It
doesn't seem very clean.

With that in mind, would what you said earlier still work?

[Bob Quintal]

pw****@hotmail. com (Paul) wrote in
news:b8******** *************** ***@posting.goo gle.com:

I have a basic form. On it are two sub forms. The top sub
form has a list of records. When a user clicks on a new row
within the top sub form. I want the RecordSource of the
bottom sub form to change according to some data in the row of
the top sub form.

Main Form Name: Form1
Top Sub Form Name: frmT1_Datasheet
Bottom Sub Form Name: frmT2_Datasheet

The code in frmT1_Datasheet is:

Private Sub Form_Current()
[Forms]![Form1]![frmT2_Datasheet].Form.RecordSou rce = _
"SELECT * FROM tblT2 WHERE ID = " &
Me![Number]
End Sub

IT DOES WORK! HOWEVER...it does not work when first loading
Form1. I get the message, "Run-time error '2455': You entered
an expression that has an invalid reference to the property
Form/Report".

I'm guessing that it is because frmT2_Datasheet is not loaded
yet. After it is loaded it works fine.

Is there another way to do this? Or is there a work around?

Yes there is. Create a hidden, unbound textbox on Form1. Let's
call it txtLinker,
In frmT1_Datasheet . put the following code in the form's current
event.

Me.parent.textl inker.value = me.number

Now you can setup the rowsource of frmT2_Datasheet to be SELECT *
FROM tblT2. Just make the link parent fields property to the
txtLinker created above.

--
Bob Quintal

PA is y I've altered my email address.

[Paul]

Thanks Bob! I got it to work.