Solved A Parent and Child Form for data entry (1 Viewer)

[Pat Hartman]

I think once I feel comfortable enough with access and all features involve in a DB then I will switch to a different platform.

Once you understand how Access works and what it does for you and the time it saves, why would you want to switch to a different platform?

To expand on binding:
Forms/reports
A form/report is bound to a recordsource which can be a table, a querydef, an SQL string.
A form/report is unbound when its RecordSource property is empty. You can fill the RecordSource property in the Open event of the form if that is easier for you.

Controls on Forms/Reports
A control is bound to a field that is contained IN the form's Recordsource by using the ControlSource dropdown and selecting a field.
A control is unbound either when the ControlSource is empty or when the ControlSource contains an expression.

Combos and Listboxes on Forms/Reports
Combos and listboxes have two points of binding:
a. The RowSource is bound to a table, querydef, or SQL String and that is what is used to produce the dropdown list.
b. The Combo/Listbox is bound to the form's RecordSource when you chose a column from the RecordSource in the dropdown of the ControlSource.

 

[Isaac]

I think in situations where the end user has to be doing serious work on child records, bound forms are often a good way to go. And, using unbound forms simply for lack of having learned bound forms is a bad idea. Otherwise, I love unbound forms. Personally I think bound forms have too much going on that happens automatically and (more importantly), non-intuitively, or, when the user wouldn't expect such a thing to happen (compared to their typical experiences on web pages and other apps). Of course, you can write all kinds of event code and tap form cycling settings to try to avoid some of that, but on simple, relationship-less forms, I often use unbound ones. It is obviously good to be familiar with both.

 

[Pat Hartman]

If you're going to roll your own, you shouldn't be using Access. You are paying a huge price in awkward distribution and lack of web access for no reason.

If you think bound forms are a problem, you don't understand them. Once you understand how events work, you have complete control over when and if data gets saved.

I once took over an app created by someone who did not understand event processing and ended up removing 5,000 - 10,000 lines of code. This person had put code in at least 5 control events for every control on every form and still could not control validation and prevent invalid data from being saved. He hadn't put a single line of code in the Form's BeforeUpdate event which is where his validation code belonged. The code was so bad that on a couple of forms, the 5 events he chose for each control validated control1 in the first control, control1 AND control2 in the second control, control1, control2, AND control3 in the third event. Both of the forms where he tried this method had over 40 fields on them so you can imagine his frustration when nothing he did ever worked. The form popped up error messages for all errors and then saved the bad record because he had no clue how to stop it.

 

[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.

 

[Pat Hartman]

When my husband and I moved into our first house, the refrigerator we bought had a water tap in the door and needed to be connected to the water line under the sink. Being apartment dwellers, we didn't have a big selection of tools. We had a hammer and a screwdriver. So, my husband, the electrical engineer, used a screwdriver to make a hole in the cold water pipe so he could connect the water line to the fridge. Was this efficient, no? Was it a good idea? No, but he got an A for creativity and we were ultimately able to fix the leak he caused.

Use your tools correctly. Use the correct tool for the job.

 

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

Clearly the message indicates that the FK is not being populated so either there is no relationship, the relationship was added after the forms were created, or there is something that caused the form to loose the relationship.

Are you disagreeing with the statement that a defined relationship isn't necessary to make Master/Child links work? Check it out. If a defined relationship were required, there would be no need for the Master/Child property since Access would just populate the FK if the relationship existed and not update the FK when there is no relationship. There would be no reason to allow us to specify which fields are linked.

 

[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

 

[Pat Hartman]

silversun, you might want to review your relationships now to see if there is a mistake there. As I mentioned earlier, if you have a relationship defined (and you should), Access will automagically set the Master/Child link to match the defined relationship.