Findfirst not working

[gsnidow via AccessMonster.com]

Greetings folks. I have a form and subform linked by the pk of the main form.
The record source of the subform is a view having field ID INT IDENTITY(1,1)
displayed on the form. I need to have the sub form return to a record after
a subform requery. Lets say I have five records in the subform, having
subform.id of 1 through 5, and displayed in txtID on the subform. If I am on
subform record 5 and I requery I am returned to subform record 1. I need to
be able to return to record 5, not 1. I found many posts referencing the
findfirst method and using a bookmark, so I referenced my Access Bible to try
it. I put an unbound text box, txtIDHolder on the subform, and set it to
txtID just before the requery. The below is what I am trying, but it is not
working.

Some code...
Me.txtIDHolder = Me.txtID 'set txtIDHolder = txtID before the
requery
Dim RS As Recordset, Criteria As String
Set RS = Me.RecordsetClone
Criteria = "[id] = '" & Me.txtIDHolder & "' "

DoCmd.RunSQL strSQL 'make updates and requery the source view
Me.Requery

RS.findfirst Criteria 'findfirst is not an option after I
type RS.
If Not RS.nomatch = True Then
Me.Bookmark = RS.Bookmark
End If
RS.Close
Rest of code

Is this option not available with ADP, and if not what should I do. I am not
sure if I should use txtID or txtIDHolder when trying to set the Creteria,
but neither option works. When I type RS., when the box pops up with the
available options, only find is displayed, not findfirst. When I try to run
the code I get an error that says "Compile error: Method or data member not
found", then ".findfirst" is highlighted in the vb window that opens up. Any
help would be much appreciated. Thank you.

Greg

 

[Sylvain Lafontaine]

FindFirst is for DAO only, not ADO and ADP uses ADO recordsets exclusively.
Use Find and test for EOF instead of NoMatch. You should also check for the
possibility of an empty recordset and it's also better to use
Me.Recordset.Clone instead of Me.RecordsetClone because I've seen many
reports in the past of problems with the later when used in an ADP project.
Your code should look like:

Dim rs As ADODB.Recordset
set rs = Me.Recordset.Clone

if (rs.BOF and rs.EOF) then exit sub

rs.MoveFirst
rs.Find .......
if (rs.EOF) then exit sub

Me.Bookmark = rs.Bookmark

Note: even if ADP use ADO exclusively, it's still better to dimension the
recordset explicitely as an ADODB.Recordset object because the VBA can still
use DAO objects and if there is a problem with the order of References in
the VBA window, you can end up with a mix-up between ADO and DAO objects.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Greetings folks. I have a form and subform linked by the pk of the main
form.
The record source of the subform is a view having field ID INT
IDENTITY(1,1)
displayed on the form. I need to have the sub form return to a record
after
a subform requery. Lets say I have five records in the subform, having
subform.id of 1 through 5, and displayed in txtID on the subform. If I am
on
subform record 5 and I requery I am returned to subform record 1. I need
to
be able to return to record 5, not 1. I found many posts referencing the
findfirst method and using a bookmark, so I referenced my Access Bible to
try
it. I put an unbound text box, txtIDHolder on the subform, and set it to
txtID just before the requery. The below is what I am trying, but it is
not
working.

Some code...
Me.txtIDHolder = Me.txtID 'set txtIDHolder = txtID before
the
requery
Dim RS As Recordset, Criteria As String
Set RS = Me.RecordsetClone
Criteria = "[id] = '" & Me.txtIDHolder & "' "

DoCmd.RunSQL strSQL 'make updates and requery the source
view
Me.Requery

RS.findfirst Criteria 'findfirst is not an option after I
type RS.
If Not RS.nomatch = True Then
Me.Bookmark = RS.Bookmark
End If
RS.Close
Rest of code

Is this option not available with ADP, and if not what should I do. I am
not
sure if I should use txtID or txtIDHolder when trying to set the Creteria,
but neither option works. When I type RS., when the box pops up with the
available options, only find is displayed, not findfirst. When I try to
run
the code I get an error that says "Compile error: Method or data member
not
found", then ".findfirst" is highlighted in the vb window that opens up.
Any
help would be much appreciated. Thank you.

Greg

 

[gsnidow via AccessMonster.com]

Thank you so much Sylvain. I have been frustrated with this for a week now.
Your example worked like a charm.

FindFirst is for DAO only, not ADO and ADP uses ADO recordsets exclusively.
Use Find and test for EOF instead of NoMatch. You should also check for the
possibility of an empty recordset and it's also better to use
Me.Recordset.Clone instead of Me.RecordsetClone because I've seen many
reports in the past of problems with the later when used in an ADP project.
Your code should look like:

Dim rs As ADODB.Recordset
set rs = Me.Recordset.Clone

if (rs.BOF and rs.EOF) then exit sub

rs.MoveFirst
rs.Find .......
if (rs.EOF) then exit sub

Me.Bookmark = rs.Bookmark

Note: even if ADP use ADO exclusively, it's still better to dimension the
recordset explicitely as an ADODB.Recordset object because the VBA can still
use DAO objects and if there is a problem with the order of References in
the VBA window, you can end up with a mix-up between ADO and DAO objects.

Greetings folks. I have a form and subform linked by the pk of the main
form.

[quoted text clipped - 48 lines]

Greg