Main and subform link problem.

[Tupacmoche]

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]

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

 

[Tupacmoche]

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]

in the attached schema

Nothing attached.

 

[Tupacmoche]

Here it is again.

 

[Mark_]

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]

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_]

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.

 

[Tupacmoche]

Do you mind sharing the code for these forms?

 

[Mark_]

@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]

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