• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Record was already edited error.

I have access frontend and SQL Sever backend
I have a form with tblDaily as part of the record source.
I add data to a field on frmMain, then click a button to go to a 2nd form.
In the button's code, I save frmMain (docmd.Save) before I open frm2nd.
I add data to frm2nd which also has tblDaily as part of the record source.
When I close frm2nd I requery and Save again using DoCmd.Save acForm, "frmMain"
and I'm back at frmMain.
When I type data into the frmMain I get the message...
    The data has been changed.
    Another user edited this record...
    Re-edit the record.
Why?  How many more saves do I need.
Does Docmd.save not work with SQL?

  • 4
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, DoCmd.Save won't work with SQL Server because DoCmd.Save has nothing to do with data. DoCmd.Save doesn't save your DATA, it saves any changes you've made to your FORM, and SQL Server has nothing to do with Form, Reports etc. Use this instead:

If Me.Dirty Then Me.Dirty = False

Note this will only work on the current object. If you need to save data on another form:

If Forms("YourFormName").Dirty Then Forms("YourFormName").Dirty = False

BobRosasAuthor Commented:
Thank you so much for your response.
Would you explain more how this works?  
I think by setting the form to Me.Dirty I'm tricking it into thinking there are no previous changes so that I don't get the message.  If that's true, I'm not sure where to put the code.  I entered the code under 'On Activate" of the Main form so that when I go back to it from the second form I can enter more data but I'm still getting the same "data has been changed etc" message.
I really appreciate your help.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're not setting the Form to Dirty, you're querying the form's Dirty property and, if True, then you're setting that Property to False, which forces Access to save your data.

Where you put this depends on where you need to force a save. Access typically saves when the record is "moved" (i.e you navigate to a different record, add a new record, etc) or when you explicitly call for a Save operation (via Code, menu selection, etc).

I'd do this BEFORE I moved off the first form ... IOW, in the Click event that opens your 2nd form, just before you open that form.
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.

BobRosasAuthor Commented:
Your explanation makes sense but I can't get it to work.
When I click on the button in form 1 to open form 2(frmDFT) I put in your code.  See attached.
I also added the code in the 2nd form on close for both forms.
I'm still getting the message on form one.

Private Sub cmdOpnDFT_Click()
    If IsNull(cboQCReportNo) Then
        MsgBox "You must enter a ReportNo and VersionNo to open this form."
        Exit Sub
    End If
    If Me.Dirty Then Me.Dirty = False
    DoCmd.OpenForm "frmDFT", acNormal
End Sub
Private Sub Form_Close()
    If Me.Dirty Then Me.Dirty = False
    If Forms("frmDaily").Dirty Then Forms("frmDaily").Dirty = False
End Sub

Open in new window

BobRosasAuthor Commented:
There is a subform within the first main form that is auto filled.  Maybe that is the problem.  So I tried adding code to account for that but I've tried all the examples below and none of them will work.
Any thoughts?
Thanks again!

    If Forms("fsubDaily").Dirty Then Forms("fsubDaily").Dirty = False
    If Me.fsubDaily.Dirty Then Me.fsubDaily.Dirty = False
    If Forms!fsubDaily!Dirty Then Forms!fsubDaily.Dirty = False
    If Forms("Me!fsubDaily").Dirty Then Forms("Me!fsubDaily").Dirty = False
BobRosasAuthor Commented:
Thanks for your help.  I'll repost another question.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now