Bookmark issue

[lmcc007]

When I am on the first record on the list and I open the form to delete that record, when closing the form I get Error Number: 3159--not a valid bookmark. Below is the code I am using:

Dim bkMark As String

If CurrentProject.AllForms("frmViewEventHistoryList").IsLoaded Then
bkMark = Forms!frmViewEventHistoryList.Bookmark
Forms!frmViewEventHistoryList.Requery
Forms!frmViewEventHistoryList.Bookmark = bkMark
End If

Is there a way I can tell it to go to next record if I delete the record I selected?

 

[MajP]

Requerying a form invalidates any bookmarks set on records in the form.

Since Microsoft Access creates a unique bookmark for each record in a form's recordset when a form is opened, a form's bookmark will not work on another recordset, even when the two recordsets are based on the same table, query, or SQL statement. For example, suppose you open a form bound to the Customers table. If you then open the Customers table by using Visual Basic and use the ADO Seek or DAO Seek method to locate a specific record in the table, you can't set the form's Bookmark property to the current table record. To perform this kind of operation you can use the ADO Find method or DAO Find methods with the form's RecordsetClone property

I can think of very few instances to use bookmarks. Use find, findfirst, or seek.

Before deleting figure out the next record primary key.
Delete your record
Return to the form and find the first record with that PK

dim rs as dao.recordset
dim nextRecID as long
set rs = me.recordsetclone
if not rs.eof then rs.movenext
nextRecID = rs.someIDField
'code to delete record

Forms!frmViewEventHistoryList.Requery
Forms!frmViewEventHistoryList.recordset.findfirst "someIDField = " & nextRecID

 

[lmcc007]

I changed it to:

Dim rs As Object
Dim lngBookmark As Long

If Me.Recordset.RecordCount = 0 Then
Forms!frmViewEventHistoryList.Requery
Exit Sub
Else
' set a variable to the current record
lngBookmark = Forms!frmViewEventHistoryList!txtEventID
'requery the frmViewEventHistoryList form
Forms!frmViewEventHistoryList.Requery
'bring us back to the original record
Set rs = Forms!frmViewEventHistoryList.RecordsetClone
rs.FindFirst "EventID = " & lngBookmark
Forms!frmViewEventHistoryList.Bookmark = rs.Bookmark
End If

which seems to get rid of Error 3159.

Okay, since the article says "...Requerying a form invalidates any bookmarks set on records in the form..." What would be the proper way of doing this?

 

[MajP]

If you use the findfirst method of the forms recordset (not a clone) it will find and move the form as well. No need to requery, find the book mark in the clone, and then set the bookmark in the form. (That is A97 coding style before the Recordset object of the form).

   'Never dimenion a recordset as anything but a dao.recordset or an aodb.recordset
    Dim rs As recordset
    'Kind of a bad name since it is a primary key value not a bookmark
    'Dim lngBookmark As Long
     If Me.Recordset.RecordCount = 0 Then
        Forms!frmViewEventHistoryList.Requery
        Exit Sub
    Else
        ' set a variable to the current record
        lngBookmark = Forms!frmViewEventHistoryList!txtEventID
        'requery the frmViewEventHistoryList form
        Forms!frmViewEventHistoryList.Requery
        'bring us back to the original record
        Forms!frmViewEventHistoryList.Recordset.FindFirst "EventID = " & lngBookmark
    End If

 

[MajP]

I mistakenly commented out the lngBookmark

 

[lmcc007]

So, are you saying the last code I posted is better one to use?

 

[MajP]

No. The last code I posted is better.

 

[lmcc007]

It looks the same as the last code I posted except for the last line. What difference will this line make or what is it doing different?

 

[MajP]

It is not the same.

You get the ID
You requery
set a recordsetclone object
move the recordset clone to the desired location
get the bookmark
set the forms bookmark to the recordset

To me that is just a bunch of wasted steps. I do not mess with bookmarks, like I said.

I get the ID
I requery
I move the forms recordset to the record with the ID.

 

[lmcc007]

What so wrong with bookmarks?

 

[lmcc007]

What are you suggesting here?

'Kind of a bad name since it is a primary key value not a bookmark
'Dim lngBookmark As Long

 

[MajP]

Look, you are the one having the problem with the code, not me. If you are trying to somehow prove you know more than me than good luck. I showed you how to make it work, and I think I explained clearly why I think it is a good design.