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
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
