SQL Backend "The data has been changed..." error

I recently migrated a database backend from Access to SQL Server utilizing the SQL Server Migration Assistant (SSMA).  

The migration did add the SSMA_TimeStamp field to my migrated tables.

I am getting the "The data has been changed...Re-edit the record" message box when switching between subforms and then trying to update a field or press a button linked to a macro.

I have researched this issue a little bit and it was suggested the timestamp field would help alleviate the error, but it doesn't seem to be working in my case.

The front end database does have numerous update queries linked to buttons and record changes, so not sure if that is somehow an issue.

My question is twofold;
1 - is there anything I need to do in terms of query structure to properly utilize the SSMA_Timestamp fields that were created during the migration to SQL?

2 - Is there something else I should try to fix the error?  Users are able to edit after clicking ok on the error message, but this is obviously far less than ideal

Thanks
reportingdudeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
WaibelRDConnect With a Mentor Commented:
This article may help.  It solved my issue like this a while back.

http://support.microsoft.com/kb/302492
0
 
WaibelRDCommented:
Do the tables in question have a Primary Key field?
0
 
reportingdudeAuthor Commented:
Yes, the tables do have primary keys.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
RyanProject Engineer, ElectricalCommented:
The basic problem is you're accessing for editing the same record(s) from multiple locations, and Access by default will lock those records for you, and may also edit them with identical values which really isn't an edit at all, but the db doesn't know that.

Ensure the first forms are readonly so they won't lock the records?  Override any editing events on those forms to write and commit the data so there are no "currently being edited" events hanging.
0
 
reportingdudeAuthor Commented:
Waibel, thanks for the link.  I am looking at the Me.Parent.Refresh command, but I am not familiar with it.  The link suggests adding it to after update.   Would putting it to before update be an issue?  Also, Me.Parent.Refresh is trying to reference a field on  the "MenuForm" which by design closes when a selection is made and launches the "MasterForm".   MasterForm contains all the entry forms and subforms.  I would think "Parent" should only be referncing MasterForm, so I don't think I have a handle on how the command works.

Bullwinkle, yes your observation is spot on, design is an issue for sure.  I inherited this database and there are hundreds of fields, so I am hoping to find another fix before I have to look at a frontend redesign.

Thanks
0
 
RyanConnect With a Mentor Project Engineer, ElectricalCommented:
It sounds like the parent is opening the child, which then opened the record that has been edited by the parent.  Then parent then closes, updating the record.  Now your child is showing "old" data, which you're attempting to edit and update, causing the message.

So if you refresh the child data after closing the parent, but before the user edits it, then your problem should go away.

Also make sure the parent is closing, if its just Visible=False, then as mentioned, call Refresh on the parent to force it to commit any changes it's attempting to make.
0
 
WaibelRDCommented:
Me.Parent.Refresh will refresh the subforms parent only.  You want to run it after the update.  How many subforms are there on the parent?
0
 
reportingdudeAuthor Commented:
Waibel, there are 20 subforms.  So would a good idea on the subform be to do Me.Refresh and then Me.Parent.Refresh?
0
 
reportingdudeAuthor Commented:
I have been re-examining the database and have found that most of the issues are not on the subforms, but are rather tied to commands on the mainform that modify the mainform data (ie applying factors, etc)  
When a field linked directly to the data is modified, the message doesn't come up, but if you try to change data via code, the message appears.  

Below is code for a checkbox on the main form that keeps triggering the message, I have tried adding refresh and requery here, but it is not working:

