Sub-forms of Sub-forms

[John.Forr]

I'm creating a data-entry form for 3 tables. There is a one-to-many
relationship between table1 and table2 and a 2nd one-to-many between
table2 and table3. What I would like to do is have the user select
from a combo box on the main form a record from Table1. That would
filter the subform on the main form to just the records from Table2
with the PK from Table1. I've got everything working fine up to this
point, but am a bit confused on where to go next.

I need a mechanism for the user to pick one of the records from Table2,
and be presented with an entry for for all records in Table3 that have
the selected PK from Table2. I've tried putting a 2nd subform on my
main form, but am having trouble getting them to link together.
Something else I tried, is putting a command button on each row of
Table2's subform in continuous forms view to open up a seperate form
for Table3.

[Shawn Yates]

Try setting the Default view of the 2nd and 3rd subform to datasheet
view.
SY

[Rich P]

Greetings,

Here is something I came up with that should do what you are asking:

Do the Link Master/Child thing with the mainform and subform1. Subform1
will need to be in Datasheet view for this trick to work.

The 2nd subform - which will also be placed on the mainform (and also
needs to be in datasheet view) will have its recordsource be based on a
query of the table for that subform (a query for table3). You will use
a parameter in the criteria field of the query for the link field
between table2 and table3. This parameter will be a function that you
will create in the Access Module tab. It is a very simple function.

Create and save a standard module (not a class module). Then create a
Public variable of the type whatever the link field type is between
table2 and table3. If the link field is a number then

Public fieldnameVar As Integer

If it is a text field then

Public fieldnameVar As String

Then function is just this:

Function Param() As Integer (or String)
Param = fieldnameVar
End Function

Save the module

Then in the query for table3 in the criteria for the linkfield just add
the function name

Param()

You need to include the Parentheses here.

In the first subform, go to its design view then go the code module for
that form. Every form code module has 2 dropdown boxes at the top of
the module. In the left dropdown for your subform - select "Form". In
the right dropdown select "Current". This is the Current Event of your
subform. Here is where you set the value of the globar var fieldNameVar

Private Sub Form_Current()
fieldnameVar = Me.yourLinkfield
End Sub

When you select a row in subform1, that triggers the Subform's current
Event. You can then set the value of fieldnameVar to the textfield in
the form. If you open the query that contains the Param() as criteria,
you will see that the query gets filtered by the value from the row you
selected in subform1.

Lastly, in the main form you will have a command button. In the command
button you will requery your 2nd subform and the correspponding rows
will show up.

Private Sub Command14_Click()
Me.Form3.Form.Requery
End Sub

So it is Me. the name of your 2nd subform and follow that by
..Form.Requery

You will now see that rows in the 2nd subform that are related to the
row you selected in the first subform.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[John.Forr]

Thanks for the answer Rich P. It looks like that'll work so I'll give
it a try.

Rich P wrote:

Greetings,

Here is something I came up with that should do what you are asking:

Do the Link Master/Child thing with the mainform and subform1. Subform1
will need to be in Datasheet view for this trick to work.

The 2nd subform - which will also be placed on the mainform (and also
needs to be in datasheet view) will have its recordsource be based on a
query of the table for that subform (a query for table3). You will use
a parameter in the criteria field of the query for the link field
between table2 and table3. This parameter will be a function that you
will create in the Access Module tab. It is a very simple function.

Create and save a standard module (not a class module). Then create a
Public variable of the type whatever the link field type is between
table2 and table3. If the link field is a number then

Public fieldnameVar As Integer

If it is a text field then

Public fieldnameVar As String

Then function is just this:

Function Param() As Integer (or String)
Param = fieldnameVar
End Function

Save the module

Then in the query for table3 in the criteria for the linkfield just add
the function name

Param()

You need to include the Parentheses here.

In the first subform, go to its design view then go the code module for
that form. Every form code module has 2 dropdown boxes at the top of
the module. In the left dropdown for your subform - select "Form". In
the right dropdown select "Current". This is the Current Event of your
subform. Here is where you set the value of the globar var fieldNameVar

Private Sub Form_Current()
fieldnameVar = Me.yourLinkfield
End Sub

When you select a row in subform1, that triggers the Subform's current
Event. You can then set the value of fieldnameVar to the textfield in
the form. If you open the query that contains the Param() as criteria,
you will see that the query gets filtered by the value from the row you
selected in subform1.

Lastly, in the main form you will have a command button. In the command
button you will requery your 2nd subform and the correspponding rows
will show up.

Private Sub Command14_Click()
Me.Form3.Form.Requery
End Sub

So it is Me. the name of your 2nd subform and follow that by
.Form.Requery

You will now see that rows in the 2nd subform that are related to the
row you selected in the first subform.
Rich

*** Sent via Developersdex http://www.developersdex.com ***