Sum Field In A Form

[beebass]

Hi

I am in the middle of creating a database and wondered if I could have your help. One of the forms in the database will be used for staff to input the number of hours spent on particualr tasks in the course of a day. I would like a field at the bottom that sums these up and validates the total against the number of hours that staff are supposed to work. For example if the total hours allowed was 8 and an employee spent their day as in the following:

Task 1 4
Task 2 3
Task 3 2

Total 9

I would like an error message to flag that the total exceeds the limit of 8. The Total field won't be stored in a table, its just their to help validate the data entered by staff.

Any ideas on how I can achieve this would be greatly appreciated.

Thanks

 

[missinglinq]

Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If Nz(Me.Task1) + Nz(Me.Task2) + Nz(Me.Task3) > 9 Then
  MsgBox "Total Hours For a Day Cannot Exceed Eight(8)"
  Cancel = True
 End If
End Sub

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dhookom]

Are there forms and subforms here? Where does the "number of hours that staff are supposed to work" come from?

Provide more information if you need assistance. I assume you don't have an un-normalized table structure that has multiple task fields.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya beebass . . .

[blue]dhookom[/blue] is on target! Some assemblence of your table structure is required!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]