Programmatically execute Find (Ctrl-F) in datasheet with VBA

[JohnM77 via AccessMonster.com]

I'm trying to figure out how to programmatically select a record in a
datasheet using VBA. Does anyone here know how to accomplish this feat?

Thanks,
John

 

[JimS]

I'm gonna take a stab at this...

dim rsClone as recordset
set rsclone=me.recordsetclone
rsclone.findfirst "whatever your criteria is"
me.bookmark=rsclone.bookmark
rsclone.close

Syntax a little shaky, but that's the idea.

 

[JohnM77 via AccessMonster.com]

Thanks for your reply. I see the concept here, but when I try it, the result
is "Error 3159 - Not a valid bookmark". Here is the series of events in my
process:

1) User selects a record in a subform control (datasheet).
2) Subform On_Current event populates unbound controls in main form with data
from selected record.
3) User edits data and clicks "Save" commandbutton on main form.
4) "Save" button code uses ADO to find record, edit data, STORE BOOKMARK (in
variable lBookmark), update record, and requery the subform (original
selection now lost).
5) Original record reselected (still in the commandbutton code of main form)
by:
Subform.Bookmark = lBookmark

Any thoughts on why the bookmark error is raised?

Thanks,
John

 

[JimS]

What's the scope of "lBookmark"? Is it "tied" to the cloned recordset of the
subform or to a cloned recordset of the main form?

 

[JohnM77 via AccessMonster.com]

lBookmark is declared in and used exclusively in the Save_click commandbutton
event of the main form. It stores the bookmark of the record in the ADO
recordset used to make edits to the selected record of the subform. The ADO
recordset in the Save_Click event is independent of the subform recordset.
I'm not making edits to the subform recordset directly, hence the need to
requery the subform and then re-find the user-selected record again, after
editing is completed.

Does that make more sense?

Here's code from another attempt (Where Subform.RecordSource = StoredQuery):

rsEdit.Open "SELECT * FROM StoredQuery WHERE ID = " & Subform.ID
'****[Code for editing record]****
lngID = rsEdit!ID
rsEdit.Update
rsEdit.Close

Subform.Requery
rsFind.Open "SELECT * FROM StoredQuery"
rsFind.Find("ID = " & lngID)
Subform.Bookmark = rsFind.Bookmark
rsFind.Close

This results in the same bookmark error.

What's the scope of "lBookmark"? Is it "tied" to the cloned recordset of the
subform or to a cloned recordset of the main form?

Thanks for your reply. I see the concept here, but when I try it, the result
is "Error 3159 - Not a valid bookmark". Here is the series of events in my

[quoted text clipped - 30 lines]

 

[JohnM77 via AccessMonster.com]

Another thing to note is, when I evaluate the Subform.Bookmark property in
the immediate window during break-mode or when the form is static, the result
is always Subform.Bookmark = ?. This seems strange to me, especially since,
when the form is static, a record (which presumably has an associated
bookmark value) is always selected in the subform.

lBookmark is declared in and used exclusively in the Save_click commandbutton
event of the main form. It stores the bookmark of the record in the ADO
recordset used to make edits to the selected record of the subform. The ADO
recordset in the Save_Click event is independent of the subform recordset.
I'm not making edits to the subform recordset directly, hence the need to
requery the subform and then re-find the user-selected record again, after
editing is completed.

Does that make more sense?

Here's code from another attempt (Where Subform.RecordSource = StoredQuery):

rsEdit.Open "SELECT * FROM StoredQuery WHERE ID = " & Subform.ID
'****[Code for editing record]****
lngID = rsEdit!ID
rsEdit.Update
rsEdit.Close

Subform.Requery
rsFind.Open "SELECT * FROM StoredQuery"
rsFind.Find("ID = " & lngID)
Subform.Bookmark = rsFind.Bookmark
rsFind.Close

This results in the same bookmark error.

What's the scope of "lBookmark"? Is it "tied" to the cloned recordset of the
subform or to a cloned recordset of the main form?

[quoted text clipped - 3 lines]

 

[JohnM77 via AccessMonster.com]

Well, I figured out a way to get this to work, but I'm not sure why. All I
did was change the rsFind from ADO to DAO and then used FindNext instead of
Find method, as shown:

Dim rsEdit As ADODB.Recordset
Dim rsFind As DAO.Recordset
lngID As Long

rsEdit.Open "SELECT * FROM StoredQuery WHERE ID = " & Subform.ID
'****[Code for editing record]****
lngID = rsEdit!ID
rsEdit.Update
rsEdit.Close

Subform.Requery
rsFind.Open "SELECT * FROM StoredQuery"
rsFind.FindNext("ID = " & lngID)
Subform.Bookmark = rsFind.Bookmark
rsFind.Close

I thought the Bookmark property was an Access thing and not specifically an
ADO/DAO thing, but I must be mistaken. I'm far from an expert. :\

lBookmark is declared in and used exclusively in the Save_click commandbutton
event of the main form. It stores the bookmark of the record in the ADO
recordset used to make edits to the selected record of the subform. The ADO
recordset in the Save_Click event is independent of the subform recordset.
I'm not making edits to the subform recordset directly, hence the need to
requery the subform and then re-find the user-selected record again, after
editing is completed.

Does that make more sense?

Here's code from another attempt (Where Subform.RecordSource = StoredQuery):

rsEdit.Open "SELECT * FROM StoredQuery WHERE ID = " & Subform.ID
'****[Code for editing record]****
lngID = rsEdit!ID
rsEdit.Update
rsEdit.Close

Subform.Requery
rsFind.Open "SELECT * FROM StoredQuery"
rsFind.Find("ID = " & lngID)
Subform.Bookmark = rsFind.Bookmark
rsFind.Close

This results in the same bookmark error.

What's the scope of "lBookmark"? Is it "tied" to the cloned recordset of the
subform or to a cloned recordset of the main form?

[quoted text clipped - 3 lines]

 

[JohnM77 via AccessMonster.com]

Correction to the previous post. I used the RecordsetClone method of the
subform, which requires DAO instead of ADO:

Dim rsEdit As New ADODB.Recordset
Dim rsFind As DAO.Recordset
lngID As Long

rsEdit.Open "SELECT * FROM StoredQuery WHERE ID = " & Subform.ID
'****[Code for editing record]****
lngID = rsEdit!ID
rsEdit.Update
rsEdit.Close

Subform.Requery
Set rsFind = Subform.RecordsetClone
rsFind.FindNext("ID = " & lngID)
Subform.Bookmark = rsFind.Bookmark
rsFind.Close

Well, I figured out a way to get this to work, but I'm not sure why. All I
did was change the rsFind from ADO to DAO and then used FindNext instead of
Find method, as shown:

Dim rsEdit As ADODB.Recordset
Dim rsFind As DAO.Recordset
lngID As Long

rsEdit.Open "SELECT * FROM StoredQuery WHERE ID = " & Subform.ID
'****[Code for editing record]****
lngID = rsEdit!ID
rsEdit.Update
rsEdit.Close

Subform.Requery
rsFind.Open "SELECT * FROM StoredQuery"
rsFind.FindNext("ID = " & lngID)
Subform.Bookmark = rsFind.Bookmark
rsFind.Close

I thought the Bookmark property was an Access thing and not specifically an
ADO/DAO thing, but I must be mistaken. I'm far from an expert. :\

lBookmark is declared in and used exclusively in the Save_click commandbutton
event of the main form. It stores the bookmark of the record in the ADO

[quoted text clipped - 27 lines]