Lost Bookmark

[Len Robichaud]

I wrote the code below to be called by a button on an open form (that has a
subform) when we need to create a new (Ghost) record using some of the
fields from the existing (current) record.

The form is based on a query that filters the form's recordset. The
environment changes rapidly so the number of "valid" records can change from
the time the form is opened to the point the recordset is requeried.

This code allows the user to add a record by pressing btnMakeGhost and if
the environment has not changed, the code returns the user to the calling
record where they can then advance one record to the New "ghost" record and
then continue on through the recordset.
However, if the environment Has changed the user gets returned to the same
ordinal record position that the calling record used to occupy (If the
original query returned 200 records and the user was on record 52 when they
pressed the btnMakeGhost they will be returned to record position 52 in the
new recordset which may now only contain 190 records placing them 9 records
ahead of the newly created record. and lost).
How can I add the record and then restart the user at the new record in the
"requeried recordset" so they finish editing that record and then continue
to go forward from there.
---------------------------------------------------

Private Sub btnMakeGhost_Cl ick()
On Error GoTo Handle_Error

Dim db As DAO.Database, tbl As Recordset
Set db = CurrentDb()
Set tbl = db.OpenRecordse t("CallTrackerM atchmakerTable" )

Dim myBookmark

myBookmark = Me.Bookmark
With tbl

.AddNew
!Call_ID = Forms![frmCallTrackerM atchMakerSelect]![Call_ID] + 0.1
!GhostFlag = "G"
'Continue populating fields......... ..

.Update
Me.Requery
Me.Bookmark = myBookmark

End With

Exit_btnMakeGho st:

tbl.Close
Exit Sub

Handle_Error:
MsgBox Err.Description
Resume Exit_btnMakeGho st

End Sub
------------------------------------------------

Thanks in advance for your help.

Len Robichaud

[Br]

Len Robichaud wrote:

I wrote the code below to be called by a button on an open form (that
has a subform) when we need to create a new (Ghost) record using some
of the fields from the existing (current) record.

The form is based on a query that filters the form's recordset. The
environment changes rapidly so the number of "valid" records can
change from the time the form is opened to the point the recordset is
requeried.
This code allows the user to add a record by pressing btnMakeGhost
and if the environment has not changed, the code returns the user to
the calling record where they can then advance one record to the New
"ghost" record and then continue on through the recordset.
However, if the environment Has changed the user gets returned to the
same ordinal record position that the calling record used to occupy
(If the original query returned 200 records and the user was on
record 52 when they pressed the btnMakeGhost they will be returned to
record position 52 in the new recordset which may now only contain
190 records placing them 9 records ahead of the newly created record.
and lost).

How can I add the record and then restart the user at the new record
in the "requeried recordset" so they finish editing that record and
then continue to go forward from there.
---------------------------------------------------

Private Sub btnMakeGhost_Cl ick()
On Error GoTo Handle_Error

Dim db As DAO.Database, tbl As Recordset
Set db = CurrentDb()
Set tbl = db.OpenRecordse t("CallTrackerM atchmakerTable" )
Is there any reason that you aren't using a recordsetclone ?

eg. set tbl = Me.Recordstclon e
Dim myBookmark

myBookmark = Me.Bookmark
With tbl

.AddNew
!Call_ID = Forms![frmCallTrackerM atchMakerSelect]![Call_ID] + 0.1
!GhostFlag = "G"
'Continue populating fields......... ..

.Update
Me.Requery
As soon as you requery your bookmark is invalid.

What you'd need to do is store the primary key in a variable and then search
through your recordset to find it.
Me.Bookmark = myBookmark

End With

Exit_btnMakeGho st:

tbl.Close
Exit Sub

Handle_Error:
MsgBox Err.Description
Resume Exit_btnMakeGho st

End Sub
------------------------------------------------

Thanks in advance for your help.

Len Robichaud

Try something like this...

dim rs as DAO.Recordset, ID as long
set rs = me.recordsetclo ne
with rs
.addnew
![Field1] = Value1
NewID = ![PrimaryKeyField]
.update
end with
me.requery
set rs = me.recordsetclo ne
rs.findfirst "[ID]=" NewID
if not rs.nomatch then me.bookmark = rs.bookmark
set rs = nothing

--
regards,

Br@dley

[Len Robichaud]

Thank you. I searched and searched and "busted my brain" for several days
before posting. For some reason it never occurred to me that I could use
recordsetclone to add a record.

I bow to your superior experience J

Len

"> Try something like this...

dim rs as DAO.Recordset, ID as long
set rs = me.recordsetclo ne
with rs
.addnew
![Field1] = Value1
NewID = ![PrimaryKeyField]
.update
end with
me.requery
set rs = me.recordsetclo ne
rs.findfirst "[ID]=" NewID
if not rs.nomatch then me.bookmark = rs.bookmark
set rs = nothing

--
regards,

Br@dley