Solved

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

Posted on 2013-01-03
12
932 Views
Last Modified: 2013-01-04
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
0
Comment
Question by:reportingdude
  • 5
  • 4
  • 3
12 Comments
 
LVL 1

Expert Comment

by:WaibelRD
Comment Utility
Do the tables in question have a Primary Key field?
0
 

Author Comment

by:reportingdude
Comment Utility
Yes, the tables do have primary keys.
0
 
LVL 1

Accepted Solution

by:
WaibelRD earned 300 total points
Comment Utility
This article may help.  It solved my issue like this a while back.

http://support.microsoft.com/kb/302492
0
 
LVL 13

Expert Comment

by:MrBullwinkle
Comment Utility
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
 

Author Comment

by:reportingdude
Comment Utility
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
 
LVL 13

Assisted Solution

by:MrBullwinkle
MrBullwinkle earned 200 total points
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 1

Expert Comment

by:WaibelRD
Comment Utility
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
 

Author Comment

by:reportingdude
Comment Utility
Waibel, there are 20 subforms.  So would a good idea on the subform be to do Me.Refresh and then Me.Parent.Refresh?
0
 

Author Comment

by:reportingdude
Comment Utility
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
 
LVL 13

Expert Comment

by:MrBullwinkle
Comment Utility
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
 
LVL 1

Expert Comment

by:WaibelRD
Comment Utility
-->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
 

Author Comment

by:reportingdude
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now