Bookmark Comparison Fails with Type mismatch

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 27 Mar 2008 19:01 GMT

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

Reply to this Message

Jay - 27 Mar 2008 19:56 GMT

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

> Hi all –
>
[quoted text clipped - 27 lines]
> Thanks in Advance,
> Jay

Reply to this Message

Allen Browne - 28 Mar 2008 03:19 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 43 lines]
>>
>> Do Until .CompareBookmarks(.Bookmark, varBookmark) = adCompareEqual

Reply to this Message

Jay - 28 Mar 2008 08:23 GMT

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

> AbsolutePosition is inferior to bookmark, so I can't recommend that
> approach.
[quoted text clipped - 59 lines]
> >>
> >> Do Until .CompareBookmarks(.Bookmark, varBookmark) = adCompareEqual

Reply to this Message