Subtotals must equal total before continuing

[Randy]

I have a form with empolyeeID, date, District, . I have a subform with
employeeid, customer, hours, Miles. What I need is enter Total Hours for an
employee, either main form or subform, I'm not sure. In the subform I may
have 5 different customers with hours entered for each. The sum of hours
for all customers in the subform must equal the total hours for the
employee. Example: Employee: 8.0 Hours. The Jones Company 2.3 Hours, The
Smith Co. 4.5 Hours, The Johnson Co., 1.2 hours Total 8.0 Hours. I need
something to prevent me from moving to a new record if the hours for all
company's do not agree with the toal employee hours. The user must go back
and correct the customer hours. This has been driving me crazy...Any help
would be appreciated...Randy

 

[Mike Painter]

I have a form with empolyeeID, date, District, . I have a subform
with employeeid, customer, hours, Miles. What I need is enter Total
Hours for an employee, either main form or subform, I'm not sure. In
the subform I may have 5 different customers with hours entered for
each. The sum of hours for all customers in the subform must equal
the total hours for the employee. Example: Employee: 8.0 Hours. The
Jones Company 2.3 Hours, The Smith Co. 4.5 Hours, The Johnson
Co., 1.2 hours Total 8.0 Hours. I need something to prevent me from
moving to a new record if the hours for all company's do not agree
with the toal employee hours. The user must go back and correct the
customer hours. This has been driving me crazy...Any help would be
appreciated...Randy

If all the information is on the main form then a Simple If statement can
fix things. I think it's in help under "Displaying a total from a sub form"
Northewind also has samples.
I usually change the color of something until the amounts match and pop a
"Do you really want to do this message". If you are doing this it's not a
problem but users get frustrateds when they can't move on because they
didn't get all of Smiths paperwork.



The total hours should probably be stored in your "date, district" table.

 

[Allen Browne]

Firstly, it is important NOT to store the total number of hours in the main
form's table. Doing so violates basic normalization rules.

If you want to store it anyway, the best you can do is to use the
BeforeInsert event of the subform to insert the remaining number of hours
like a default value in the subform. This really does help the user to over-
or underexpend the hours. You could also use the BeforeUpdate event of the
subform to ensure the hours are not overspent, using a DSum() on the *other*
related records in the subform's table plus the value of the record being
saved, and cancelling the event if necessary (allowing for slight rounding
errors).

However, you cannot block under-expended hours, because you do have to allow
the entry of underexpended hours (one record at a time) and there is no
event that fires before you move record.

If this is really important, you could use temp tables to hold the values
being entered/edited/deleted in the main form and subform, and only permit
these to be written to the real table once they add up. This will take
considerable effort if multiple users could be editing/deleting the same
records at the same time.

Another alternative is to use the Current event of the main form to store
the primary key value in a module-level variable. Then when the Current
event fires again or the form's Unload event fires, you can examine the
values of the mostly recently visited record and then do something if they
don't add up.

 

[Albert D. Kallal]

I have built a number of forms that do this.

The trick, or idea here invices using the sub-form contorls on exit event.
Note that I said the sub-form contorls on-exit event..NOT the sub-form!!

I have

curChequeTotal = Nz(Me.frmBatchLedger!PAmount, 0) ' this is our main
total

curDetailsTotal = Nz(frmMyDetails.Form!txtAmountTotal, 0) ' this is the
distriubed check total

If curChequeTotal > 0 Then
If curChequeTotal <> curDetailsTotal Then
Cancel = True
MsgBox "Total amounts distributed do not match", vbCritical,
AppName
End If
End If

You can seen a screen shot here where I take a cheque amount...and then
"distribute" it to several accounts. (it is the 2nd to last screen shot)
here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

I should add there is/was a few conditions where users could get around the
fact of the distributed amounts not matching the entered check amount.
However, I added a "close daily" posting to the whole system, and thus it
tests the balances..and if they are all ok, then a posting field is set, and
the users can no longer edit the data...

 

[Randy]

Thank you Albert. You solved my problem

 

[Mike Painter]

Firstly, it is important NOT to store the total number of hours in
the main form's table. Doing so violates basic normalization rules.

The individual hours reported from several sources should not be stored but
the total hours that come from another source must be stored if you will
come back to the information.

If you don't store it then you can never compare the individual times with
the (assummed correct) total.
This is closely analogeous to an Amount tendered which is compared to the
total due to see if the amounts are equal or more money is due.

If you want to store it anyway, the best you can do is to use the
BeforeInsert event of the subform to insert the remaining number of
hours like a default value in the subform. This really does help the
user to over- or underexpend the hours. You could also use the
BeforeUpdate event of the subform to ensure the hours are not
overspent, using a DSum() on the *other* related records in the
subform's table plus the value of the record being saved, and
cancelling the event if necessary (allowing for slight rounding
errors).

You are assuming that there is a default number of hours but this may not be
the case. Jones may work a 4 hour day and only turn in three one hour
slips.
Smith may work 8 hours and do the same.