Solved A Parent and Child Form for data entry

[Isaac]

If you're going to roll your own, you shouldn't be using Access.

I think that's a bit of an overstatement, but you're entitled to your opinion.

 

[silversun]

Hi. I thought so. What do you want to do with the value selected from the dropdown? If you want to store it in your table, then you would select the field name for the control source property.

@ theDBguy
Hi,
I want to save that value in another table. I did put destination table/field in Control Source and then a new issue came up. Since the new generated Primary Key in T_invoice is not saved yet so the child table doesn't let me to save the related record because of missing a foreign key in T_invoice_details.
Please see the attachment. I am showing only tables that I am working on for now. The second image shows the error message.

I've removed the relationships temporarily just to test T_invoice and T_invoice_details easier.
Your helps are appreciated.
Thanks

 

[silversun]

Removing the relationships isn't the solution. There is some other problem and it is probably that the master/child links are not set for the subform.

When you move focus from the main form to the subform, Access automatically saves the parent record because it KNOWS that in a parent/child relationship, the parent record must exist before you can enter the child records so your parent record HAS been saved since I can see that focus is in the child record.

@ Pat Hartman
Thanks for time you and other folks spend to explain.
The reason I removed the relationships was to test form T_invoice and form T_invoice_details are working together properly without errors from other fields that might left empty or entered a wrong value during the test.
This is the beginning, once I get my two major form/sub-form working well then I will restore relationships and add more components to easily enter all data.
Form set up is a Form-subForm setup as theDBguy recommended previously.
My issue is the part: " ... move focus from the main form to the subform, Access automatically saves ..." is not happening properly. (

 

[silversun]

Removing the relationships isn't the solution. There is some other problem and it is probably that the master/child links are not set for the subform.

When you move focus from the main form to the subform, Access automatically saves the parent record because it KNOWS that in a parent/child relationship, the parent record must exist before you can enter the child records so your parent record HAS been saved since I can see that focus is in the child record.

@ Pat Hartman
I've setup my forms as parent/child relationship. I am not sure why Access is not saving parent/child records the way you are saying. Could you please help me to fix this issue?
Thank you

 

[isladogs]

@silversun
Recommend you read my extended article Relationships and Referential Integrity.
It explains with examples, the purpose of using relationships to maintain referential integrity between tables and the use of cascade updates/deletes.
It is a three part article but the section relevant to you is in the first part.

 

[cheekybuddha]

Looking at your screenshot you have set up table T_invoice_details incorrectly.

You do not need the invoice_id field. Create the relationship to table T_invoice on field detail_id.

If ever you need to know which invoice a detail belongs to you the query against the joined T_invoice table.

Scratch all that - was having an obvious brain-fart at the time.

You don't want detail_id in T_invoice

hth,

d

 

[isladogs]

@Pat Hartman
I don't completely agree with your final paragraph above.
Access will ALWAYS populate master/child Links where RI exists together with cascades
The error message in post #26 indicates that isn't the case.
So it may well be the 'problem here' though I'm unclear from the OP's comments whether the error was before or after the relationship was removed

 

[silversun]

The message you are getting indicates that the Master/child links are not correctly set. When you set the master/child link, you are telling Access what the PK of the parent form is and what the FK in the child form is so when you create a new record in the child form, Access copies the PK from the parent (which is why Access saves the parent first) and places is in the FK of the child automatically. You could of course, write code to do this yourself but WHY? It is always best to use the features Access provides.

Please open the main form in design view.
Click to select the subform control - not the subform itself.
Paste a picture of the Master/Child properties.

PS, although I agree with Colin that you should be using defined relationships and also enforcing Referential Integrity, that isn't the problem here. Access doesn't care if there is a relationship defined or not. Usually when there is a relationship defined, Access will automatically populate the Master/Child links but I suppose there are some circumstances where the auto population doesn't happen and I have experienced that myself.

@ Pat Hartman
I found the issue here. When I looked at Master/Child properties as you mentioned I realized it was set incorrectly. As you can see in the image here Access had selected inv_date as Master Field and invoice_id as Child Field where as it should be invoice_ID and invoice_id as Master and Child Field respectively. You were right.

I changed it to invoice_ID as Master and invoice_id as Child Field and it is fixed now.

Thank you for your guidance.
Thanks all other people who discussed all about this feature of Access, you all helped me a lot.

 

[isladogs]

Hi Pat
I agree totally that master/child Links don't require a relationship.
I was referring to the error message shown in post #26