Update Subform Control With Value In Parent Form

[Dal]

1 - I'm trying to use VBA to update the value of my subform fHeadersSubform[BillID] set to the value of the parent form fMainHeaders[BillID]. I thought best to assign it to the event BeforeUpdate of the overall form Me!. I do not want to hardcode the name of the parent form because I want it to be as dynamic as possible.


Currently receiving the error "You cannot add/change because a related record is required in table 'tBills'. So the issue is the VBA code is not working properly. Am I assigning the wrong event/ bad code? Also tried using the event of "entering the subform" to no avail.

2 - When I get the above to work how can I hide the BillID & HeaderID fields in the fHeadersSubform? I have visible set to no but they are still appearing. I can't imagine because it is a datasheet format.

[Micron]

The code may not be working "properly" but Access is - at least according to how you must have set your relationships. It's telling you that you cannot enter a child record because there is no parent record, which makes sense, yes? Maybe explain why you'd want to start a child record when there is no parent record.

Yes, it is because the form is a datasheet and you have that field in the results. If you must stay with a datasheet, then set the column width to 0 in your code. That will not stop anyone else from dragging it back though. The solution is don't include the field or use a continuous form instead. With some work, you can make it look like a datasheet if that is the look you really want.

[Dal]

1 - Thanks Micron, the parent record of BillID & MainHeaderID is present in the subform. I am trying to avoid entering the BillID & MainHeaderID each time if I choose to create a new Header under the current MainHeader (parent form). Basically if I start entering data in the subform[Header] I am choosing to enter a new record & do not want to have to enter data which should be inherited already from the parent record.

2 - Interesting, so the datasheet format is messing me up. What would you recommend to display all records of the subform from the single record in the parent form? I chose it because it was the quickest way to get what I want & I'm a newbie.

[Micron]

Do you have your Link Master/Child fields linked between the 2 forms (select subform control and see data tab on property sheet)? If you do, then you don't need to enter ID data in a subform. When you create a new record, the subform's linked field automatically gets populated.

2) solutions stated in my prior post

Why do you have most/all fields duplicated in your forms?

[Welshgasman]

You need to save the main form data, before you can set child form data.

[Dal]

Thanks Micron.

1 - I put an expression to feed BillID in the subform from the parent form. It works! BUT when I enter a record it tells me "a related record is required in tBills". What's strange is that the MainHeaderID is showing a 1, but it is not being fed at all. It's so strange. I cannot see how a record is required in tBills & clearly this cannot be the issue anyway as it is working fine when I just enter the data manually .

2 - Sorry, I remember a tutorial on continous forms now - big thanks.

[Dal]

You need to save the main form data, before you can set child form data.

Thanks Welshgasman, sorry how would you suggest to overcome? I have not added a new record/ changed data to the parent form. https://www.access-programmers.co.uk...-entry.312126/ The way I interpret thread #2 is that the main form saves itself. Not saying you're wrong here at all, but just stumped as to what I need to do here & why I'm getting the irrelevant tBills error saying I need a record in tBills also. Confused every which way atm.

[Dal]

Sorry it was informing me that I hadn't put anything in the BillID field of the main form which did feed the tBills.