How do you compare bookmarks in DAO recordsets?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using fairly standard code in Access 2000 to move a form to a specific
record (see code fragment below). It all works fine, but other parts of my
coding depend on the bookmarking process triggering the Form_Current event -
something that does not happen if the form is already at the record being
requested (a situation that may occasionally happen).

I would like to be able to test if the RecordsetClone bookmark is the same
as the form's bookmark but I haven't been able to find a method that works
for DAO recordsets. Can anyone help, please?


Dim rst as Recordset
Set rst = Me.RecordsetClone
rst.Findfirst << logical test here >>

If rst.NoMatch Then
MsgBox "No match was found"
Else
Me.Bookmark = rst.Bookmark
End If
 
I'm using fairly standard code in Access 2000 to move a form to a specific
record (see code fragment below). It all works fine, but other parts of my
coding depend on the bookmarking process triggering the Form_Current event -
something that does not happen if the form is already at the record being
requested (a situation that may occasionally happen).

I would like to be able to test if the RecordsetClone bookmark is the same
as the form's bookmark but I haven't been able to find a method that works
for DAO recordsets. Can anyone help, please?
Click to expand...


A Bookmark is a string, just save it in a string variable
and compare that to the RecordsetClone's Bookmark.
 
Thanks for the help, Marshall. I've used the following code to implement your
suggestion, and it works the first time I find that the form is already where
I want to be. However, subsequent passes through the code, when a move IS
required, still indicate (wrongly) that the form is already at the specified
record. Is there perhaps something about bookmarks that I don't understand?

Dim varBM1 As String
Dim varBM2 As String

varBM1 = Me.Bookmark
varBM2 = rstFrames.Bookmark

If Not varBM1 = varBM2 Then
MsgBox "About to move to selected record"
Me.Bookmark = rstFrames.Bookmark
Else
MsgBox "Subform already at required record"
End If
 
