Bookmark Comparison Fails with Type mismatch

[Jay]

Hi all –

The following code fails to compile when run. A ‘Type mismatch’ error
occurs on the 'Do Until…' statement.

With rs ‘valid recordset object
'load records
.MoveFirst: .MoveLast: .MoveFirst
'move to 10th record and capture its bookmark
For i = 1 To 9
.MoveNext
Next i
varBookmark = .Bookmark '10th record

'Step through records until bookmarked 10th
'record is found, starting at record 1.
.MoveFirst
Do Until .Bookmark = varBookmark
.MoveNext
Loop
MsgBox "Bookmarked record found."
End With

I’ve also tried the following alternative ‘Do Until’ statement, but it also
fails to compile with a ‘Method or data member not found’ error even though I
have set references to the MS ADO 2.5, 2.8, or ADORecordset 2.8 Libraries:

Do Until .CompareBookmarks(.Bookmark, varBookmark) = adCompareEqual

Thanks in Advance,
Jay

 

[Jay]

I've found an alternative that avoids the problem described in my original
post. Instead of using the Bookmark property and a comparison of bookmark
objects, I substituted the AbsolutePosition property and a comparision of
absolute positions:

1. replace 'varBookmark = .Bookmark'
with 'varAbsolutePosition = .AbsolutePosition'

2. replace 'Do Until .Bookmark = varBookmark'
with 'Do Until .AbsolutePosition = varAbsolutePosition'

It appears that operators (such as the = operation) can't be used with
Bookmarks.

Jay

 

[Allen Browne]

AbsolutePosition is inferior to bookmark, so I can't recommend that
approach.

Instead of using a For...Next loop with a counter, loop until EOF. This will
avoid the invalid bookmark message you received when you moved past the end
of the recordset.

Here's an example:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

There are other things that could fail, e.g. the MoveFirst will fail if
there are no records. More info in:
Traps: Working with Recordsets - 10 common mistakes
at:
http://allenbrowne.com/ser-29.html

 

[Jay]

Thanks for the response and the information leads, Allen. Additional
research confirms your opening statement; the bookmark appears to be more
robust and the method of choice in most situations. I'll consider it first
whenever faced with having to drop a "breadcrumb" in a recordset.

Thanks again,
Jay