Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Record has been modified message - which I don't want to see

    Hello everyone:

    I have created a form to record the execution of Preventive Maintenance.

    The logic is this, it will post a service work order into the tblserviceworkorders table with some basic information, but only when the information on the subform has been entered. Because of this, I have the subform inputting information into the parent form when (currently on the technicianuid) field is changed. Then, after all the data is complete, the user clicks on the btnUpdateLastPMandExit button to save the record and exit the form.

    This seems to work, but I get a message saying that the record has been modified, and I have to click on the save record button.

    There may be a better way to do this, or it may just be that I can ignore the message in this particular event.

    I'm fine with either method.

    Code follows: Any suggestions?

    From Main Form:
    Option Compare Database

    Private Sub btnCloseForm_Click()
    On Error GoTo Err_btnCloseForm_Click


    DoCmd.Close

    Exit_btnCloseForm_Click:
    Exit Sub

    Err_btnCloseForm_Click:
    MsgBox Err.Description
    Resume Exit_btnCloseForm_Click

    End Sub

    Private Sub Form_Current()

    End Sub

    Private Sub Form_Deactivate()

    End Sub

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acNewRec
    End Sub
    Private Sub btnAbort_Click()
    On Error GoTo Err_btnAbort_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

    Exit_btnAbort_Click:
    Exit Sub

    Err_btnAbort_Click:
    MsgBox Err.Description
    Resume Exit_btnAbort_Click

    DoCmd.Close
    End Sub

    Private UpdateLastPMDate()
    MsgBox "got to updatePMDate"
    On Error GoTo Err_UpdateLastPMDate

    Dim stDocName As String

    stDocName = "qryUpdateLastPMDate"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_UpdateLastPMDate:
    Exit Sub

    Err_UpdateLastPMDate:
    MsgBox Err.Description
    Resume Exit_UpdateLastPMDate

    End Sub
    Private Sub btnUpdateLastPMandExit_Click()

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'MsgBox "saved record"


    If IsNull(Me.DateCompleted) Then
    MsgBox "Error, could not complete transaction, please review data and try again"
    Else

    On Error GoTo Err_btnUpdateLastPMandExit_Click

    Dim stDocName As String

    stDocName = "qryUpdateLastPMDate"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close
    'If Me.Dirty Then Me.Dirty = False
    DoCmd.SetWarnings True


    Exit_btnUpdateLastPMandExit_Click:
    Exit Sub

    Err_btnUpdateLastPMandExit_Click:
    MsgBox Err.Description
    Resume Exit_btnUpdateLastPMandExit_Click
    End If

    End Sub


    From Sub form
    Option Compare Database

    Private Sub ActivityDate_Change()
    Parent.DateCompleted = Me.ActivityDate
    End Sub

    Private Sub ActivityDate_Click()
    PopCalendar
    End Sub

    Private Sub ActivityDate_LostFocus()
    Parent.DateCompleted = Me.ActivityDate
    End Sub

    Private Sub CallStatus_Change()
    Parent.CallStatus = Me.CallStatus
    End Sub

    Private Sub EndTime_Click()
    PopClock
    End Sub

    Private Sub Form_Deactivate()
    If Me.Dirty = True Then
    MsgBox "subform is dirty"
    DoCmd.RunCommand acCmdSaveRecord
    Me.Dirty = False
    Else
    'do nothing
    End If
    End Sub

    Private Sub Form_Dirty(Cancel As Integer)
    End Sub

    Private Sub Form_LostFocus()
    If Me.Dirty = True Then
    MsgBox "subform is dirty"
    DoCmd.RunCommand acCmdSaveRecord
    Me.Dirty = False
    End If
    End Sub

    Private Sub frmInventoryTransactionSubformforWorkOrderEntry_En ter()

    End Sub

    Private Sub StartTime_Click()
    PopClock
    End Sub

    Private Sub TechnicianContactUID_Change()
    If IsNull(Parent.WorkOrder) Then
    Parent.ProblemDescription = "Perform Preventive Maintenance"
    Me.WorkOrder = Parent.WorkOrder
    Else
    'do nothing
    End If
    Parent.TechnicianUID = Me.TechnicianContactUID
    Me.TechnicianName = DLookup("[Firstname]", "tblContactMaster", "[ContactUID]=TechnicianContactUID") & " " & DLookup("[Lastname]", "tblContactMaster", "[ContactUID]=TechnicianContactUID")
    End Sub

    Private Sub TechnicianContactUID_LostFocus()
    'Me.TechnicianName = DLookup("[Firstname]", "tblContactMaster", "[ContactUID]=TechnicianContactUID") & " " & DLookup("[Lastname]", "tblContactMaster", "[ContactUID]=TechnicianContactUID")
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Please, in the future, only post code that is germaine to your question/problem.

    Code:
    DoCmd.SetWarnings False
    'Your code causing the warning message goes here
    DoCmd.SetWarnings True
    Always remember the last line, resetting the warnings after your procedure is completed, or warnings you may need to see could be suppressed!

    Good Luck!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2003
    Posts
    103

    Sorry about the extra code, I'm not sure what is causing the problem, thus all

    Sorry about extra code. I actually had used the docmdsetwarnings false and reset it later but that didn't help. I'm not quite sure where the error is coming from, that's why I included everything related so that if there was some unknown relationship with the subform inserting the data or some such, that someone might have a clue.

    Oh well, thanks anyway. I imagine that I can keep playing.

    Specifically, I error when running this function.

    Private Sub btnUpdateLastPMandExit_Click()

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    'MsgBox "saved record"


    If IsNull(Me.DateCompleted) Then
    MsgBox "Error, could not complete transaction, please review data and try again"
    Else

    On Error GoTo Err_btnUpdateLastPMandExit_Click

    Dim stDocName As String

    stDocName = "qryUpdateLastPMDate"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.Close
    'If Me.Dirty Then Me.Dirty = False
    DoCmd.SetWarnings True


    Exit_btnUpdateLastPMandExit_Click:
    Exit Sub

    Err_btnUpdateLastPMandExit_Click:
    MsgBox Err.Description
    Resume Exit_btnUpdateLastPMandExit_Click
    End If

    End Sub

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Would changing the
    docmd.openquery stDocName, acNormal, acEdit
    to
    docmd.openquery "qryUpdateLastPMDate"
    help? Otherwise, I'm wondering if you're getting the update msg from the first
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    after the
    Private Sub btnUpdateLastPMandExit_Click()
    Last edited by pkstormy; 05-17-07 at 19:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by pkstormy
    Would changing the
    docmd.openquery stDocName, acNormal, acEdit
    to
    docmd.openquery "qryUpdateLastPMDate"
    help? Otherwise, I'm wondering if you're getting the update msg from the first
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    after the
    Private Sub btnUpdateLastPMandExit_Click()
    No, neither helped. Thanks though.

    I think the problem is that this form opens from another form, which is used for default values on this form. THAT record remains open, and although there are "no locks" on the data from the calling form I think it likely that that is the root of the problem.

    Message is:
    "Write Conflict
    The record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
    Copying the changes to the clipboard will let you examine the values the other user entered, and then paste your changes back in if you decide to make changes."

    I'd prefer to correct the problem, but if not, then just supress this message, always opting to save the record.


    Thanks
    Last edited by jdostie; 05-17-07 at 19:43.

  6. #6
    Join Date
    Oct 2003
    Posts
    103
    I still don't know the cause, but it definately has something to do with filling data in on the subform first and using that data to populate the form.

    New strategy if no-one else has an idea:
    I'll just setup unbound objects that mirror what I'm trying to complete on the subform and use another insert into for that data.

    *** Edit *** actually, after further consideration I'll wait to see if anyone has a suggestion. Looking at the form I realized that I had a subform and that form had a subform - for a reason. To try to replicate all of that with unbound objects is possible, but not where I'd rather go.
    Last edited by jdostie; 05-17-07 at 20:14.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "Write Conflict
    The record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
    Copying the changes to the clipboard will let you examine the values the other user entered, and then paste your changes back in if you decide to make changes."
    Why would you want to supress this message? It's invaluable in a multi user application!

    But ok, if you *really* want it to go then throughout your code stick
    Code:
    MsgBox "1"
    ...
    MsgBox "2"
    ...
    MsgBox "n"
    This will allow you to narrow down where the error is occuring (it'll be between 2 numbers)! Let us know the results
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Have we established that this is, in fact, a multi-user environment?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    "Write Conflict
    The record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.
    Copying the changes to the clipboard will let you examine the values the other user entered, and then paste your changes back in if you decide to make changes."

    I used to see these when 2 different forms would be opened based on the same recordset.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I ask again:
    Have we established that this is, in fact, a multi-user environment?
    I understand that the error message would seem to indicate that it is, but things aren't always as they seem in Access! Could this be some form of corruption? Let us know, Jdostie!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Write Conflict

    The attached example shows a few ways you can re-produce a write conflict on a database which doesn't necessarily need to be used by multiple users. The general concept is that you want to be careful when opening different forms based on the same recordset although you can also get a write conflict by trying to manipulate data when you only have one form open based on a recordset. I hope this helps.
    Attached Files Attached Files
    Last edited by pkstormy; 05-27-07 at 12:59.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    It would help us help the OP if they'd let us know one way or the other.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Oct 2003
    Posts
    103
    Thanks for the suffestions, I've been away for a while.

    Here are a few things.
    1. The database is a multi-user, but the error was occuring when only I was in the database.
    2. I KNOW now that I cause this error by trying to write data to the subform before any data is put into the form. When I do that, I am checking the master/child field on the parent form and setting it to the value of the child form - beause otherwise the child does not properly link to the parent form (I forget the exact details,but it is written to the table, but is missing, or has a different value in the data that forms the parent/child relationship. This means that the data seems to disappear.

    So, my "solution" will be to "mirror" the child fields with unbounded controls on the parent form, and update the child on events.

    Or, I could effectively to an insert query, but since I want a second level of subform, that may not be a good idea.

    I apologise for not responding for a while, I though I had all the responses I was going to get and hadn't logged on for a while.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •