Solved Error 3159 Bookmark not Valid

[BusyBeeBiker]

1. I have a form frmPersonnel which logs persons name address etc.
2. On that form I have a new record button which brings up new record based upon click event below:

DIM strBookMark as variant - Set at top of form module frmPersonnel so available to all sub procedures in module.

Private Sub cmbAddNew_Click()

If Forms!frmLoginScreen!numSecurityLevel >= 8 Then ' Checks Security Clearance
strBookMark = Me.Bookmark 'Set Bookmark for current record
Call addNewRecord("frmPersonnel", "fkTitleID") ' Add New Record Function
Me!frmStatusSubForm.Form.AllowAdditions = True ' Set Allow Additions Default Setting.
' Me.fkTitleID = ""
Me.fkTitleID.SetFocus
Call modTracking(Me.Name, 1, Environ("ComputerName"), Environ("UserName"), "") ' Tracks Addition of Record to WLP System.
End If

ExitHere: ' Any Clean Up Code
Err.Clear
Exit Sub

End Sub

3. What I want to do is when user is asked to whether to save New Record or Abandon it, on abandonment of record, I want it to return to the previous record selected , to which end I have used the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' Dimension Variables
Dim Response, strMessage As String

If Me.Dirty = True Then ' Validates if Record is Dirty.
If Me.NewRecord = True Then ' Validates if New Record

' Validate that Surname has Entered is a NEW record.
If IsNull(txtSurname) Then

' Initialise Message String.
strMessage = "Cannot Save Record Without Surname." & vbCrLf _
& "Select YES to return to Surname Field." & vbCrLf _
& "Select NO to Abandon Change(s). Return to Previous Record."

Response = MsgBox(strMessage, vbYesNo)

If Response = vbNo Then
Cancel = True
Me.Bookmark = strBookMark
Me.Requery
Me.txtSearch.SetFocus
GoTo ExitHere:

Else
Me.txtSurname.SetFocus
GoTo ExitHere:
End If
End If

' Asks User to Save Record or Not!
Response = MsgBox("Save New Record?", vbYesNo)
If Response = vbNo Then
Cancel = True
End If
End If
End If

ExitHere: ' Any Clean Up Code
Err.Clear

End Sub

4. I have used bookmarking to try to elicit this response (In Green) but on third line of this green coding get the error message
"Error 3159 Not a valid bookmark"

When I check strBookMark doesn't have a value as it says Empty. but a populated it first with strBookMark = Me.Bookmark 'Set Bookmark for current record.

Been trying for 2 days to resolve this issue and getting nowhere fast.

 

[arnelgp]

you do have an Identity field (autonumber or PK field)?
you can use that instead of bookmark.

strBookmark = me!ID

to go back to previous "bookmark"

me.recordset.findfirst "ID = " & strBookmark

 

[BusyBeeBiker]

Sorry as they say in the circus good try but no coconut!

I have amended the code as you suggested now getting the message "error 3426 - this action is cancelled by associated object"

 

[moke123]

strBookMark = Me.Bookmark 'Set Bookmark for current record.

Bookmark is a property of the recordset not of the form. You could try Me.Recordset.BookMark.

You can check if a recordset is bookmarkable with Me.Recordset.BookMarkable which returns true or false.

 

[Gasman]

Me.BookMark comes up in intellisense though @moke123 ?

 

[moke123]

Me.BookMark comes up in intellisense though @moke123 ?

https://documentation.help/DAO360/daprobookmark.htm

could be because you can set the form to the recordset bookmark

Me.Bookmark = rst.Bookmark

 

[Gasman]

Now see, that would trip me up as well

 

[jdraw]

@BusyBeeBiker

Unrelated to the issue per se, but for consideration.

This does not do what you expect:

Dim Response, strMessage As String

This will Dim as follows:
Response as Variant
strMessage As String

Access requires explicit Dim:
Dim Response as String, strMessage As String OR
Dim Response as String
Dim strMessage As String


More info on Bookmark/Form.Bookmark

 

[bastanu]

I think moke123 gave you the answer, but here is it from MS:

Form.Bookmark property (Access)

Office VBA reference topic

docs.microsoft.com

In the first sub you need to set it to the form's recordset:

If Forms!frmLoginScreen!numSecurityLevel >= 8 Then ' Checks Security Clearance
strBookMark = Me.Recordset.Bookmark 'Set Bookmark for current record
Call addNewRecord("frmPersonnel", "fkTitleID") ' Add New Record Function

