locked
Bookmarks RRS feed

  • Question

  • Hi,

    I've an Access DB with SQL Server back end. In fact I had an issue that every second time when try to I edit the same record on the form i get error as: 'The data has been changed. Another user edited the record and saved the changes before you attempted to save your changes. Re-edit the record."... But, there's no other user except myself. Somewhere I read that it is the way SQL Server works as it handle records differently and suggested to use DoCmd.ReQuery (Me.Refresh doesn't solve the problem) and Me.Bookmark feature in AfterUpdate event. It worked great on few of occasion, but then it started giving me error.

    Bookmarks works fine for updates after Me.ReQuery, but for new record it generates an error 3159 i.e. Not a Valid BookMark. I've handled this by saving unique ID of the record in temporary variable and created an error routine in which RE-opened the same form using .OpenForm with Where clause condition from saved ID.

    This works to an extent, but .OpenForm results are filtered, and is not same like pointing to a record with given ID without filter.

    Problem is, I don't want 'Filtered' results but only to move pointer to desired record. How this situation could be improved please, while staying on the same new/updated record without filtering?
    OR, is there a better way to deal with earlier message as above 'The data has been changed.............'
    Either solution will solve my problem!

    Here's the code I used in Form_AfterUpdate event:

    On Error GoTo exitSub:

        Dim vbookmark As String
        vTempID = Me.ID
        
        vbookmark = Me.Bookmark
        Me.Requery
        Me.Bookmark = vbookmark

    Exit Sub

    exitSub:
        '**Error 3159 appears as in this form it could not find valid Bookmark
        If Err.Number = 3159 Then
            DoCmd.OpenForm "frmEmployee", , , "ID = " & vTempID 'vTempID is Global Variable
           
           'Docmd.GoToRecord ,,acLast
           '**For a new record above line works fine at times, but sometimes show 'Command not available...' message

            Exit Sub
        Else
            MsgBox Err.Number & ", " & Err.Description
        End If

    Thanks in advance,
    K

    Friday, December 23, 2016 8:47 PM

Answers

  • 1. Why are you requerying the form?  It's certainly not necessary in order to save the record.  For that matter, in the form's AfterUpdate event the record has already been saved.

    2. If you do requery the form, you can reposition to the record you were on by using a FindFirst on the primary key field, like this:

        Dim vTempID As Variant

        vTempID = Me.ID  ' assuming Me.ID is bound to primary key field "ID"

        Me.Requery

        Me.Recordset.FindFirst "ID = " & vTempID  ' assuming ID is a numeric field

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KhurramKZ Monday, December 26, 2016 5:53 AM
    Saturday, December 24, 2016 9:22 PM

All replies

  •     vbookmark = Me.Bookmark
        Me.Requery
        Me.Bookmark = vbookmark

    Hi KhurramKZ,

    Instead of a Me.Requery, did you try a Me.Dirty = False. This saves the current record, no need for Bookmarks then.

    Imb.

    Friday, December 23, 2016 8:53 PM
  • I found Me.Dirty = False thing earlier over the internet as well. Just tried, hasn't worked for me. In fact, record doesn't show dirty before this error appears.
    Any other suggestion please??


    • Edited by KhurramKZ Saturday, December 24, 2016 9:52 AM
    Saturday, December 24, 2016 9:49 AM
  • Any other suggestion please??

    Hi KhurramKZ,

    Can you step through your code with the Debugger, to see exactly when and where the error is generated?

    Imb.

    Saturday, December 24, 2016 12:52 PM
  • Does your table have a timestamp field?

    What version of Access?

    Which ODBC driver are you using?

    Saturday, December 24, 2016 1:22 PM
  •  Hi KhurramKZ,

    I had the same problem with SQLServer as BE ,he dosenot like Me.Bookmark  (:

    when access was the BackEnd the Me.Bookmark was working very well.

    So the easiest way to work around it , is to use 

    if not Me.newRecord then

    DoCmd.SearchForRecord , "", acFirst, "[ID] = " & vTempID

    else

    If Me.Dirty Then Me.Dirty = False

    if Me.RecordsetClone.RecordCount > 1 then
    DoCmd.RunCommand acCmdRecordsGoToPrevious
    DoCmd.RunCommand  acCmdRecordsGoToNext

    End if

    End if


    Good luck

    Asaf

    Saturday, December 24, 2016 5:43 PM
  • 1. Why are you requerying the form?  It's certainly not necessary in order to save the record.  For that matter, in the form's AfterUpdate event the record has already been saved.

    2. If you do requery the form, you can reposition to the record you were on by using a FindFirst on the primary key field, like this:

        Dim vTempID As Variant

        vTempID = Me.ID  ' assuming Me.ID is bound to primary key field "ID"

        Me.Requery

        Me.Recordset.FindFirst "ID = " & vTempID  ' assuming ID is a numeric field

     


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by KhurramKZ Monday, December 26, 2016 5:53 AM
    Saturday, December 24, 2016 9:22 PM
  • Thank you, Me.RecordSet.FindFirst solved my problem but i've figure out the reason why i needed to ReQuery after editing each record. That is because UPDATE trigger on SQL Server table was reediting the same record as i was using the SQL Server Trigger to enter TimeStamp on the same row after each edit.

    Thank you so much for help. At least i learned something new ))

    BR,
    K

    • Marked as answer by KhurramKZ Monday, December 26, 2016 5:53 AM
    • Unmarked as answer by KhurramKZ Monday, December 26, 2016 5:53 AM
    Monday, December 26, 2016 5:53 AM
  • Thank you, Me.RecordSet.FindFirst solved my problem but i've figure out the reason why i needed to ReQuery after editing each record. That is because UPDATE trigger on SQL Server table was reediting the same record as i was using the SQL Server Trigger to enter TimeStamp on the same row after each edit.

    Possibly it would be sufficent to refresh the recordset, rather than requery it.  Instead of "Me.Requery", try "Me.Refresh".  That should let you get the updated record without losing your current position in the recordset.  Then you wouldn't need the code to reposition the form.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Monday, December 26, 2016 6:09 PM
  • Thank you Dirk, I'm using Me.Refresh now and it works well. Earlier I had an issue as above, that's why i was using Me.Requery.

    Tuesday, December 27, 2016 6:31 AM