Bookmark crashes after subform requery

[Guest]

I am having problems with the bookmark failing in a subform due to "No
current record". This only happens when I set a bookmark in the subform and
return to it. Then requery the subform and try to set another bookmark at
the same location.

The only thing that I found that will fix the problem is if I go into the
subform and use the cursor to select several records. I can then go back the
original record and the bookmark will work.

I use the following statements to set and return to the bookmark.

BMK = Recordset.Bookmark ' set book mark
'perform action on records. Note: it doesn't matter what actions I perform
the problem will occur regardless of whether these steps are here.
Recordset.Bookmark = BMK ' return to bookmark

I then use the following statement to requery the form for a different
recordset.

Forms!SCHEDULE.Child26.Requery 'Child 26 is the subform in question

When I try to reset the bookmark in the same location in the subform I get a
message that there is "No current record". This seems odd since I am using
an afterupdate on the form to trigger setting the bookmark. Consquently,
there must be a current record.

Please help!

 

[Allen Browne]

When you requery a form, Access loads all its records again. At this point,
it has a completely different recordset than it did before the requery. The
bookmark you saved related to a recordset that has now been destroyed--it
does not survive the requery. You cannot use a bookmark after a requery,
since the old bookmark is for a recordset that no longer exists.

Instead, save the primary key value, and FindFirst in the new recordset.
Example:
Dim varID as Variant
Dim rs As DAO.Recordset
If Me.Dirty Then 'Save and edits in progress.
Me.Dirty = False
End If
varID = Me.[ID] 'Assuming a primary key named ID.
Me.Requery
If IsNull(varID) Then 'Must have been at the new record.
'Make sure the subform has focus
RunCommand acCmdRecordsGotoNew
Else
With Me.RecordsetClone
.FindFirst "[ID] = " & varID
If .NoMatch Then
MsgBox "Disappeared"
Else
Me.Bookmark = .Bookmark
End If
End With
End If

 

[Guest]

Thanks Allen, but I did not make it clear:

After the requery, I am trying to bookmark a new record, in the requery'ed
form that happens to be in the same position in the form as the one I
bookmarked earlier. If I bookmark any other record in the form I have no
problems, but if I try to bookmark a record that is in the exact position on
the form that the last one was the program stops and an error box pops up
that says "No Current Record".

When you requery a form, Access loads all its records again. At this point,
it has a completely different recordset than it did before the requery. The
bookmark you saved related to a recordset that has now been destroyed--it
does not survive the requery. You cannot use a bookmark after a requery,
since the old bookmark is for a recordset that no longer exists.

Instead, save the primary key value, and FindFirst in the new recordset.
Example:
Dim varID as Variant
Dim rs As DAO.Recordset
If Me.Dirty Then 'Save and edits in progress.
Me.Dirty = False
End If
varID = Me.[ID] 'Assuming a primary key named ID.
Me.Requery
If IsNull(varID) Then 'Must have been at the new record.
'Make sure the subform has focus
RunCommand acCmdRecordsGotoNew
Else
With Me.RecordsetClone
.FindFirst "[ID] = " & varID
If .NoMatch Then
MsgBox "Disappeared"
Else
Me.Bookmark = .Bookmark
End If
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am having problems with the bookmark failing in a subform due to "No
current record". This only happens when I set a bookmark in the subform
and
return to it. Then requery the subform and try to set another bookmark at
the same location.

The only thing that I found that will fix the problem is if I go into the
subform and use the cursor to select several records. I can then go back
the
original record and the bookmark will work.

I use the following statements to set and return to the bookmark.

BMK = Recordset.Bookmark ' set book mark
'perform action on records. Note: it doesn't matter what actions I
perform
the problem will occur regardless of whether these steps are here.
Recordset.Bookmark = BMK ' return to bookmark

I then use the following statement to requery the form for a different
recordset.

Forms!SCHEDULE.Child26.Requery 'Child 26 is the subform in question

When I try to reset the bookmark in the same location in the subform I get
a
message that there is "No current record". This seems odd since I am
using
an afterupdate on the form to trigger setting the bookmark. Consquently,
there must be a current record.

Please help!

 

[Allen Browne]

I guess I don't understand what you are trying to do.

As I understand it, a bookmark is a pointer to a particular record in a
particular recordset. You cannot apply the bookmark from one recordset to
another (unless the recordsets are actually the same object.) A bookmark in
one recordset cannot be used to find the same record in another recordset.
So I don't understand how you expect to use the defunct bookmark on a
completely different recordset.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, but I did not make it clear:

After the requery, I am trying to bookmark a new record, in the requery'ed
form that happens to be in the same position in the form as the one I
bookmarked earlier. If I bookmark any other record in the form I have no
problems, but if I try to bookmark a record that is in the exact position
on
the form that the last one was the program stops and an error box pops up
that says "No Current Record".

