By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,220 Members | 1,512 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,220 IT Pros & Developers. It's quick & easy.

Highlighting a Record in a Subform

P: n/a
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
Jan 23 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Jan 23 '08 #2

P: n/a
Hi Salad ...

"Salad" <oi*@vinegar.comwrote in message
news:13*************@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.

<snip>
>
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.

Jan 23 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.