 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / March 2005Validating the sum of a subforms entries
Hi, I have a form with an embedded subform. The form is an invoice header with the invoice number and other related information including cost. The subform allows entries that belong to the invoice, but the belong in separate accounts. My problem is this: I need to validate that the sum of the lines entered in the subform equal the total invoice cost. I'm using Access2k so there is no record exit event. I can't use BeforeUpdate since this fires before the entry of any subform lines. The closest I came was Cancelling the subform exit if the total doesn't match. The problem with that is the subforms record isn't updated or inserted to allow checking the final sum since the record is not final. Is there anyway around this problem. Are you trying to make sure that the total of the cost amounts on the subform doesn't exceed the total on the main form cost amount? > Hi, > [quoted text clipped - 10 lines] > updated or inserted to allow checking the final sum since the record is not > final. Is there anyway around this problem. Yes! More than that I want to be sure that if a user goes in to an invoice and changes the (parent) total or the amount of any of the (child) associated lines that the total of the (parent) invoice matches the sum total of the child records. Any ideas how to code this or at least what event on can hang my code on. > Are you trying to make sure that the total of the cost amounts on the > subform doesn't exceed the total on the main form cost amount? [quoted text clipped - 20 lines] > not > > final. Is there anyway around this problem. Anyone? Anybody? > Yes! More than that I want to be sure that if a user goes in to an invoice > and changes the (parent) total or the amount of any of the (child) associated [quoted text clipped - 26 lines] > > not > > > final. Is there anyway around this problem. To begin with, basic database theory says you shouldn't store the total at all; instead, you should calculate it on the fly whenever you need it. Now, there can be counter-arguments as to why you might actually need to store the invoice total at header level (actually all major ERP's do!). In that case, the Before Update or After Update or On Change event of the line item amount will do the job just fine, while the control bound to header level total in the mainform should be disabled/locked, so the user can't change it anyway - why should they have access to it? HTH, Nikos > Anyone? Anybody? > [quoted text clipped - 42 lines] >>> >>>>final. Is there anyway around this problem. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.