WHY use bookmarks?

  • Thread starter Thread starter Rob Wills
  • Start date Start date
R

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
=========================
 
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
 
Rob Wills said:
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
=========================
Click to expand...


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.
 
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
 
Back
Top