Error 7878 "The data has changed" after an afterupdate event

[mafhobb]

All right, I need expert help on this one.

I have an unbound form with several fields and buttons. The form works well except if one particular sequence is followed, which causes error 7878: "The data has changed"

The following is the code for the "accept and close" button for that form:

Private Sub cmdadddetails_Click()
   On Error GoTo cmdadddetails_Click_Error
    Dim ErrorForm As String
    Dim ErrorControl As String
    Dim ErrorCode As String
    Dim ErrorNumber As String
    ErrorForm = "frmCreditredemption"
    ErrorControl = "cmdAddDetails"
    
' Dimensioning Variables
    Dim txtRecipients As String
    Dim txtSender As String
    Dim txtsubject As String
    Dim txtBody As String
    Dim StatusAfterContact As String
    Dim CurrNotes As String
    Dim ResolutionValue As String
    Dim sName As String
    Dim CustName As String
    Dim CustID As String
    Dim RMA As String
    Dim Trans As String
    Dim Email As String

'Check if text was actually entered.
If txtNewDetails.Value & "" = "" Then
    If MsgBox("Vorgang ohne weitere Einträge Bestätigen?", vbYesNo, "Keine zusätzlichen Informationen") = vbNo Then
        Exit Sub
    End If
End If

'Who is the user
    sName = Nz(DLookup("EngineerID", "Tbl-Users", "PKEnginnerID = " & StrLoginName & ""), "")
    
'The following line defines the value of Status after contact and Resolution value depending on the option chosen
   
Select Case optnewstatus
    Case 1
        StatusAfterContact = "Warten auf Sendung"
        ResolutionValue = "Gutschrift/Rücksendung in Bearbeitung"
    Case 2
    
    StatusAfterContact = "Sendung erhalten/and VK übergeben"
    ResolutionValue = "An Verkauf/Erledigt"
    
'Find if the user is a dealer/consumer and if this is a Rev/Hob call
    Dim RevHob As String
        RevHob = Me.[lblRevHob].Caption
        
'Setup Signature and "email from" based on the above.
    Dim Signature As String
    Dim emailfrom As String
        If RevHob = "Hob" Then
            'Single signature for both Hob options here
            Signature = " Ihr Hob Service Team"
            emailfrom = "service@hob.de"
        Else
            'Signature for Rev Dealer
            Signature = " Ihr Rev Service Team" 
            emailfrom = "rcservice@rev.de"
        End If
    
'verify if there is a customer name
    Me.txtCustName.SetFocus
    If txtCustName.Value = "" Or IsNull(txtCustName.Value) Then
        txtCustName.Value = "Kein Kundename vorhanden."
        MsgBox "Bitte beachten Sie, Kundendatei ist nicht vorhanden."
    Else
        CustName = txtCustName.Value
    End If
    
'verify if there is a customerID
    Me.txtCustID.SetFocus
    If txtCustID.Value = "" Or IsNull(txtCustID.Value) Then
        txtCustID.Value = "Keine Kundennummer."
        MsgBox "Bitte beachten Sie, Kundennummer ist nicht vorhanden."
    Else
        CustID = txtCustID.Value
    End If
    
'Verify if there is an e-mail address
    Me.txtemail.SetFocus
    If txtemail.Value = "" Or IsNull(txtemail.Value) Then
        txtemail.Value = "Keine E-Mail Adresse angegeben."
        MsgBox "Bitte beachten Sie, der Kunde hat keine E-Mail Adresse angegeben."
    Else
        Email = txtemail.Value
    End If
    
'Find Transaction number
    Me.txtTrans.SetFocus
    If txtTrans.Value = "" Or IsNull(txtTrans.Value) Then
        MsgBox "Please note that there is no transaction number."
        Trans = "Keine"
    Else
        Trans = txtTrans.Value
    End If
        
 'Open e-mail form
        DoCmd.OpenForm "frmEmailTemplate"
        With Forms("frmEmailTemplate")
            .txtFrom = emailfrom
            .txtTo = Email
            .txtSub = " Sendung zum Vorgang " & Trans & " erhalten."
            .txtBod = " Sehr geehrte Damen und Herren, " & vbCrLf & vbCrLf & "wir haben Ihre Sendung erhalten. Der/Die Artikel werden an unsere zuständige Fachabteilung weitergeleitet." & vbCrLf & "Bei Unklarheiten werden wir uns mit Ihnen in Verbindung setzten." & vbCrLf & vbCrLf & " Mit freundlichen Grüßen" & vbCrLf & vbCrLf & Signature
        End With
    End Select

