Solved

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

Posted on 2013-01-03
12
935 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
ID: 38741213
Do the tables in question have a Primary Key field?
0
 

Author Comment

by:reportingdude
ID: 38741239
Yes, the tables do have primary keys.
0
 
LVL 1

Accepted Solution

by:
WaibelRD earned 300 total points
ID: 38741521
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
ID: 38741550
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
ID: 38741647
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
ID: 38741681
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 1

Expert Comment

by:WaibelRD
ID: 38741682
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
ID: 38741738
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
ID: 38744496
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
ID: 38744678
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
ID: 38744805
-->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
ID: 38745115
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

920 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

18 Experts available now in Live!

Get 1:1 Help Now