Calculated Controls in form/sub-form setup (1 Viewer)

silversun

Registered User.
Local time
Today, 04:22
Joined
Dec 28, 2012
Messages
204
Hi everyone.
I have a parent/child form setup as seen in the attached image. My forms are bounded to related tables.
Is there anyway of dynamically calculate the totals for each item and grand total in upper section that is invoice total as user enters the details of each item?
I would like to do the calculation here locally in the form and then save all data from both forms in related tables when I click on Next/New (not shown in the picture) buttons.

1596342492704.png

I have some codes that I calculate the detail totals as change and save in invoice detail table (and appears in Amount control) but the grand total (Total Paid) in invoice table is the part I need your help.
Please let me know if I am not clear enough
Please help me to find the solution.
Thank you very much in advance
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:22
Joined
Feb 28, 2001
Messages
26,996
Here is the basic issue: Your parent and child forms, though one nests into the other, are mostly independent of each other. Which means that if you do something in the parent, the child doesn't know about it, and vice versa. When you update the child form, there is no direct event available to the parent form to tell you that an update has occurred on a value in the child form.

The brute force method would be that if the sub-form gets changed, have a subroutine somewhere in a general module such that if you execute it, it EITHER requeries the parent form (by name) or just directly does the sums for you. If the main form uses aggregate queries to define the totals, telling that form to requery itself would solve the problem (perhaps). This is a brute-force method because it would hard-code the name of the parent form.

The trickier method is that if you are in a child form, then "Me" applies to the child form, not the parent. So... Me.Parent.Requery might do the job. However, if the parent's total slots are NOT defined by an aggregate query, a simple .Requery won't work. And unfortunately I don't see what is behind the scenes so cannot tell you what is best here. Hope I gave you some ideas at least.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:22
Joined
May 7, 2009
Messages
19,169
you need another table for Payments.
also a different form.

the form should have:
1. payment date
2. invoice number (the invoices being paid, 1 entry for each invoice number)
3. amount paid (can be partial/full)
4. mode (cash/cheque)
5. reference (check number/ note no, etc.)
 

silversun

Registered User.
Local time
Today, 04:22
Joined
Dec 28, 2012
Messages
204
Here is the basic issue: Your parent and child forms, though one nests into the other, are mostly independent of each other. Which means that if you do something in the parent, the child doesn't know about it, and vice versa. When you update the child form, there is no direct event available to the parent form to tell you that an update has occurred on a value in the child form.

The brute force method would be that if the sub-form gets changed, have a subroutine somewhere in a general module such that if you execute it, it EITHER requeries the parent form (by name) or just directly does the sums for you. If the main form uses aggregate queries to define the totals, telling that form to requery itself would solve the problem (perhaps). This is a brute-force method because it would hard-code the name of the parent form.

The trickier method is that if you are in a child form, then "Me" applies to the child form, not the parent. So... Me.Parent.Requery might do the job. However, if the parent's total slots are NOT defined by an aggregate query, a simple .Requery won't work. And unfortunately I don't see what is behind the scenes so cannot tell you what is best here. Hope I gave you some ideas at least.

Thanks for your reply.
I am still thinking about what you and arnelgp suggest. I have my database attached. you can look at it and see what would be the best scenario.
I was thinking about a temp table. Please tell me what you think.
I appreciate your help and other friends.
Thank you
 

Attachments

  • newDatabase3.accdb
    2.4 MB · Views: 206

silversun

Registered User.
Local time
Today, 04:22
Joined
Dec 28, 2012
Messages
204
Hi all friends,
I've not received any help since last week I've uploaded the database. Does it mean I need to reopen a new thread?
Do I have done something wrong?
Please if you know the answer write me a respond so that I can solve this issue and continue working on my database.
Thank you
 

Users who are viewing this thread

Top Bottom