Validation

[David]

I have an Access 2000 database. It stores timesheet
information recording a transaction of employee_ref,
company_ref, time_in and time_out. I would like to
validate this entry to check for duplicates prior to
updating the record. i.e. an employee can't have 2
transactions for the same time slot or overlap. Does
anyone have a procedure that I could implement or some
ideas as to how to solve the problem. Many thanks.

 

[Allen Browne]

Two events overlap if:
A begins before B ends, AND
B begins before A ends.

To that, you need to add that it is the same employee, and also that an
event does not clash with itself.

Assuming your Time fields contain both date and time, and you have a primary
key named "Id", try something like this in the BeforeUpdate event procedure
of the *form*:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String 'Criteria for lookup.
Dim varResult As Variant 'Result of lookup.
Const conJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#" 'JET's expected
date/time format.

If IsNull(Me.Employee_ref) Or IsNull(Me.time_in) Or IsNull(Me.time_out)
Then
Cancel = True
MsgBox "You forgot..."
Else
strWhere = "(Employee_ref = " & Me.Employee_ref & _
") AND (time_in < " & Format(Me.time_out, conJetDateTime) & _
") AND (" & Format(Me.time_in, conJetDateTime) & " < time_out) " & _
"AND (Id <> " & Me.Id & ")"

varResult = DLookup("Id", "YourTableNameHere", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clashes with ID " & varResult
End If
End If
End Sub

 

[David]

-----Original Message-----
Two events overlap if:
A begins before B ends, AND
B begins before A ends.

To that, you need to add that it is the same employee, and also that an
event does not clash with itself.

Assuming your Time fields contain both date and time, and you have a primary
key named "Id", try something like this in the BeforeUpdate event procedure
of the *form*:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String 'Criteria for lookup.
Dim varResult As Variant 'Result of lookup.
Const conJetDateTime = "\#mm\/dd\/yyyy

hh\:nn\:ss\#" 'JET's expected

date/time format.

If IsNull(Me.Employee_ref) Or IsNull(Me.time_in) Or IsNull(Me.time_out)
Then
Cancel = True
MsgBox "You forgot..."
Else
strWhere = "(Employee_ref = " & Me.Employee_ref & _
") AND (time_in < " & Format(Me.time_out, conJetDateTime) & _
") AND (" & Format(Me.time_in, conJetDateTime) & " < time_out) " & _
"AND (Id <> " & Me.Id & ")"

varResult = DLookup("Id", "YourTableNameHere", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clashes with ID " & varResult
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.
Thanks for this Allen as always your help is greatly

appreciated