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

Keith Wilby wrote:
subform's recordsource? IOW,
set rs = Me.SubformName.Form.Recordsetclone

When you come to a clash then
Me.SubformName.Form.Bookmark = rs.Bookmark

If you need to use the query instead of recordsource I would think you
would still need to create a recordset of the subform and FindFirst it
and set the bookmark.
set rs1 = Me.SubformName.Form.Recordsetclone
rs1.FindFirst "ID = " & rs!ID
Me.SubformName.Form.Bookmark = rs1.Bookmark

BTW, instead of "Goto Exitsub:, wouldn't "Exit Do" perform the same process?

Bop
http://www.youtube.com/watch?v=vzpxRd44PpE

Hi Salad ...

"Salad" <oil (AT) vinegar (DOT) com> wrote

No reason at all except that I couldn't remember the correct syntax, so
thanks for that.

<snip>

There's always something new to learn in this game and that is a new one on
me! Thanks for that too.

Regards,
Keith.