Linking subforms to one another

[Trystyn]

Hi,

New to the site and hoping someone can help me with the following:

I have created a form which contains 2 subforms. The 1st subform is linked to the main form and displays data in continuous form view.

The second subform is unlinked. It shares several fields with the 1st subform but displays them in single form view.

What I would like to do is create a link between the two subforms so that when I select (click or double click) a record in the first subform, the equivalent record is displayed in the second subform.

Is this possible, and if it is, how do I go about it.

Thanks,

Trystyn

 

[pbaldy]

To my knowledge, you'd have to code it yourself (no master/child links equivalent). You'd either set the filter property of the second subform, or its record source property. You could also base it on a query that used the first subform as a criteria and requery it. In all cases, you'd use the current event of the first subform.

 

[YNWA]

What fields do they share?

Give us a break down of each field in each table and each form.

I think I have some code for this from someone on this forum.

 

[Trystyn]

Thanks for the responses.

I'm not knowledgeable enough on this to write my own code to do this. I will try to give a bit more information.

The main form is more or less a list of years from a table of years the field [Year] is linked to subform1 which is a list of species seen in that year. The field [Scientific Name] within this subform is the one which would be shared with subform2

Subform2 is a complete list of species with much other information and as described above, I want it to display the record connected by the field [Scientific Name]

Trystyn

 

[pbaldy]

The simplest method is probably the third. Base the second subform on a query that has a criteria on that field that refers to the appropriate control on the first subform. Then in the current event of the first subform, requery the second:

http://www.mvps.org/access/forms/frm0031.htm

 

[vbaInet]

There might just be another way using Link Master/Child fields.

1. Drop a textbox onto your Main form
2. Use the expression builder and set the Control Source to the ID of the 1st subform. This ID should be the ID that links the 1st and 2nd subform
3. Give a meaningful name to that textbox, maybe txtSub1ID
4. Put in the Link Master Fields property of the 2nd Subform txtSub1ID. Don't use the popup, just type it in. If you use the popup txtSub1ID will not be in the list.
5. For the Link Child Fields property put the ID field of 2nd subform

That should do it.

 

[Trystyn]

Sorry for the late response, got back to looking at this yesterday and managed to do it as you described.

Thanks for all the help!!

 

[vbaInet]

Goodie! Glad that worked for you.