How do I do this?

[Steve]

I have a form, which has two datasheet style subforms. One of the datasheet
subforms will contain data that is many-to-one with the main forms data, the
other subform is many-to-one with a single record from the first subform.
I'd like to set things up so that as I move through records in the first
subform, the data in the second subform is the data for the record currently
selected in the first subform.

Anyone have any ideas?

 

[bzamfir]

Hi,

Suppose your first subform is called subform1, and second subform is called
subform2.

In order to accomplish this, you need to do the following:

1. On the subform1, in design mode, add a textbox, call it txtID and bind it
to the id field the data from the subform2 is related to.
2. On main form add a textbox called txtSubID. Make it hidden (Visible =
false)
3. Set its ControlSource to =[subform1].Form!txtID
4. On the subform2, in design mode, add a textbox, call it txtSub2ID and
bind it to the foreign key field which is linked to data from the subform1
5. Select the subform2 in design mode and set the following properties:
Link child field = txtSub2ID
Link master field = txtID

And then run the form.

When you move through records in subform1, subform2 will be automatically
updated.

If you have any more questions, you can contact me.

HTH,
Bogdan Zamfir

__________________________
Independent consultant

 

[Van T. Dinh]

You can nest the second Subform inside the first Subform and then set
appropriate LinkMasterFields and LinkChildFields for the second Subform
Control.

Personally, I NEVER use nested Subforms: they are simply too complex for the
average users.

 

[Steve]

Thanks for the responses! The text box idea worked like a charm, and I agree
on the nested subforms. Actually, I tried them but I couldn't get a
datasheet form nested in another datasheet form.