Highlighting a Record in a Subform

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

Re: Highlighting a Record in a Subform

Keith Wilby wrote:
> 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
>
>
Is there a reason to set the recordset to a query instead of the
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

Re: Highlighting a Record in a Subform

Hi Salad ...

"Salad" wrote in message
news:13pejj6rge0bpf2@corp.supernews.com...
> Is there a reason to set the recordset to a query instead of the subform's
> recordsource? IOW,
> set rs = Me.SubformName.Form.Recordsetclone

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



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

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

Regards,
Keith.