Cheers,

 

[BusyBeeBiker]

Me.BookMark comes up in intellisense though @moke123 ?

Gasman
New to posting on forums. What the chuffing hell are code tags?

 

[BusyBeeBiker]

Many thanks for all your comments.

In the end went down a slightly different pathway instead of using BookMarking I used Docmd.applyfilter instead to return to the previous record.

However I still kept on getting error 2501 on the DoCmd.ApplyFilter, that is till I restructured the code as follows (In Green):

It needed the inclusion of Me.Undo, without it DoCmd.ApplyFilter kicked out Error 2501.

Does anyone know why, I know it works but why?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If glbHandleErrors Then On Error GoTo ErrHandler ' Set Error Handling

Dim strMessage As String, Response
    If Me.NewRecord = True Then ' Validates if New Record
    ' Validate that Surname has Entered is a NEW record.
        If IsNull(txtSurname) Then
        
        ' Initialise Message String.
            strMessage = "Cannot Save Record Without Surname." & vbCrLf _
                       & "Select YES to return to Surname Field." & vbCrLf _
                       & "Select NO to Abandon Change(s). Return to Previous Record."
        
           Response = MsgBox(strMessage, vbYesNo)
            If Response = vbNo Then
               Cancel = True
               Me.Undo
               DoCmd.ApplyFilter , "[pkPersonID]=" & IIf(IsEmpty(strBookMark), Forms!frmLoginscreen!fkID, strBookMark)
               Me.txtSearch.SetFocus
               GoTo ExitHere:

            Else
                Me.txtSurname.SetFocus
                GoTo ExitHere:
            End If
        End If
    
    ' Asks User to Save Record or Not!
            Response = MsgBox("Save New Record?", vbYesNo)
            If Response = vbNo Then
               Cancel = True
               Me.Undo
            End If
    End If

ExitHere:   ' Any Clean Up Code
   Err.Clear
   Exit Sub
 
ErrHandler: ' ERROR HANDLING ROUTINE.
   If Err.Number <> 0 Then
       Call LogError(Err.Number, Err.Description, Forms!frmLoginscreen!fkID, Environ("UserName"), Environ("ComputerName"), "", glbHandleErrors)
      Resume ExitHere
   End If
End Sub

 

[Gasman]

Gasman
New to posting on forums. What the chuffing hell are code tags?

Well you appear to have found the code tag icon?

 

[moke123]

You can still use bookmark, just a little differently.

Something like:

    Dim rs As DAO.Recordset
    
    Set rs = Me.RecordsetClone

    rs.FindFirst "pkPersonID = " &  strBookMark ' The pkPersonID you saved

    If Not rs.NoMatch Then
        Me.Bookmark = rs.Bookmark
    End If

 

[NauticalGent]

Gasman
New to posting on forums. What the chuffing hell are code tags?

With Code Tags:

If Forms!frmLoginScreen!numSecurityLevel >= 8 Then ' Checks Security Clearance
strBookMark = Me.Recordset.Bookmark 'Set Bookmark for current record
Call addNewRecord("frmPersonnel", "fkTitleID") ' Add New Record Function

Without:
If Forms!frmLoginScreen!numSecurityLevel >= 8 Then ' Checks Security Clearance
strBookMark = Me.Recordset.Bookmark 'Set Bookmark for current record
Call addNewRecord("frmPersonnel", "fkTitleID") ' Add New Record Function

 

[NauticalGent]

chuffing hell

And now I have a new substitute for the f-word that rhymes with "truck"...

 

[Gasman]

Crossposted 03/07/21? http://www.vbaexpress.com/forum/showthread.php?68966-BookMark-Problem

 

[BusyBeeBiker]

Picking up on this post, I have sorted on the Bookmarking problem, many thanks for helping me understand this subject area and have marked as solved.

 

[Gasman]

So please share how, so as to help others?

 

[BusyBeeBiker]

Broadly speaking, I was using DoCmd.ApplyFilter as a way of selecting the record I wanted in the underlying table for the main form, not realising by doing so I was restricting the recordset to 1 record.

The results of which were when I tried to use Bookmarking there was only one record to bookmark.

Once that point had been forced into my grey matter the rest was relatively straight forward based upon peoples suggestions in the thread.

Again many thanks for peoples input.