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 LinkMasterFields 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.google.c om...
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.RecordSource = _
"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?