How to highlight a current record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeff - 05 Nov 2005 14:30 GMT

Hi
I have a continuous subform on a parent form. I have a search button that
let me search the records in the subform. When a record in the subform is
found after searching, I need that record to be selected or "highlighted".
Thank you.

Signature

Jeff

Reply to this Message

Duane Hookom - 05 Nov 2005 17:26 GMT

Selected and "highlighted" are two different attributes. You can use
conditional formatting to change properties of the controls in the subform.

Signature

Duane Hookom
MS Access MVP

> Hi
> I have a continuous subform on a parent form. I have a search button that
> let me search the records in the subform. When a record in the subform is
> found after searching, I need that record to be selected or "highlighted".
> Thank you.

Reply to this Message

Marshall Barton - 05 Nov 2005 17:38 GMT

>I have a continuous subform on a parent form. I have a search button that
>let me search the records in the subform. When a record in the subform is
>found after searching, I need that record to be selected or "highlighted".

Add an invisible text box named txtCurKey to the form's
header sectiom. Then add a line of code to the form's
Current event:
Me.txtCurKey = Me.[your primary key field]

Next add a text box to the detail section. Make it as large
as the entire section and set its BackColor to the form's
back color. Now use Conditional Formatting (Format menu) to
set the Expression Is: option to:
[txtCurKey] = [your primary key field]
and select the desired highlight color for the back color
property.

Finally, use Send to Back (Format menu) to put the text box
behind all the other controls. You may also want to make
some of the other control's BaclStyle property to
Transparent.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Jeff - 07 Nov 2005 00:39 GMT

Hi Marshall,

Thank you for your help. I have tested the method you told me and it works.
I can’t get it work in a “data form”, because the background color cannot
cross through the whole section. A data form is like a form when we click and
open an Access table. I am not sure if I tell the right term because my
Access is not an English version.

My first problem is I cannot set focus to the record found after searching.
I know there is something to do with “Bookmark”, but I can't figure it out. I
have a main form displaying clients'ID, name, phone number….., a subform on
the main form is for recording the appointments with any clients, i.e. the
subform is independent and not related to the main form.

In the CmdButton's Click event(the button is on main form):
Set rstObject = dbObject.OpenRecordset(strQuery)
With rstObject
.FindFirst "[MasterID]=" & Me![ID]
If .NoMatch Then
.AddNew
![MasterID] = Me![ID]
!Phone = Me![Phone]
!Date = Me![Date]
.Update
.Close
Else
MsgBox “This client already has an appointment with us”
…….let focus go back to the record found on the subform
End If
End With

Me![subform].Form.Requery
dbObject.Close
Set dbObject = Nothing

When the user finds a client already has an appointment, I'd like that
record be set focus. Any further help is appreciated.

Signature

Jeff

"Marshall Barton" 來函：

> >I have a continuous subform on a parent form. I have a search button that
> >let me search the records in the subform. When a record in the subform is
[quoted text clipped - 17 lines]
> some of the other control's BaclStyle property to
> Transparent.

Reply to this Message

Marshall Barton - 07 Nov 2005 06:07 GMT

>Thank you for your help. I have tested the method you told me and it works.
>I cant get it work in a data form, because the background color cannot
[quoted text clipped - 31 lines]
>When the user finds a client already has an appointment, I'd like that
>record be set focus. Any further help is appreciated.

I gather you're using the form in DataSheet view, where I
was assuming you were in Continuous view. I highly
recommend a continuous form because of its many capabilities
that don't exist in sheet view. However, if you prefer
sheet view you can set up the header text box and the
current event as I described before. Then, instead of the
one big text box, use CF on each or the text boxes in the
detail section.

I think you can add the new record in the subform and
position it using something more like:

With Me.[subformcontrol].Form.RecordsetClone
.FindFirst "[MasterID]=" & Me![ID]
If .NoMatch Then
.AddNew
![MasterID] = Me![ID]
!Phone = Me![Phone]
!Date = Me![Date]
.Update
Me.[subformcontrol].Form.Bookmark = .LastModified
Else
MsgBox This client already has an appointment with
us
Me.[subformcontrol].Form.Bookmark = .Bookmark
End If
End With

Signature

Marsh
MVP [MS Access]

Reply to this Message

Jeff - 10 Nov 2005 10:07 GMT

Hi Mashall,

Thank you for your help, I still can not get my work done and I think I need
more time to try it. I tried what you told me but the cursor always goes to
the first record. On the other hand, what I need is when a record in the
subform is found i.e. an appointment that has already been made by that
client, I’d like that already registered record be “SetFocus”, not the new
record (appointment) that is just added.

Set rstObject = dbObject.OpenRecordset(strQuery)
With rstObject
.FindFirst "[MasterID]=" & Me![ID]
If .NoMatch Then
.AddNew
![MasterID] = Me![ID]
!Phone = Me![Phone]
!Date = Me![Date]
.Update
.Close
Else
……….put something here??????
MsgBox “This client already has an appointment with us”
……let focus go back to the already existed record found on the subform
End If
End With

When I replace With rstObject by With
Me.[subformcontrol].Form.RecordsetClone, how should I re-write Set rstObject
= dbObject.OpenRecordset(strQuery)?

I am sorry I post so many questions. Anyway, thank you very much.

Signature

Jeff

"Marshall Barton" 來函：

> >Thank you for your help. I have tested the method you told me and it works.
> >I can’t get it work in a “data form”, because the background color cannot
[quoted text clipped - 59 lines]
> End If
> End With

Reply to this Message

Marshall Barton - 10 Nov 2005 14:00 GMT

>Thank you for your help, I still can not get my work done and I think I need
>more time to try it. I tried what you told me but the cursor always goes to
[quoted text clipped - 23 lines]
>Me.[subformcontrol].Form.RecordsetClone, how should I re-write Set rstObject
>= dbObject.OpenRecordset(strQuery)?

If the first record becomes the current record, then I
suspect that you have a Requery somewhere. The code I
suggested did not use your rstObject and avoided the need to
requery the form. It also dealt with making either the new
record or the found record current as appropriate. From
what I can see, you can use my code as is, except that you
have to replace subformcontrol with the name of the control
on your main form.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Jeff - 12 Nov 2005 09:40 GMT

Hi Marshall,

Finally I've got my work done. Thank you very much.

Signature

Jeff

"Marshall Barton" 來函：

> >Thank you for your help, I still can not get my work done and I think I need
> >more time to try it. I tried what you told me but the cursor always goes to
[quoted text clipped - 32 lines]
> have to replace subformcontrol with the name of the control
> on your main form.

Reply to this Message