SOLVED

One to Many setup - query doesnt work but parent/subform does

Steel Contributor

Hello Experts,

 

I have 2 tables: tblFacility and tblAmortization. 

forgive me but I am not good on the wording of the issue but let me try to explain. I make a query as below 

SELECT tblAmortization.*
FROM tblFacility LEFT JOIN tblAmortization ON tblFacility.ID = tblAmortization.FacIDfk;

in visual: 

tblFacility has only 1 record and I do not want >1.  tblAmortization is the many side. 

Tony2021_1-1684598423541.png

I add data to the query but I can only add 1 record in tblAmortization. If I try to add another record then it returns this message of "no record found in tblFacility with key matching field FacIDfk".  

Tony2021_0-1684598186598.png

 

HOWEVER, if I make a form (tblFacility as Parent) and put the subform (tblAmortization in the footer) and link it on tblFacility.ID and tblAmortization.AmortIDfk, I can add as many records as I want and I dont get that message above. 

 

I dont really see the difference between the query method and the form subform method since they are joined on the same fields.  I didnt really want to make a form and subform setup and simply wanted to make a query and add records that way.  

 

What am I doing wrong in the query?  

 

 

2 Replies
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 
If I try to add another record then it returns this message of "no record found in tblFacility with key matching field FacIDfk".

Say that tblFacility has a PK value of 1.
In your query you can add as many tblAmortization records as you wish, AS LONG AS FacIDfk = 1.

 

Note that tblFacility is not needed in your query.
Also note that data entry should be done in forms, not queries.

And that both parent form and subform should be based on a query with 1 table, not joined to the other table. The LinkMasterFields/LinkChildFields properties take care of the link between the two tables.

Hi Tom, thanks for the reply.
<Say that tblFacility has a PK value of 1.
In your query you can add as many tblAmortization records as you wish, AS LONG AS FacIDfk = 1.
==>I do understand this but I need FacIDfk to populate automatically (=1). So it seems the solution to that is the parent / subform and let LinkMasterFields/LinkChildFields properties take care of the link and the FacIDfk will populated automatically.
<Also note that data entry should be done in forms, not queries.
==>Ahh yes I forgot about that. thanks.
<Note that tblFacility is not needed in your query.
So, in my parent form I only have tblFacility and in the subform I have only tblAmortization and let the LinkMasterFields/LinkChildFields properties take care of the link (this is what I decided to do actually and it works). I was confused since I thought I could do the same thing in a query but it looks like I cant but as you said I should use a form to enter data.

thank you very much.
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 
If I try to add another record then it returns this message of "no record found in tblFacility with key matching field FacIDfk".

Say that tblFacility has a PK value of 1.
In your query you can add as many tblAmortization records as you wish, AS LONG AS FacIDfk = 1.

 

Note that tblFacility is not needed in your query.
Also note that data entry should be done in forms, not queries.

And that both parent form and subform should be based on a query with 1 table, not joined to the other table. The LinkMasterFields/LinkChildFields properties take care of the link between the two tables.

View solution in original post