I have developed an appointments system database and I use the attached code
in the main form's current event to detect appointment clashes in the
subform. Is it possible to go to the offending record in the subform so as
to highlight it? I did originally have this code in the subform but (of
course) using the Bookmark property in its Current event made it impossible
to move away from the offending record!
Many thanks.
Keith.
Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select * from qsfrmAppointments where ResourceID = " & Me.txtID
Set rs = db.OpenRecordset(strSQL)
Dim varEndTime As Variant, varDate As Variant
With rs
.MoveFirst
Do Until .EOF
varDate = ![ApptDate]
varEndTime = ![StartTime] + ![Duration]
.MoveNext
If .EOF Then GoTo ExitSub
If varEndTime ![StartTime] And varDate = ![ApptDate] Then
Me.lblClash.Visible = True
MsgBox "Clash detected at " & varEndTime & " on " & varDate,
vbCritical, "Appointment clash"
GoTo ExitSub
Else
Me.lblClash.Visible = False
End If
Loop
End With
ExitSub:
rs.Close
Set rs = Nothing
Set db = Nothing