'------------------------------------------------------------
' ImmatureExp_Click
'
'------------------------------------------------------------
Private Sub ImmatureExp_Click()
On Error GoTo ImmatureExp_Click_Err

    If (Forms![F-MasterForm]!ImmatureExp = -1) Then
        Forms![F-MasterForm]!IncurFactorMed = DLookup(" [T-IncurralFactor]![IncurAdjMed]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegMed, ExpPerEndMed) + 1)
       
    End If
    If (Forms![F-MasterForm]!ImmatureExp = 0) Then
        Forms![F-MasterForm]!IncurFactorMed = 1
       
    End If
    If (Forms![F-MasterForm]!ImmatureExp = -1) Then
        Forms![F-MasterForm]!IncurFactorRx = DLookup(" [T-IncurralFactor]![IncurAdjRx]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegRx, ExpPerEndRx) + 1)
       
    End If
    If (Forms![F-MasterForm]!ImmatureExp = 0) Then
        Forms![F-MasterForm]!IncurFactorRx = 1
       
    End If
    If (Forms![F-MasterForm]!ImmatureExp = -1) Then
        Forms![F-MasterForm]!IncurFactorDent = DLookup(" [T-IncurralFactor]![IncurAdjDent]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegDent, ExpPerEndDent) + 1)
       
    End If
    If (Forms![F-MasterForm]!ImmatureExp = 0) Then
        Forms![F-MasterForm]!IncurFactorDent = 1
       
    End If
    If (Forms![F-MasterForm]!ImmatureExp = -1) Then
        Forms![F-MasterForm]!IncurFactorVis = DLookup(" [T-IncurralFactor]![IncurAdjVis]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegVis, ExpPerEndVis) + 1)
    End If
    If (Forms![F-MasterForm]!ImmatureExp = 0) Then
        Forms![F-MasterForm]!IncurFactorVis = 1
    End If
0
 
RyanProject Engineer, ElectricalCommented:
How about calling Me.Requery, rather than Me.Refresh?

It also looks like this code could be cleaned up immensely, since there are really only 2 tests here, run multiple times.

Also, if this code resides on the F-MasterForm, then "Forms![F-MasterForm]!" isn't needed.

I personally, also prefer to use the Recordset references rather than the control references when applying values to a bound access form, but if the form was created from a wizard, likely the control and linked RS fields are identical names.  It just causes issues because its hard to tell if ImmatureExp is a textbox or a recordsetfield.
0
 
WaibelRDCommented:
-->Waibel, there are 20 subforms.  So would a good idea on the subform be to do Me.Refresh and then Me.Parent.Refresh?
That wouldn't hurt. I also agree with MrBullwinkle, that code block could be cleaned up, something like:
    If (Forms![F-MasterForm]!ImmatureExp = -1) Then
        Forms![F-MasterForm]!IncurFactorMed = DLookup(" [T-IncurralFactor]![IncurAdjMed]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegMed, ExpPerEndMed) + 1)
        Forms![F-MasterForm]!IncurFactorRx = DLookup(" [T-IncurralFactor]![IncurAdjRx]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegRx, ExpPerEndRx) + 1)
        Forms![F-MasterForm]!IncurFactorDent = DLookup(" [T-IncurralFactor]![IncurAdjDent]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegDent, ExpPerEndDent) + 1)
        Forms![F-MasterForm]!IncurFactorVis = DLookup(" [T-IncurralFactor]![IncurAdjVis]", "[T-IncurralFactor]", "Months =" & DateDiff("m", ExpPerBegVis, ExpPerEndVis) + 1)
    Else 'Forms![F-MasterForm]!ImmatureExp = 0
        Forms![F-MasterForm]!IncurFactorMed = 1
        Forms![F-MasterForm]!IncurFactorRx = 1
        Forms![F-MasterForm]!IncurFactorDent = 1
        Forms![F-MasterForm]!IncurFactorVis = 1
    End If

My question is, where is this code running, on the sub form?
0
 
reportingdudeAuthor Commented:
Great comments.  This DB was created by two very bright guys, but they didn't have a lot of coding experience.  I auto converted some embedded macros to VBA, this was one of them.

This is occuring on the main form and not a subform.

Still working through this, but I have determined the issue is ACTUALLY with the switching of the "ImmatureExp" check box.  It is linked to a yes/no field from the mainform's source table.  I got rid of any on-click action, and was still getting the error message.  I also inserted message boxes into the code to verify that the error was happening before any code ran.

The error message is popping up prior to any code being run, it is happening when selecting the checkbox tries to save the underlying value linked to the checkbox from "no" to "yes" or vice versa (it is set up on the table as a yes/no field)

I know it is a refresh issue, hitting F9 after the tab is selected makes the errors go away.  The tool has grown a little convoluted, some tabs are linked to subforms, others to main forms, people go back and forth etc.   Now I know it is just a matter of getting a refresh to run when the tab is selected.

Thank you both for your responses, this is an issue with numerous causes/solutions and you pointed me in the right direction, I think it will help others as well.

*Edit*  I ended up adding a hidden unbound text box on the form in question, then set the text box to be first in the tab order list.  I set a me.refresh on LostFocus.  Now the refresh will trigger if any part of the tab is selected.
0
All Courses

From novice to tech pro — start learning today.