Subform Link Master/Child Question (1 Viewer)

Hello,

I have a table (tblCompanies) that is related to a junction table (tblCompaniesProjects). As expected, my parent form is based on tblCompanies and my subform is based on tblCompaniesProjects.

Basically, I would like to link two child fields 1. CompanyID (FK in tblCompaniesProjects) and 2. ParentCompanyID (FK in main table, tblCompanies) to the PK in tblCompanies.

The ParentCompanyID is only used when a company is taken over by another company. My goal is to merge the data with the parent company record (i.e. so it shows project associations with the former companies). I do not want to insert a separate subform for this info but will if it is the only way.

When I try to link the two fields to the Master field using subform field linker, no data shows up. I changed the recordsource already to include the ParentCompanyID field in the query and inserted into my subform.

I can link each child field individually and the related data shows but not both at once.

Any ideas, suggestions or criticisms of doing this?

Thank you!

I would get the combined list of companies, including parent companies in the main form. Then the child Project sub form is easy.

To get a combined list of companies you could a Union query to combine the Company ID's.

For example
(Select tblCompaniesA.CompanyID
from tblCompanies inner join tblCompanies AS tblCompaniesA on tblCompanies.ParentCompanyID = tblCompaniesA.CompanyID) union
(select CompanyID from tblCompanies)

if you want to link a subform using multiple links try doing the following

On the main form create a non visible text control (lets call it SFLink) and in its controlsource put =[link1] & [link2]

in your subform recordsource create a new column (can also be called FLink) which combines the two links as above

Then your subform child and master fields are SFLink

Thank you both for your suggestions.

Cronk: I tried going the union query route but had problems because I am referring to the same table. I assume that is why you suggest using tblCompaniesA and tblCompanies.

CJ London:I tried using calculated fields but had not come up with your idea. I was going to try a Dlookup next but your solution sounds easier.

I will try both and see if I can get this working. Thanks!

I think CJ has a more elegant solution but I think it might need some massaging.

The Child link is still CompanyID . It's just that the parent form's link can be one or two CompanyIDs, if I do understand your requirement.

For example if Company 3 has as parent Company 1, then for Company 3 displayed in the main form you want Projects with CompanyIDs 1 and 3. Company 2 having no parent should show Projects only against Company 2.

Maybe then a composite link based on a string nnnnkkkk where n is company 1 ID padded with zeros and kkkk parent company's company id.

The more I think about it, the more I'd be inclined to set the subform's recordsource in the current event of the parent form.

Hi Cronk: Basically, if Company 3 has parent Company 1, then I want to show Company 3's projects with Company 1.

Anyway, after trying what feels like an endless number of unsuccessful solutions, I think I am going to have to set the recordsource in the OnCurrent event of the main form as you suggested.

In case someone else needs to link two child fields to the same master field, this ended up being easier to solve than I thought. I made a query and put the SQL to set the subform recordsource into vba as follows and it works perfectly: