Jeff said: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".
Jeff said: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.
Jeff said: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.
Jeff said: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.
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
Jeff said: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)?
Jeff said: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)?
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.