' Loading the SubCalls form in invisible mode
    DoCmd.OpenForm "SubCalls"
    [Forms]![SubCalls].Visible = False
    [Forms]![SubCalls]![CallID] = Forms![Contacts]![Call Listing Subform].Form![CallID].Value
    [Forms]![SubCalls]![SubCallDate] = Now()
    [Forms]![SubCalls]![WhoPickedUp] = sName
    [Forms]![SubCalls]![WhatWasSaid] = Me.txtNewDetails.Value
    [Forms]![SubCalls]![StatusAfterCall] = StatusAfterContact
    [Forms]![SubCalls]![ResolutionDetails] = ResolutionValue
    [Forms]![SubCalls]![Label] = ""
    DoCmd.Close acForm, "SubCalls"

'This empty string will sort out if the field is Null. Not the control.
    CurrNotes = Forms![Contacts]![Call Listing Subform].Form![Notes] & ""
'set focus on new details form
    Forms![frmCreditRedemption].SetFocus
    
'Assign values to other forms

    Forms![Contacts]![Call Listing Subform].Form![Notes] = " " & sName & " Schrieb am " & Now & " ----- " & Me.txtNewDetails.Value & ". " & vbCrLf & "Status des Vorgangs: " & StatusAfterContact & ". ******* End of Contact. *******" & vbCrLf & vbCrLf & CurrNotes
    'Test to add call Resolution details to this form. This resolution Details will be based on the absolute latest value entered for it.
    Forms![Contacts]![Call Listing Subform].Form![ResolutionDetails] = ResolutionValue
    Forms![Contacts]![Call Listing Subform].Form.Refresh
    DoCmd.Close acForm, "frmCreditRedemption"
    
    Exit Sub
    
cmdadddetails_Click_Error:

    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub

End Sub

As I said, this code works well except for the case when the following afterupdate event is run on a field on this same form

Private Sub txtCustRepID_AfterUpdate()

On Error GoTo ErrorHandler
    Dim ErrorForm As String
    Dim ErrorControl As String
    Dim ErrorCode As String
    Dim ErrorNumber As String
    ErrorForm = "frmCreditredemption"
    ErrorControl = "txtCustRepIDAfterUpdate"
    
'Check if there is data
    If Me.txtCustRepID.Value = "" Or IsNull(txtCustRepID) Then
        MsgBox "Bitte geben Sie eine Händlerbeleg Nr"
        Me.txtCustRepID.Value = "Keine"
        Me.txtCustRepID.SetFocus
        Exit Sub
    End If

'Go to Calls table and find original value for CustRepID
    'Fin the Call ID first
        Dim CallIDVar As Long
        Dim ContactIDVar As Long
        Dim CustRepIDOr As String
        CallIDVar = Forms![Contacts]![Call Listing Subform].Form![CallID]
        
    'find CustRepID Original based on CallID using a recordset on the Calls table, matching it to CallID
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Calls", dbOpenDynaset)
        rs.FindFirst "[CallID]=" & CallIDVar
    'Assign the value to the CustRepIDOr variable
        CustRepIDOr = rs!CustRepID
    'reset recordset
        Set db = Nothing
        Set rs = Nothing

    Dim sName As String
    Dim CustRepIDNew As String
        
    'get the new value
        CustRepIDNew = Me.txtCustRepID.Value
        
    'Are you sure you want to change it?
        If MsgBox("Sind Sie sich sicher die Belegnummer zu ändern?", vbQuestion + vbYesNo, "Händlerbelegnummer Wechseln") = vbNo Then
            'If entering the new value is cancelled
            Me.txtCustRepID.Value = CustRepIDOr
            Exit Sub
        Else
            'Who is making the changes
            sName = Nz(DLookup("EngineerID", "Tbl-Users", "PKEnginnerID = " & StrLoginName & ""), "")
            
            'Accept change and add new value to table
            CurrentDb.Execute _
            "UPDATE Calls " & _
            "SET CustRepID = '" & CustRepIDNew & "' " & _
            "WHERE CallID = " & CallIDVar, dbFailOnError
           
            'Add text to box below
            txtNewDetails = txtNewDetails & " Händlerbelegnummer wurde von " & CustRepIDOr & " zu " & CustRepIDNew & " geändert von " & sName & "."
        End If
        
            Exit Sub
    