When you requery a form, Access loads all its records again. At this
point,
it has a completely different recordset than it did before the requery.
The
bookmark you saved related to a recordset that has now been destroyed--it
does not survive the requery. You cannot use a bookmark after a requery,
since the old bookmark is for a recordset that no longer exists.

Instead, save the primary key value, and FindFirst in the new recordset.
Example:
Dim varID as Variant
Dim rs As DAO.Recordset
If Me.Dirty Then 'Save and edits in progress.
Me.Dirty = False
End If
varID = Me.[ID] 'Assuming a primary key named ID.
Me.Requery
If IsNull(varID) Then 'Must have been at the new record.
'Make sure the subform has focus
RunCommand acCmdRecordsGotoNew
Else
With Me.RecordsetClone
.FindFirst "[ID] = " & varID
If .NoMatch Then
MsgBox "Disappeared"
Else
Me.Bookmark = .Bookmark
End If
End With
End If

I am having problems with the bookmark failing in a subform due to "No
current record". This only happens when I set a bookmark in the
subform
and
return to it. Then requery the subform and try to set another bookmark
at
the same location.

The only thing that I found that will fix the problem is if I go into
the
subform and use the cursor to select several records. I can then go
back
the
original record and the bookmark will work.

I use the following statements to set and return to the bookmark.

BMK = Recordset.Bookmark ' set book mark
'perform action on records. Note: it doesn't matter what actions I
perform
the problem will occur regardless of whether these steps are here.
Recordset.Bookmark = BMK ' return to bookmark

I then use the following statement to requery the form for a different
recordset.

Forms!SCHEDULE.Child26.Requery 'Child 26 is the subform in question

When I try to reset the bookmark in the same location in the subform I
get
a
message that there is "No current record". This seems odd since I am
using
an afterupdate on the form to trigger setting the bookmark.
Consquently,
there must be a current record.

Please help!

 

[Guest]

Aha!

I am trying to set a brand new bookmark, it just so happens that it is in
the same relative position within the requery'ed form as the last bookmark I
set. For example, I bookmark and return to record 16 in the first form. I
then requery to get new data in the form and just by coincidence I am trying
to create a new bookmark at record 16 in the form again. This causes the
crash " No current record". I stop the program, if try to run it again and
set the bookmark, it crashes again with the same error, however, if I
actually go into the form and select any other records in the form by
clicking on them, this somehow resets the ability to bookmark at that
identical location (record 16). I can then go back and set a bookmark at
record 16 and the program runs through to completetion.

I guess I don't understand what you are trying to do.

As I understand it, a bookmark is a pointer to a particular record in a
particular recordset. You cannot apply the bookmark from one recordset to
another (unless the recordsets are actually the same object.) A bookmark in
one recordset cannot be used to find the same record in another recordset.
So I don't understand how you expect to use the defunct bookmark on a
completely different recordset.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, but I did not make it clear:

After the requery, I am trying to bookmark a new record, in the requery'ed
form that happens to be in the same position in the form as the one I
bookmarked earlier. If I bookmark any other record in the form I have no
problems, but if I try to bookmark a record that is in the exact position
on
the form that the last one was the program stops and an error box pops up
that says "No Current Record".

When you requery a form, Access loads all its records again. At this
point,
it has a completely different recordset than it did before the requery.
The
bookmark you saved related to a recordset that has now been destroyed--it
does not survive the requery. You cannot use a bookmark after a requery,
since the old bookmark is for a recordset that no longer exists.

Instead, save the primary key value, and FindFirst in the new recordset.
Example:
Dim varID as Variant
Dim rs As DAO.Recordset
If Me.Dirty Then 'Save and edits in progress.
Me.Dirty = False
End If
varID = Me.[ID] 'Assuming a primary key named ID.
Me.Requery
If IsNull(varID) Then 'Must have been at the new record.
'Make sure the subform has focus
RunCommand acCmdRecordsGotoNew
Else
With Me.RecordsetClone
.FindFirst "[ID] = " & varID
If .NoMatch Then
MsgBox "Disappeared"
Else
Me.Bookmark = .Bookmark
End If
End With
End If

I am having problems with the bookmark failing in a subform due to "No
current record". This only happens when I set a bookmark in the
subform
and
return to it. Then requery the subform and try to set another bookmark
at
the same location.

The only thing that I found that will fix the problem is if I go into
the
subform and use the cursor to select several records. I can then go
back
the
original record and the bookmark will work.

I use the following statements to set and return to the bookmark.

BMK = Recordset.Bookmark ' set book mark
'perform action on records. Note: it doesn't matter what actions I
perform
the problem will occur regardless of whether these steps are here.
Recordset.Bookmark = BMK ' return to bookmark

I then use the following statement to requery the form for a different
recordset.

Forms!SCHEDULE.Child26.Requery 'Child 26 is the subform in question

When I try to reset the bookmark in the same location in the subform I
get
a
message that there is "No current record". This seems odd since I am
using
an afterupdate on the form to trigger setting the bookmark.
Consquently,
there must be a current record.

Please help!