T
True.Kilted.Scot
I have a timesheet database, and I need to prevent people entering
overlapping times.
i.e. If an employee enters the following:
13/04/2006 Joe Bloggs Project X 09:00 12:00
they then cannot enter:
13/04/2006 Joe Bloggs Project Y 10:00 11:00
I found a similar question in this group, and used the code provided in
the BeforeUpdate event, but it doesn't seem to work. My BeforeUpdate
event looks like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim strMsg As String
Dim varID As Variant
strWhere = "([fldDateWorked] = " & Format(Me.[txtDateWorked],
"\#dd\/mm\/yyyy\#") & ") AND " & _
"(([fldStartTime] < " & Format(Me.[txtEndTime],
"\#hh:nn\#") & ") AND " & _
"([fldEndTime] > " & Format(Me.[txtStartTime],
"\#hh:nn\#") & ")) AND " & _
"([fldEmployeeID] = " & Me.[txtEmployeeID] & ")"
If Not Me.NewRecord Then
strWhere = strWhere & " AND ([fldWorkID] <> " & Me.[fldWorkID]
& ")"
End If
varID = DLookup("fldWorkID", "tblTimeSheet", strWhere)
If Not IsNull(varID) Then
strMsg = "Warning" & vbCrLf + vbCrLf + _
"The Date / Times entered clash with" + vbCrLf + _
"an already existing record." + vbCrLf + vbCrLf + _
"Please correct your entries."
If MsgBox(strMsg, vbOKOnly + vbCritical, "!! Entry Clash !!")
Then
Cancel = True
End If
End If
End Sub
In the above, I have used the "fld" prefix to denote a field name in a
table, and the "txt" prefix to denote a field name on a form.
Everytime the event fires, "varID" is null, and so the warning message
never appears, even although I may have entered time that are
incorrect.
Can someone please advise on what I have done wrong?
Rgds
Duncs
overlapping times.
i.e. If an employee enters the following:
13/04/2006 Joe Bloggs Project X 09:00 12:00
they then cannot enter:
13/04/2006 Joe Bloggs Project Y 10:00 11:00
I found a similar question in this group, and used the code provided in
the BeforeUpdate event, but it doesn't seem to work. My BeforeUpdate
event looks like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim strMsg As String
Dim varID As Variant
strWhere = "([fldDateWorked] = " & Format(Me.[txtDateWorked],
"\#dd\/mm\/yyyy\#") & ") AND " & _
"(([fldStartTime] < " & Format(Me.[txtEndTime],
"\#hh:nn\#") & ") AND " & _
"([fldEndTime] > " & Format(Me.[txtStartTime],
"\#hh:nn\#") & ")) AND " & _
"([fldEmployeeID] = " & Me.[txtEmployeeID] & ")"
If Not Me.NewRecord Then
strWhere = strWhere & " AND ([fldWorkID] <> " & Me.[fldWorkID]
& ")"
End If
varID = DLookup("fldWorkID", "tblTimeSheet", strWhere)
If Not IsNull(varID) Then
strMsg = "Warning" & vbCrLf + vbCrLf + _
"The Date / Times entered clash with" + vbCrLf + _
"an already existing record." + vbCrLf + vbCrLf + _
"Please correct your entries."
If MsgBox(strMsg, vbOKOnly + vbCritical, "!! Entry Clash !!")
Then
Cancel = True
End If
End If
End Sub
In the above, I have used the "fld" prefix to denote a field name in a
table, and the "txt" prefix to denote a field name on a form.
Everytime the event fires, "varID" is null, and so the warning message
never appears, even although I may have entered time that are
incorrect.
Can someone please advise on what I have done wrong?
Rgds
Duncs