Using bookmarks to select a record after form requery

[tommitchell.nz]

I have a continuous form in an ADP linked to a SQL view. The view
contains both ordering and filtering.

The form is designed to display invoice lines... Some items in the
invoice are grouped under a parent, and the parent line denoted by a
field itemParentID, pointing to the primary key of the parent line.
One field on the form is a price. When changing the price on a part
which is from a group, the overall price gets updated, and vice versa.
This happens in a stored procedure.

What I want to happen, is that in the OnExit event for the price text
box, the stored procedure is called, the form is requeried, and then
using a bookmark I want to have the form jump to the parent line for
the group. I have tried various approaches, including:

Dim bkmark As Variant
Set rs = Me.RecordsetClone

rs.Find "invoiceLineID = " & itemParentID.value
bkmark = rs.Bookmark

Me.Requery

Me.Bookmark = bkmark

And:

Set rs = conn.Execute("SELECT invoiceLineID FROM tblInvoices
WHERE itemParentID = " & itemParentID.value)

If Not rs.EOF Then
Me.Requery
DoCmd.FindRecord rs("invoiceLineID"), acEntire, ,
acSearchAll
End If

None of which I have had much luck with. The form jumps to the top
record when requerying, and nothing seems to happen when using a
bookmark or FindRecord. Any suggestions appreciated.

 

[Allen Browne]

The bookmark is only valid while the recordset exists. The Requery destroys
the recordset and reloads it, so the Bookmark does not survive.

Instead, store the primary key value in a record, and FindFirst *after* the
Requery.

 

[tommitchell.nz]

I have a continuous form in an ADP linked to a SQL view. The view
contains both ordering and filtering.

The form is designed to display invoice lines... Some items in the
invoice are grouped under a parent, and the parent line denoted by a
field itemParentID, pointing to the primary key of the parent line.
One field on the form is a price. When changing the price on a part
which is from a group, the overall price gets updated, and vice versa.
This happens in a stored procedure.

What I want to happen, is that in the OnExit event for the price text
box, the stored procedure is called, the form is requeried, and then
using a bookmark I want to have the form jump to the parent line for
the group. I have tried various approaches, including:

Dim bkmark As Variant
Set rs = Me.RecordsetClone

rs.Find "invoiceLineID = " & itemParentID.value
bkmark = rs.Bookmark

Me.Requery

Me.Bookmark = bkmark

And:

Set rs = conn.Execute("SELECT invoiceLineID FROM tblInvoices
WHERE itemParentID = " & itemParentID.value)

If Not rs.EOF Then
Me.Requery
DoCmd.FindRecord rs("invoiceLineID"), acEntire, ,
acSearchAll
End If

None of which I have had much luck with. The form jumps to the top
record when requerying, and nothing seems to happen when using a
bookmark or FindRecord. Any suggestions appreciated.

Well to answer my own question, was able to get around the problem
with the following code:

Dim curRecord As Integer
curRecord = Me.CurrentRecord

Me.Refresh

DoCmd.GoToRecord , , , curRecord

 

[Allen Browne]

Not ideal, Tom.

You may end up with the wrong record if the form was based on a query that
sorts by something other than an incremental autonumber (e.g. sorting by
company name), and you inserted a record, or if you deleted a record, or if
a filter is applied, or if another user inserted/deleted a record.

 

[i_takeuti]

I have a continuous form in an ADP linked to a SQL view. The view
contains both ordering and filtering.

The form is designed to display invoice lines... Some items in the
invoice are grouped under a parent, and the parent line denoted by a
field itemParentID, pointing to the primary key of the parent line.
One field on the form is a price. When changing the price on a part
which is from a group, the overall price gets updated, and vice versa.
This happens in a stored procedure.

What I want to happen, is that in the OnExit event for the price text
box, the stored procedure is called, the form is requeried, and then
using a bookmark I want to have the form jump to the parent line for
the group. I have tried various approaches, including:

Dim bkmark As Variant
Set rs = Me.RecordsetClone

rs.Find "invoiceLineID = " & itemParentID.value
bkmark = rs.Bookmark

Me.Requery

Me.Bookmark = bkmark

And:

Set rs = conn.Execute("SELECT invoiceLineID FROM tblInvoices
WHERE itemParentID = " & itemParentID.value)

If Not rs.EOF Then
Me.Requery
DoCmd.FindRecord rs("invoiceLineID"), acEntire, ,
acSearchAll
End If

None of which I have had much luck with. The form jumps to the top
record when requerying, and nothing seems to happen when using a
bookmark or FindRecord. Any suggestions appreciated.