Beforeupdate on textbox not reverting back

[mafhobb]

Hello.

I have an unbound textbox with certain value. I want the user to be able to change the value, but first, I want them to get a question about it. I have added the following code to the before update event for that textbox. If they answer "No" to the question i want the value to go back to what it was, but if they say "yes" then I want the value to stay and a table to be updated.

This is my code:

Private Sub txtCustRepID_BeforeUpdate(Cancel As Integer)

    Dim CallIDVar As Long
    Dim sName As String
    Dim CustRepIDNew As String
    Dim CustRepIDOr As String
        
        CustRepIDOr = Me.txtCustRepID.Value

        If MsgBox("Are you sure you want to change the value?", vbQuestion + vbYesNo, "Update") = vbNo Then
            'If clicking on "No" the new value is cancelled and we go back to the original one
            Cancel = True
            Exit Sub
        Else
            'If clicking on "Yes" then the new value is accepted and the table updated.
            'figure out what record this is to add the new warranty status to it.
            CallIDVar = Forms![Contacts]![Call Listing Subform].Form![CallID]

           
            'Capture the new value
            CustRepIDNew = Me.txtCustRepID.Value
            
            
            'add new value to table
            CurrentDb.Execute _
            "UPDATE Calls " & _
            "SET CustRepID = " & CustRepIDNew & " " & _
            "WHERE CallID = " & CallIDVar, dbFailOnError
            
        End If

End Sub

The code for "Yes" works well, but when the user selects "no", the value in the textbox does not reverse back to the original value.

What do I need to do?

Also, I would like to add a message box that says "The value has been changed from (original value) to (new value)" How do I do that?

Thanks

mafhobb

 

[pr2-eugin]

If the Text Box is Unbound, then I think it does not have an old value to go back to.. Use the After Update Instead, and set the values manually..

 

[David R]

Maybe a stupid question, but why are you using an unbound textbox to update a table if they answer a riddle? You're making your life much harder than it has to be...

 

[mafhobb]

OK. I have updated my code to this based on PR2's comment

Private Sub txtCustRepID_BeforeUpdate(Cancel As Integer)


'Go to Calls table and find original value
    'What is the Current CustRepIDOR from the Calls table
        Dim CallIDVar As Long
        Dim ContactIDvar As Long
        Dim CustRepIDOr As String
        CallIDVar = Forms![Contacts]![Call Listing Subform].Form![CallID]
        
    'find contactID 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 ContactIDvar variable
        CustRepIDOr = rs!CustRepID
    'reset recordset
        Set db = Nothing
        Set rs = Nothing

MsgBox CustRepIDOr

    Dim sName As String
    Dim CustRepIDNew As String
        
        CustRepIDNew = Me.txtCustRepID.Value
    
MsgBox CustRepIDNew

        If MsgBox("Sind Sie sich sicher die Belegnummer zu ändern?", vbQuestion + vbYesNo, "Update xxxx") = vbNo Then
            'If entering the new value is cancelled
            Cancel = True
            Me.txtCustRepID = 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

End Sub

The msgboxes give me the correct values (original and new), but the line "Me.txtCustRepID = CustRepIDOr" (when saying No to the question) gives me the error "RUNTIME ERROR 2147352567(800200009) The macro or function set to beforeupdate or ValidationRule property for this field is preventing the database from saving the data in the field"

What is happening?

mafhobb

 

[mafhobb]

The same code in an afterupdate even works well.

Thanks PR2-eugin!

mafhobb

 

[pr2-eugin]

Glad to help mate.. Good Luck !

 

[SHABZO]

Hello.

I have an unbound textbox with certain value. I want the user to be able to change the value, but first, I want them to get a question about it. I have added the following code to the before update event for that textbox. If they answer "No" to the question i want the value to go back to what it was, but if they say "yes" then I want the value to stay and a table to be updated.

This is my code:

Private Sub txtCustRepID_BeforeUpdate(Cancel As Integer)

    Dim CallIDVar As Long
    Dim sName As String
    Dim CustRepIDNew As String
    Dim CustRepIDOr As String
        
        CustRepIDOr = Me.txtCustRepID.Value

        If MsgBox("Are you sure you want to change the value?", vbQuestion + vbYesNo, "Update") = vbNo Then
            'If clicking on "No" the new value is cancelled and we go back to the original one
            Cancel = True
            Exit Sub
        Else
            'If clicking on "Yes" then the new value is accepted and the table updated.
            'figure out what record this is to add the new warranty status to it.
            CallIDVar = Forms![Contacts]![Call Listing Subform].Form![CallID]

           
            'Capture the new value
            CustRepIDNew = Me.txtCustRepID.Value
            
            
            'add new value to table
            CurrentDb.Execute _
            "UPDATE Calls " & _
            "SET CustRepID = " & CustRepIDNew & " " & _
            "WHERE CallID = " & CallIDVar, dbFailOnError
            
        End If

End Sub

The code for "Yes" works well, but when the user selects "no", the value in the textbox does not reverse back to the original value.

What do I need to do?

Also, I would like to add a message box that says "The value has been changed from (original value) to (new value)" How do I do that?

Thanks

mafhobb

Hallo

This has nothing todo with this topic. I just want to know if you have solved the incremnt issue using dmax.