Main and subform link problem. (1 Viewer)

Tupacmoche · Feb 22, 2018

Hi,

I'm having a problem linking main form to a subform. I have already done this when the first table in my schema (attached) is in the main form and the second table in my schema is in the subform. Now, I want to show records that are linked or related to the second table tblGiftInfo.GiftID (PK) to tblSoftDonorInfo.GiftId (FK). In Access, I'm using a tabbed control. The First tab called Gift has a subform that works fine the header part of the form has tblDonorInfo and the subform has tblGiftInfo no issues. But when, I try to add a new subform to show tblSoftDonorInfo the link box does not show tblGiftInfo as the first link table. It always shows tabDonorInfo which has no direct link to tblSoftDonorInfo. How can this be resolved?

arnelgp · Feb 22, 2018

have you setup the Link Master/Child Fields on the subform?

Tupacmoche · Feb 22, 2018

As, I mentioned in the original thread, I have done that for the first table in the attached schema. However, I can't link tblSoftDonorInto to the main table since there is no join field in it. The link must be made to tblGiftInto.GiftID (PK) = tblSoftDonorInfl.GiftID (FK). This is the issue the tblGiftInto is not available to link into.

jdraw · Feb 22, 2018

in the attached schema

Nothing attached.

Tupacmoche · Feb 22, 2018

Here it is again.

Mark_ · Feb 22, 2018

I'd make a query that has TblGiftInfo and TblSoftDonorInfo in it. It would have DonorID from tblGiftInfo and all fields from TblSoftDonorInfo. This will allow you to run your subform off the query but match to the parent's DonorID.

Tupacmoche · Feb 22, 2018

Thanks that worked. Just curious why the need to link to the first table on the food chain? What if one is working with a very complex schema where a 'remote' table somewhere down the food chain does not contain a link to the first table, how is that handled.

It will come up sooner or later.

Mark_ · Feb 22, 2018

If you can't follow a set of links back to the "First table", or if you need to go up and down a set of links so you cannot get a set of one to many relationships, you need to rework how your tables are set up.

The suggestion I gave is based off of "Customer - Purchase order - line item" You can get every line item if you make a query that looks up the purchase order for each line item, then grabs the customer from the purchase order. You can even do the same to identify specific customers who have purchased specific items by linking the same query up through the product in a line item to find "Customers who bought banana hats", even grabbing the date from the purchase order to find "Customers who bought banana hats in April".

If you can think of a scenario where you can't work out how to get from A to B without there being an ambiguous step, odd are you shouldn't be trying it in the first place.

Pat Hartman · Feb 22, 2018

I'm not sure that what you just did actually worked.

When you have a main form with two subforms (the tab control is a red herring - it has no impact on this discussion), and subform1 is related to the main form but subform 2 is related to subform 1, you have do make the connection a little differently. You include the reference to the subform name. I've attached two pictures. The main form is unbound. It has only filter fields. The subform on the left is acting as the controller. Clicking on an item in the list, the subform on the upper right shows the details and then the subform on the lower left shows the next level down from the second subform.

Tupacmoche · Feb 23, 2018

Do you mind sharing the code for these forms?

Mark_ · Feb 23, 2018

@Tupacmoche,

Are you trying to show grandchildren to the parent on the 2nd tab or are you trying to show children for the child shown on the first tab?

My advice was for showing all grandchildren to the parent record.

Tupacmoche · Feb 23, 2018

As the attached schema indicates, I'm showing grand-children.

Pat Hartman · Feb 23, 2018

What code are you looking for? I highlighted in yellow on the property sheet, the property that links form 2 with form 3. When the master/child links are properly set, Access will synchronize the forms. When you click on a new line in the list form, Access syncs forms 2 and 3 based on the master/child links. That is the magic of a RAD tool properly used.

This form shows all three at once. As I understand your layout, you have form 2 on a different tab from form 1. Nothing changes. The tab control does not act like a container and so it doesn't affect the reference path from one object to another.

This is a picture of the master/child links for the second subform so sfrm1 is the master in this case.

Main and subform link problem. (1 Viewer)

Tupacmoche

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Tupacmoche

Registered User.

jdraw

Super Moderator

Tupacmoche

Registered User.

Attachments

Mark_

Longboard on the internet

Tupacmoche

Registered User.

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Attachments

Tupacmoche

Registered User.

Mark_

Longboard on the internet

Tupacmoche

Registered User.

Pat Hartman

Super Moderator

Attachments

Similar threads

Users who are viewing this thread