473,321 Members | 1,877 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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
Jan 23 '08 #1
2 5850
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Mark | last post by:
Hi there, I have a subform, set as a continuous form. When a user selects a particular record in that subform, how can I make that particular record stand out (color or font change, size, etc) from...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
3
by: matthewemiclea | last post by:
So I think this may be a stretch, but I have a subform that is displayed on a main form. The subform is based on a query that brings records from a table(It does not bring all fields, but the main...
12
by: swingingming | last post by:
Hi, in the NorthWind sample database, when clicking on the next navigation button on the new order record with nothing on the subform (order details), we got an order with nothing ordered. How can...
6
by: dk4300 | last post by:
Hi!! I have a subform with a comments (memo) field, linked to a form. On the Memo field: Properties-Other-Enter Key Behavior = New Line in Field On the subform: Properties- Other- Cycle =...
3
by: alex091088180189 | last post by:
Hi all sorry if this has been answered but on a tight schedule so haven't got time to search I'm making a database to record and refer results to several tests in a unit of exams. There are 8...
5
Breezwell
by: Breezwell | last post by:
I have done some searching on this one and I have yet to find any information that sheds light on what I am trying to do. At lease from what I can understand. Basically, I have a main form which...
1
Alireza355
by: Alireza355 | last post by:
Dear all, I have a main form with a subform in it, showing some data. I want to have a little form, called "Quick Search" for example, that has a textbox and 3 buttons: Find, Find Next and...
0
by: Gordon Padwick | last post by:
A form contains controls, one or more of which can be other forms. A form that contains another form is known as a main form. A form contained by a main form is known as a subform. A subform itself...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.