comparing records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a subform that tracks the different times a student is on
our campus. The primary key is an auto number for the attendance since a
student can come on campus more than once per day. the problem is i have no
way to detect duplicate entries or overlap of times. a duplicate date is not
an automatic deal breaker but two activities at one o'clock on the same day
will raise eyebrows and questions about my manliness. Please help
 
I have a form with a subform that tracks the different times a student is on
our campus. The primary key is an auto number for the attendance since a
student can come on campus more than once per day. the problem is i have no
way to detect duplicate entries or overlap of times. a duplicate date is not
an automatic deal breaker but two activities at one o'clock on the same day
will raise eyebrows and questions about my manliness. Please help
Click to expand...

Well, that just depends on how many young ladies you were visiting at
one o'clock wouldn't it...? <bg>

You'll need to use the Form's BeforeUpdate event to check for
duplicates. Do you have a TimeArrived and TimeLeft field? If so you
could use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim vVisitID As Variant
vVisitID = DLookUp("[VisitID]", "[tablename]", _
"[StudentID] = " & Me!txtStudentID _
& " AND (([TimeArrived] >= #" & Me!txtTimeArrived & "#" _
& " AND [TimeArrived] <= #" & Me!txtTimeLeft & "#)" _
& " OR ([TimeLeft] >= #" & Me!txtTimeArrived & "#" _
& " AND [TimeLeft] <= #" & Me!txtTimeLeft & "#))")
If Not IsNull(vVisitID) Then
MsgBox "This student can't be on campus twice at the same time!", _
vbOKOnly
Cancel = True
' you can optionally navigate to the found record if you want
End If
End Sub

John W. Vinson[MVP]
 
Thanks John, I will definitely try this. I'll Let you know how i do.

Dan

John Vinson said:
I have a form with a subform that tracks the different times a student is on
our campus. The primary key is an auto number for the attendance since a
student can come on campus more than once per day. the problem is i have no
way to detect duplicate entries or overlap of times. a duplicate date is not
an automatic deal breaker but two activities at one o'clock on the same day
will raise eyebrows and questions about my manliness. Please help
Click to expand...

Well, that just depends on how many young ladies you were visiting at
one o'clock wouldn't it...? <bg>

You'll need to use the Form's BeforeUpdate event to check for
duplicates. Do you have a TimeArrived and TimeLeft field? If so you
could use code like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim vVisitID As Variant
vVisitID = DLookUp("[VisitID]", "[tablename]", _
"[StudentID] = " & Me!txtStudentID _
& " AND (([TimeArrived] >= #" & Me!txtTimeArrived & "#" _
& " AND [TimeArrived] <= #" & Me!txtTimeLeft & "#)" _
& " OR ([TimeLeft] >= #" & Me!txtTimeArrived & "#" _
& " AND [TimeLeft] <= #" & Me!txtTimeLeft & "#))")
If Not IsNull(vVisitID) Then
MsgBox "This student can't be on campus twice at the same time!", _
vbOKOnly
Cancel = True
' you can optionally navigate to the found record if you want
End If
End Sub

John W. Vinson[MVP]
Click to expand...
 
Back
Top