Avatar of reportingdude
reportingdude
 asked on

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
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
reportingdude

8/22/2022 - Mon
WaibelRD

Do the tables in question have a Primary Key field?
reportingdude

ASKER
Yes, the tables do have primary keys.
ASKER CERTIFIED SOLUTION
WaibelRD

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ryan

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
reportingdude

ASKER
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
SOLUTION
Ryan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
WaibelRD

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

ASKER
Waibel, there are 20 subforms.  So would a good idea on the subform be to do Me.Refresh and then Me.Parent.Refresh?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
reportingdude

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

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

-->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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
reportingdude

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