To further clarify that last problem, sometimes the varBM1 = varBM2 test
correctly causes a move to the required record and sometimes it wrongly
decides that the form is already at the record. However, I have been unable
so far to determine any pattern in when it gets it wrong. It just appears to
be (but almost certainly isn't) a random failure.
 
David said:
Thanks for the help, Marshall. I've used the following code to implement your
suggestion, and it works the first time I find that the form is already where
I want to be. However, subsequent passes through the code, when a move IS
required, still indicate (wrongly) that the form is already at the specified
record. Is there perhaps something about bookmarks that I don't understand?

Dim varBM1 As String
Dim varBM2 As String

varBM1 = Me.Bookmark
varBM2 = rstFrames.Bookmark

If Not varBM1 = varBM2 Then
MsgBox "About to move to selected record"
Me.Bookmark = rstFrames.Bookmark
Else
MsgBox "Subform already at required record"
End If
Click to expand...


As I understand what you're doing, the logic I would use is
something like:

With Me.RecordsetClone
.FindFirst . . .
If Not .NoMatch Then
If .Bookmark = Me.Bookmark Then
MsgBox "Already there"
Else
MsgBox "Moving"
Me.Bookmark = .Bookmark
End If
Else
MsgBox "Not found"
End If
End With

The check for NoMatch seems critical to me. If the search
is unsuccessful, the recordset's bookmark is kind of
unknown.

If none of that helps, then I think I need to see the rest
of your code.
 
varBM1 = Me.Bookmark
varBM2 = rstFrames.Bookmark
Click to expand...

Be carefull, if you requery either of the above reocrdsets, then the book
marks are now invalid. In fact, you can't really use bookmarks between two
different recordsets anyway. (unless those two reocrdsets are actually based
on the same recoordset).

set rst1 = rstFrames
set rst2 = rstFrames

in the above case, both reocrdset vars are set to the same recordset.
However, if you got two different record sets, OR YOU requery the ONE
roecrdset, then bookmarks are now invalid.

My guess right now you should make this stuff work on the key id of the
record...and not use bookmarks....


me.RecordSetClone.FindFirst "id = " & lngid
if me.RecordSetClone.nomatch = false then
me.bookmark = me.ReocrdSetClone.BookMark
end if

So, store the reocrd id...and don't use bookmarks....
 
Make sure you include the DAO 3.6 Library in the References and then declare
your rst variable as:

Dim rst As DAO.Recordset
 
Marshall, my real code is almost identical to your suggestion, with the
exception that I save the bookmarks to string variables before comparing
them. Doing the comparison directly, as in "If .Bookmark = Me.Bookmark Then",
gives a Type Mismatch error. Bookmarks appear to be rather strange things
.......
 
Albert, thanks for your attempts to help me but I'm not sure I understand
your primary point. You say "make this stuff work on the key id of the record
and not use bookmarks", but your sample code only uses the Key ID in the
Findfirst conditions - and then goes on to use bookmarks in exactly the way
I'm already doing!

As it happens, my existing Findfirst conditions do indeed check the key
field of the form's recordset.

I understand your other point about not requerying either the form recordset
or its clone. However, this clone recordset is created, searched and closed
all within the same section of event code and no requerying of either
recordset takes place there.

Is there another way to move my form to the record identified by the
Findfirst search on its clone, i.e. a method that doesn't use bookmarks?
 
I was a little hasty writing my last post. The action is not all confined to
just that one section of event coding. During my failing scenario, the code
goes elsewhere and returns to that event several times. I've not yet spotted
any requeries elsewhere in my coding but I'm still looking .....
 
Van, I already have the DAO 3.6 Library in my References. I followed your
suggestion to 'disambiguate' my code by adding the DAO prefix in my Dim
statement, but unfortunately it made no difference.
 
I think the only way you can get that error is if one of the
bookmarks is Null. I'm not sure how that can happen, maybe
an empty reordset??

I suggest that you place breakpoints in the procedure and
step through the code to see where each value is
set/changed/cleared.
 
All valid points Albert, but I believe David is already
doing that. He's not saving the bookmarks, just comparing
them.

The question was how to determine if the form is already
positioned on the record found in the RecordsetClone.

OR are you saying David should, after the FindFirst, check
the RecordsetClone's current record PK to the form's current
record's PK. Seems like a perfectly valid idea, but should
it really make a difference?
 
Marsh,
I posted my problem on another Access forum and got a response providing a
link to this MS Knowledgebase article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;198648

The article acknowledges a Type Mismatch problem when comparing bookmarks.
The resolution is to do a binary comparison using the StrComp function.
Sadly, however, while this is a more elegant solution than my own workaround
it does not make my code any more reliable. It still fails in just the same
way as before.

By the way, my problem is not random. Every time I go through the same set
of actions with my form it's always at the same place that it first tells me
(wrongly) that the form is already at the selected record.


Marshall Barton said:
I think the only way you can get that error is if one of the
bookmarks is Null. I'm not sure how that can happen, maybe
an empty reordset??

I suggest that you place breakpoints in the procedure and
step through the code to see where each value is
set/changed/cleared.
--
Marsh
MVP [MS Access]


David said:
Marshall, my real code is almost identical to your suggestion, with the
exception that I save the bookmarks to string variables before comparing
them. Doing the comparison directly, as in "If .Bookmark = Me.Bookmark Then",
gives a Type Mismatch error. Bookmarks appear to be rather strange things
......
Click to expand...
Click to expand...
 
Sorry, I don't read you original post correctly.

I agree with Albert: you can simply check like the following:

If rst.Fields("RecordID") = Me.RecordID Then
' Already at the correct record
Else
' Not at the correct Record, needs to set Bookmark
Me.BookMark = rst.BookMark
' Current Event will fire
End If
 
Interesting?? I hadn't stumbled over that KB article
before.

Have you been able to identify any unusual circumstances
related to the problem? Did stepping through the code
provide any clues?

Maybe you can avoid the problem using Albert's idea by
comparing the PK fields of the two recordsets.
 
Hey, that works! Doing a StrComp of the bookmarks is flaky but simply
comparing the primary key field seems to work every time. Thanks for that!
 
I didn't quite understand Albert's suggestion of modifying the Findfirst
conditions, but Van Dinh's last post made it clear how I could avoid
comparing bookmarks and compare primary key fields instead. This method works
fine!

I'm still at a loss as to why the bookmark comparison is unreliable, but I'm
a practical guy - I now have a solution and I can stop worrying about
bookmarks.

Thanks to everyone for all the assistance.


Marshall Barton said:
Interesting?? I hadn't stumbled over that KB article
before.

Have you been able to identify any unusual circumstances
related to the problem? Did stepping through the code
provide any clues?

Maybe you can avoid the problem using Albert's idea by
comparing the PK fields of the two recordsets.
--
Marsh
MVP [MS Access]


David said:
I posted my problem on another Access forum and got a response providing a
link to this MS Knowledgebase article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;198648

The article acknowledges a Type Mismatch problem when comparing bookmarks.
The resolution is to do a binary comparison using the StrComp function.
Sadly, however, while this is a more elegant solution than my own workaround
it does not make my code any more reliable. It still fails in just the same
way as before.

By the way, my problem is not random. Every time I go through the same set
of actions with my form it's always at the same place that it first tells me
(wrongly) that the form is already at the selected record.
Click to expand...
Click to expand...
 
David Anderson said:
I understand your other point about not requerying either the form
recordset
or its clone. However, this clone recordset is created, searched and
closed
all within the same section of event code and no requerying of either
recordset takes place there.
Click to expand...

ok, then you got 100% of my advice down pat!. I just wanted to make sure the
above is clear in your mind...and it is obviously by your response you got
this down right. Do note that if you code causes a record to added to the
form
then again, those books marks go bad.
.....
Is there another way to move my form to the record identified by the
Findfirst search on its clone, i.e. a method that doesn't use bookmarks?
Click to expand...

You can use the absoulteposition of the roecrdset. (and, just to be clear, I
was not suggesting a alternative to bookmarks, but you can use).

Dim lngI As Long
Dim str As String
Dim rst As DAO.Recordset
Dim lngrecPtr As Long


Set rst = Me.Recordset

lngI = rst.AbsolutePosition

Me.Requery

rst.AbsolutePosition = lngI

Set rst = Nothing

do note that by defining rst as DAO, then you get a the absolute position,
and if you declare rst as ADO, then you do not get this feature. Remember,
ms-access "casts" the reocrdset type into whichever type you choose! (I
believe by default, that me.reocrdset is ADO, and me.recordsetclone is dao)

Anyway, we could use recordsetclone also here...

lngI = Me!ID

Me.Requery

Me.RecordsetClone.FindFirst "id = " & lngI
lngrecPtr = Me.RecordsetClone.AbsolutePosition

Me.Recordset.AbsolutePosition = lngrecPtr

or in place of the above, you could even use

DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, lngrecPtr + 1

So, there are some approaches here...but I don't see why what you got don't
work...
 
Back
Top