Linking two subforms in datasheet view?

[ppwlee]

Hi,

I have created a main form with 2 subforms,

Both subforms are in datasheet view.

I want to be able to pick a record from subform1 and the corresponding
linked record (created by a 1-to-many relationship) will show up in
subform2.

My problem is, I can't create a link between the 2 subforms. I can
only link subform2 to the mainform, which is in single form view (I
want it in datasheet view to see all the records at once). Is there
any work around?

Note: I was thinking of selecting a record in subform1, "force" the
main form (using Dlookup??) to go to that record, then the subform2
will be able to reference to that record by using a "child/parent"
relationship. Can this be done?

Any advice is much appreciated.

Rgds,
Peter

[Ron2006]

There are a couple of ways you can do what you are describing.

Method 1: Not parent child but does keep them linked.
1) subform 1 keep as is
2) subform 2 have it based on a query that references the field in
subform 1 that controls it.
3) In the OnCurrent event of SUBFORM 1 issue a requery of subform2
4) If you create records in subform2 you will need to add an before
insert event to load the key information from subform1 into that field
in subform 2
Method 2: A true parent child relationship
1) On the main form place an unbound field which usually is NOT
visible.
2) In the OnCurrent event of subform 1 move the key field to that
field on the main form.
3) When on the form properties of subform 2 place the name of the
unbound field in the parent location and the matching field in the
child field. YOU WILL NEED TO DO THIS MANUALLY. The parent child
wizard will not function.
4) New records that you add in subform2 will automatically have the
linking field loaded.

Hope this gives you some ideas.

Ron