ErrorHandler:

    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub

End Sub

If this afterupdate event is run before cmdAddDetails runs, I get error 7878 "The data has changed" on the line

    Forms![Contacts]![Call Listing Subform].Form![Notes] = " " & sName & " Schrieb am " & Now & " ----- " & Me.txtNewDetails.Value & ". " & vbCrLf & "Status des Vorgangs: " & StatusAfterContact & ". ******* End of Contact. *******" & vbCrLf & vbCrLf & CurrNotes

Interestingly enough I found a while ago that if I add a loop right before this line, most computers (eleven user on a split db setup) don't run into this issue, but there are three machines (I do not know if they are particularly slow or what their network setup is) run into this error no matter what I do. This is the loop I add right before the line that causes the error:

Dim Start As Variant
Start = Timer
Do While Timer < Start + 2
DoEvents
Loop

I am pretty sure that this has something to do with the "CurrentDb.Execute....." line in the afterupdate event, but I do not know for sure.

So, anyone has any ideas of what is happening? I've geen going around in circles for a long time on this one and I feel like I am missing something.

Thanks

mafhobb

 

[pr2-eugin]

Why this complication? Could it not use a Bound Form? Why Unbound and so much code?

 

[mafhobb]

Hi pr2-eugin

The form shows data from several tables and also saves data to more than one table. At the time I did this (several years ago...I am self-taught) I though that this was the better way of doing it. It worked fine for all these years until I was asked to add this additional field to update this other variable "CustRepID" a couple month ago.

If I did it again I would likely do it differently, in fact I may be tasked with a major update in the coming months, but for now what I need is to resolve this particular issue. Any ideas?

mafhobb

 

[mafhobb]

Also, after the 7878 error message comes up and it is "OK'd" the form remains open and if "cmdAddDetails" is pressed again, the sub goes through without errors. So the error messagebox showing up actually resolves whatever issue was happening. why?

 

[mafhobb]

In fact, if I change the error sub to this, then everything woks fine to the eyes of the user as the data is saved properly and he sees no errors.

cmdadddetails_Click_Error:

    If Err.Number = 7878 Then
        Forms![Contacts]![Call Listing Subform].Form![Notes] = " " & sName & " Schrieb am " & Now & " ----- " & Me.txtNewDetails.Value & ". " & vbCrLf & "Status des Vorgangs: " & StatusAfterContact & ". ******* End of Contact. *******" & vbCrLf & vbCrLf & CurrNotes
    'Test to add call Resolution details to this form. This resolution Details will be based on the absolute latest value entered for it.
        Forms![Contacts]![Call Listing Subform].Form![ResolutionDetails] = ResolutionValue
    ' end of test
        Forms![Contacts]![Call Listing Subform].Form.Refresh
        DoCmd.Close acForm, "frmCreditRedemption"
        Exit Sub
    End If

    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    ErrorCode = ErrorCode & LineNumber
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub

I just do not think that this is the apropriate way of dealing with this error.

mafhobb

 

[gemma-the-husky]

almost certainly the error will be caused by having the same record being edited in 2 different places, so you are causing your own error.

the way to prevent it is to save any changes before opening the same record a second time, but if you are opening the second form you can save the first (maybe because some required fields are not entered) then you have a problem - so it's a design issue really.

it's just a matter of tracking it down.

 

[mafhobb]

Yeah, that is my feeling too, but I just do not know were to save the record to avoid this. I've tried to do it in the afterupdate event then at the begining of the cmdAddDetails sub, then right before the conflicting line...I am at a loss.

mafhobb