RecordsetClone & Bookmarks

[Bill]

I'm wanting to understand the HELP text
referring to RecordsetClone and the
accompanying use of Bookmark. Obviously,
I haven't yet found that, as the following
code fails with a 7951 reference error.
tbBalance is one of the fields in the forms
RecordSource query.

Private Sub tbTransAmt_DblClick(Cancel As Integer)

With Me.RecordsetClone <<<<<<<error here
.FindLast "FolioID = " & Me.lblTitle3
Me.tbTransAmt = Me.RecordsetClone.tbBalance
End With
Call TestPostExclusion

End Sub

 

[Bill]

I still get a 7951 with this code:

Private Sub tbTransAmt_DblClick(Cancel As Integer)
Dim rst As DAO.Recordset

Set rst = Me.Recordset <<<<<<< error
rst.FindLast "FolioID = " & Me.lblTitle3
Me.Bookmark = rst.Bookmark
Me.tbTransAmt = rst![TransBal]

Call TestPostExclusion

End Sub

 

[Bill]

RATS!!! No wonder I'm not making any progress,
the RecordSource I'm needing to clone is that of a
Sub-form. I.e., I need to retrieve a value from the
field tbBalance from the last record of that Sub-
form's RecordSource.

Am I anywhere close with the code immediately
below?

Bill

I still get a 7951 with this code:

Private Sub tbTransAmt_DblClick(Cancel As Integer)
Dim rst As DAO.Recordset

Set rst = Me.Recordset <<<<<<< error
rst.FindLast "FolioID = " & Me.lblTitle3
Me.Bookmark = rst.Bookmark
Me.tbTransAmt = rst![TransBal]

Call TestPostExclusion

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

I'm wanting to understand the HELP text
referring to RecordsetClone and the
accompanying use of Bookmark. Obviously,
I haven't yet found that, as the following
code fails with a 7951 reference error.
tbBalance is one of the fields in the forms
RecordSource query.

Private Sub tbTransAmt_DblClick(Cancel As Integer)

With Me.RecordsetClone <<<<<<<error here
.FindLast "FolioID = " & Me.lblTitle3
Me.tbTransAmt = Me.RecordsetClone.tbBalance
End With
Call TestPostExclusion

End Sub

 

[David-W-Fenton]

Am I anywhere close with the code immediately
below?

You would use this:

If Me!Subform.Form.Dirty Then
Me!Subform.Form.Dirty = False
End If
Me!Subform.Form.RecordsetClone.MoveLast
[then do with the value from that record whatever you need

Does that work for you?

 

[Bill]

David,
I don't get any errors per se' with "this" code, but
the record accessed, as confirmed by the MsgBox
statement, is the FIRST of those currently shown
in the TransAcctSubform. I.e., it doesn't appear
that the "MoveLast" had any affect.

Private Sub tbTransAmt_DblClick(Cancel As Integer)
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If
Me!TransAcctSubform.Form.RecordsetClone.MoveLast

Me.tbTransAmt = Me!TransAcctSubform.Form.[TransBal]
MsgBox Me!TransAcctSubform.Form.[TransDesc]

Call TestPostExclusion

End Sub

Bill
---------------------------------------------------------------------

Am I anywhere close with the code immediately
below?

You would use this:

If Me!Subform.Form.Dirty Then
Me!Subform.Form.Dirty = False
End If
Me!Subform.Form.RecordsetClone.MoveLast
[then do with the value from that record whatever you need

Does that work for you?

 

[David-W-Fenton]

I don't get any errors per se' with "this" code, but
the record accessed, as confirmed by the MsgBox
statement, is the FIRST of those currently shown
in the TransAcctSubform. I.e., it doesn't appear
that the "MoveLast" had any affect.

Private Sub tbTransAmt_DblClick(Cancel As Integer)
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If
Me!TransAcctSubform.Form.RecordsetClone.MoveLast

Me.tbTransAmt = Me!TransAcctSubform.Form.[TransBal]
MsgBox Me!TransAcctSubform.Form.[TransDesc]

Call TestPostExclusion

End Sub

Duh! YOu're right. You still have to set the bookmark, i.e., after
the MoveLast, add:

Me!TransAcctSubform.Form.Bookmark = _
MeTransAcctSubform.Form.RecordsetClone.Bookmark

My idiotic mistake! The issue is that you have to synchronize the
form's edit buffer with the RecordsetClone. I never use anything
other than FindFirst/FindNext/etc. with the RecordsetClone, and only
use MoveLast with plain old recordsets in code (i.e., not in the
context of a form's edit buffer), so just forgot that you had to
synchronize the bookmarks, just as you do with .FindFirst.

 

[Bill]

Thanks David, that's all that was required.

The bulk of my experience with these kinds of
tasks is with the use of recordsets produced
by SQL queries, which is what I think you're
saying when you refer to the non-use of edit
buffers.

Bill

I don't get any errors per se' with "this" code, but
the record accessed, as confirmed by the MsgBox
statement, is the FIRST of those currently shown
in the TransAcctSubform. I.e., it doesn't appear
that the "MoveLast" had any affect.

Private Sub tbTransAmt_DblClick(Cancel As Integer)
If Me!TransAcctSubform.Form.Dirty Then
Me!TransAcctSubform.Form.Dirty = False
End If
Me!TransAcctSubform.Form.RecordsetClone.MoveLast

Me.tbTransAmt = Me!TransAcctSubform.Form.[TransBal]
MsgBox Me!TransAcctSubform.Form.[TransDesc]

Call TestPostExclusion

End Sub

Duh! YOu're right. You still have to set the bookmark, i.e., after
the MoveLast, add:

Me!TransAcctSubform.Form.Bookmark = _
MeTransAcctSubform.Form.RecordsetClone.Bookmark

My idiotic mistake! The issue is that you have to synchronize the
form's edit buffer with the RecordsetClone. I never use anything
other than FindFirst/FindNext/etc. with the RecordsetClone, and only
use MoveLast with plain old recordsets in code (i.e., not in the
context of a form's edit buffer), so just forgot that you had to
synchronize the bookmarks, just as you do with .FindFirst.

 

[David-W-Fenton]

The bulk of my experience with these kinds of
tasks is with the use of recordsets produced
by SQL queries, which is what I think you're
saying when you refer to the non-use of edit
buffers.

Right -- that's about the only place I ever use .MoveLast, and
that's why I forgot about setting the .Bookmark, which I only ever
do with .RecordsetClone.FindFirst/Next/etc.

In general, though, I do far more navigation by the latter method
than I do within recordsets, where normally I'd just be looping
through all the records returned.

 

[Bill]

Thanks again David.

Right -- that's about the only place I ever use .MoveLast, and
that's why I forgot about setting the .Bookmark, which I only ever
do with .RecordsetClone.FindFirst/Next/etc.

In general, though, I do far more navigation by the latter method
than I do within recordsets, where normally I'd just be looping
through all the records returned.