How to require subforms to return results relative parent forms

[Shona Rennie]

Hi I am a self taught user of access and would appreciate some advice.

I have a main form which holds employee details.

1st continuous subform which shows which sites each employee is or has been working on.

A 2nd continuous subform which shows further details of the employee on each specific site such as start/end dates, car status, failure of 24month rule, etc.

The main form and subform are linked with no problems.

However, the links between the 2nd subform and main form are not correct.

For example:Employee A, is set up with contract number 123 (1st subform) and has a start and end date on the 2nd subform.

Employee B, is also set up on contract number 123 as he also works there but he has a different start and end date.

When you go back into employee A, the 2nd subform shows both data entries instead of only the one relevant to him, and vice versa.

Main form is linked by EEID to subform 1, subform 1 is linked by ContractNumberID to subform 2

I would appreciate some advice on the relationships which may resolve this and will apologise in advance if I have not provided enough information.

Thanks
Shona

[jimatqsi]

You can use more than one column in the form linkage. Instead of selecting the fields directly, click on the little ... icon to get a graphical tool to use multiple columns for the lingage.

Jim

[Shona Rennie]

Hi Jim, Thank you so much for getting back to me. I have tried to add a 2nd link in the Child/Masterfields however I think that my relationships are wrong.

MainForm is Employee Information - EEID (Key)

- linked to the 1st subform using EEID

1st Subform is Site Info Table - SiteContractNumberID (Key)

- linked to 2nd subform using SiteContractNumberID

2nd Subform is employee site information - MileageID (key)

Between the main EE table and Site Info table I have the following relationship


LnkEESite Table - EEID (key) - duplicates OK
ContractSiteNumberID (key) - duplicates OK

This table is populated/links the 2 tables together - not sure if entirely necessary.

Mileage Info Table - is linked to the ContractSiteNumberID

I tried to have a link table between Site Info table and Mileage Info Table but nothing was being populated when entering data.

So it would appear that I need some kind of relationship between the Main Table - EEID and the Mileage Info Table so that the Mileage Info table only returns data relevant to both the Main EE table and 1st subform.

Thanks for your patience!!!!

[jimatqsi]

No, you do not need a direct relationship between the main form's record set and the 2nd subform. There must be an implied relationship, by virtue of the fact that the first subform must be related to both the main form and the 2nd subform.

The link from subform one to subform two should NOT have anything to do with LnkEESite Table - EEID (key). That is on the main form. You can only link subform 2 to subform 1 by using data items from those two forms/recordsets. In other words,
LnkEESite Table - EEID to ContractSiteNumberID - EEID
should be
ContractSiteNumberID - EEID to Mileage table's EEID

Jim

[Shona Rennie]

Jim

Sorry for the delay I have been away - thanks for getting back to me.

I will need to experiment further as I still cannot get the 2nd subform to only return the results relative to both the 1st subform and the main form.

The 2nd subform is still returning all records for a contract number in the 1st subform regardless of whether or not it is relevant to the main form - not sure where I am going wrong but I clearly have not got the relationships right - but thanks for the effort.

Shona

[Shona Rennie]

Hi

I managed to solve my subform problem and both now link correctly. :-)

However..........I there is now a problem with the requery.

When selecting an employee from the main from then clicking on to a record (contract number) on the 1st subform, the related records on the 2nd subform should show.

However, they only show after I have clicked on to a contract number on the first subform, clicked on to a different contract number and then clicked back to the first.

Any advice would be much appreciated.

Thanks
Shona