Bookmark Blues

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

Guest

My objective seems simple enough but I am unclear what is happening behind
the scenes with bookmarks and recordset clones. I want to capture the current
record, requery the form, which I know moves me to record one, then move back
to the original record. There is no primary key, so I can't store a variable
and use FindFirst. Below is the code, which executes without an error but
leaves me at record one. Furthermore, the boolean switch on the datasheet
does not reflect the change, despite the fact that I can watch the progress
bar as it calculates. Closing and opening the form properly displays the
changes, but that is not elegant. The code is behind a command button on
another form and I want the requery to occur on the Selector form in
datasheet mode. Any help would be appreciated. Thanks.

Public Location As Variant
TargetForm = "frm Selector" ' Datasheet display
Me.Sample = True ' Flip a boolean switch
Dim rst As Object
Set rst = Forms(TargetForm).Recordset.Clone
Location = rst.Bookmark
Forms(TargetForm).Requery
Forms(TargetForm).SetFocus
Forms(TargetForm).Bookmark = Location
 
Michael, the approach you are taking cannot work.

Think of an bookmark as in in-memory index pointer. It has meaning only for
a particular recordset, and only for the life of that recordset. You cannot
close the form and reopen it and expect to use the same bookmark. Requerying
effectively closes the recordset, and recreates it. The bookmark you saved
previously is now irrelevant: it applies to a recordset that no longer
exists. Therefore you cannot use it to find your record.

It follows that you need a different approach.
Some possible alternatives:

a) Add a primary key to your table, so as to make it easy to FindFirst.

b) Save an entire Criteria string to use with FindFirst, e.g.:
Dim strWhere As String
strWhere = "(City = """ & Me.City & "") AND (Amount = " & Me.Amount & ")
AND (EntryDate = " & Format(Me.EntryDate, "\#mm\/dd\/yyyy\#") & ")"
Me.Requery
With Me.RecordsetClone
.FindFirst strWhere
...

c) Avert the need to requery. For example, if you added a new record to the
table, perhaps you could AddNew to the RecordsetClone instead.
 
Allen:
Sorry for the delayed response. I took your suggestion and ran with it and
forgot to reply, sorry. I re-evaluated the need to requery and realized the
problem. Somehwere in my past I came to the conclusion that all forms should
have a query as the recordsource. I have since changed that philosophy. I
recognized that the table had ID numbers and the form had combos with those
IDs as the bound column, so there was no need to use a query to display the
text, the combo displays the text and changes the number. Once I changed the
recordsource to the table I had to add a few =DLookups, which I did not know
how to use three months ago, and all the info is now properly displayed on
the form. Requerys are not needed as the table displayed changes immediately
and the form opens up much faster because it is not opening a query. As far
as the original database that caused this question is concerned, I realized
the data was not changing, so I made a big query with all the related
information and made a table with it. Now the user can check or uncheck the
boolean box and no requery is needed, the base information is never changing.
Sometimes I get lost down a path and forget to backup and see if there is
another way to solve the problem. Your suggestion about why a requery was
needed backed me up and I found a better way. Thanks.
Regards,
Michael
PS. 75 degrees and windy in NYC, reminds me of Freo.
--
Michael Conroy
Stamford, CT


Allen Browne said:
Michael, the approach you are taking cannot work.

Think of an bookmark as in in-memory index pointer. It has meaning only for
a particular recordset, and only for the life of that recordset. You cannot
close the form and reopen it and expect to use the same bookmark. Requerying
effectively closes the recordset, and recreates it. The bookmark you saved
previously is now irrelevant: it applies to a recordset that no longer
exists. Therefore you cannot use it to find your record.

It follows that you need a different approach.
Some possible alternatives:

a) Add a primary key to your table, so as to make it easy to FindFirst.

b) Save an entire Criteria string to use with FindFirst, e.g.:
Dim strWhere As String
strWhere = "(City = """ & Me.City & "") AND (Amount = " & Me.Amount & ")
AND (EntryDate = " & Format(Me.EntryDate, "\#mm\/dd\/yyyy\#") & ")"
Me.Requery
With Me.RecordsetClone
.FindFirst strWhere
...

c) Avert the need to requery. For example, if you added a new record to the
table, perhaps you could AddNew to the RecordsetClone instead.
Click to expand...
 
Back
Top