Use the BeforeUpdate event procedure of the form (not control.) Use DSum()
to get the number of hours from *other* records, and add this one. (That
approach will work for new records and edited records.)
The event procedure will be something like the example below. Note that:
a) I've assumed the Employee ID field is named EmpID. Add square brackets
around the name if it contains spaces, e.g. [Emp ID]
b) I've assumed the Date field is named WorkDate. DATE is a reserved name in
Access and can cause you grief.
c) I've assumed the Hours field is a Number type (not Date/Time.)
d) I've assumed the primary key field is named ID. We use that to exclude
the current record from the DSum().
e) Don't change the JET Date format constant (regardless of your regional
settings.)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dblHours as Long
Dim strWhere As String
Dim strMsg As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Const dblcMaxHours As Double = 12
If IsNull(Me.EmpID) Then
Cancel = True
strMsg = strMsg & "Employee required." & vbCrLf
End If
If IsNull(Me.WorkDate) Then
Cancel = True
strMsg = strMsg & "Date required." & vbCrLf
End If
If IsNull(Me.Hours) Then
Cancel = True
strMsg = strMsg & "Hours required." & vbCrLf
End If
If Cancel Then
strMsg = strMsg & vbCrLf & "Complete the data, or press Esc to
undo."
MsgBox strMsg, vbExclamation, "Incomplete"
Else
strWhere = "(EmpID = " & Me.EmpID & ") AND (WorkDate = " & _
Format(Me.WorkDate, strcJetDate) & "(ID <> " & Me.ID & ")"
dblHours = Nz(DSum("Hours", "Table1", strWhere),0) + Me.Hours
If dblHours > dblcMaxHours Then
strMsg = "This employee now has " & dblHours & _
" hour(s) on this date." & vbCrLf & "Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Overload") <> vbYes
Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Jon said:
I want to build a database to track an employee work day hours. I have
a group of employees and each employee should not excess 12 Hours
per day. What I want to do is making a table for entering employee ID,
Date & working Hours . when I want to input employee record, access
should check if this employee is with Hours limit(12 HOURS) if yes,
access precedes with record, if not access show me the capability of
hours still and give me alternative of other employees with the same
group who can work the number of hours I entered. Is that possible in
access. Please help???