WHY use bookmarks?

[Rob Wills]

Hi,

I've been coding Access and ADO/DAO for a few years now, and I'm just trying
to understand what benefits are gained from using bookmarks on a recordset

I've never used them before - but I'm looking into someone's code that is
using them - But I'm pretty sure they're irrelevent in this instance....

But this leaves me with the question of WHY????

Cheers
Rob

p.s. Instance example below

=========================
For each Item in CollABC
rs.AddNew
rs!Field1.Value = Item.ABC
rs!Field2.Value = Item.XYZ
rs.Update
rs.Bookmark = rs.LastModified
next Item
=========================

 

[Allen Browne]

The code adds a new record. There is no guarantee that this new record
becomes the current record of the recordset. Setting the bookmark makes it
the current record.

In the example you gave, the code does nothing with the current record, so
establishing the current record seems pointless.

Here's more info:
VBA Traps: Working with Recordsets
at:
http://allenbrowne.com/ser-29.html

 

[Dirk Goldgar]

Hi,

I've been coding Access and ADO/DAO for a few years now, and I'm just
trying
to understand what benefits are gained from using bookmarks on a recordset

I've never used them before - but I'm looking into someone's code that is
using them - But I'm pretty sure they're irrelevent in this instance....

But this leaves me with the question of WHY????

Cheers
Rob

p.s. Instance example below

=========================
For each Item in CollABC
rs.AddNew
rs!Field1.Value = Item.ABC
rs!Field2.Value = Item.XYZ
rs.Update
rs.Bookmark = rs.LastModified
next Item
=========================

If there's really no other code inside the For/Next loop, then yes, in that
case there's no point in setting rs.Bookmark. All that does is position the
recordset to the record that was just added, and there's no reason -- in
this case -- to do that.

If, on the other hand, there was code that wanted to capture the autonumber
ID of the record that was just added, or otherwise wanted to act on that
record after adding it, then setting the bookmark would position the
recordset to that record. IIRC, DAO and ADO behave differently with respect
to the recordset position after adding a record; I *think* DAO leaves the
recordset positioned at the record that was current before the call to
..AddNew, while ADO (I think) leaves the recordset positioned to the new
record. I could be wrong about that, though, and you should check the
online help if it matters.

 

[Linq Adams via AccessMonster.com]

Bookmarks are generally used when you want to

1) Leave a record

2) Do something else (such as requerying a form, or open a second form, close
it, and return to the first form)

3) Return to the record

To be honest, I have no idea what the code you posted is supposed to be doing!
Perhaps one our MVPs wil be able to explain it.

 

[Linq Adams via AccessMonster.com]

See, I told you someone would be along!

How's the university gig going, Professor Browne?

 

[dch3]

And for a practical application...

I have a DB in which user's can leave comments pertaining to the records
that they're working with. One two particular forms, I have code which
automatically captures certain changes to the records and logs the change as
a comment in the database. Since I'm very much a slick and polish kind of
guy, I wanted the newly added comment to be displayed. The comment subform is
always visible on the form. So I created this sub which automatically
requeries the listBox showing the comment headers (date/time, user) and then
displays the new comment.

Private Sub showNewComment()

On Error GoTo Err_showNewComment

'Move the comment subfrm to the newly inserted comment - cosmetic

Dim lngNewCommentId As Long
Dim frm As Form
Dim rsClone As Recordset

If
[Forms]![frmTrailerActivity]![subfrmTrailerActivity].Form.Section("FormFooter").Visible = True Then
lngNewCommentId = DLast("lngCommentId",
"tblTrailerActivityComments", "lngTrailerActivityHeaderId = " &
Me.lngTrailerActivityHeaderId)
'But only if we can locate the commentID in the table, it should be
there unless generateSystemComment fails
If IsNull(lngNewCommentId) = False Then
Set frm =
[Forms]![frmTrailerActivity]![subfrmTrailerActivity].Form![subfrmTrailerActivityComments].Form
Set rsClone = frm.RecordsetClone
rsClone.FindFirst "lngCommentId = " & lngNewCommentId
frm.lstComments = lngNewCommentId
frm.Bookmark = rsClone.Bookmark
Set rsClone = Nothing
Set frm = Nothing
End If
End If

Exit_showNewComment:
Exit Sub

Err_showNewComment:
MsgBox getDefaultErrorMessage(Me.Name, "showNewComment", Err.Number),
vbCritical
Resume Exit_showNewComment

End Sub

Access and ADO/DAO for a few years now, and I'm just trying