I have an Access front end with linked tables to a SQL Server database. One table has a trigger that fires whenever a particular column in the table is updated.

My Access form that updates this table has a bound control on this field. When data in the control is changed, a VBA BeforeUpdate event on the control checks the change is valid and cancels the Update event if not.

However, even though the Update event is cancelled correctly, the trigger on the table still fires.

VBA code and trigger below. Thanks for any help you can provide,

Private Sub AllocatedUser_BeforeUpdate(Cancel As Integer)
    If gErrorHandling Then On Error GoTo My_Error
    
        Dim lngCheck As Long
        
        'Get highest status ID for any review in Master Customer Group
        lngCheck = RunStoredProcRV("sp_Review_Allocate_Check", Me.MasterID)
    
        If lngCheck >= 1040 Then
            Cancel = True
            Me.AllocatedUser.Undo
            MsgBox "You cannot change the Reviewer as one or more reviews for this Master customer group has already been finalised.", vbCritical, "Review Already Finalised"
        ElseIf lngCheck >= 1020 Then
            If MsgBox("The Reviewer has already started one or more reviews within the Master Customer Group." & vbCrLf & vbCrLf & _
                      "Are you sure you want to change the Reviewer?", vbQuestion + vbYesNo + vbDefaultButton2, _
                      "Review In Progress - Change Reviewer?") = vbNo Then
                Cancel = True
                Me.AllocatedUser.Undo
            End If
        End If
    
    My_Exit:
        Exit Sub
        
    My_Error:
        MsgBox Err.Number & vbCrLf & Err.Description
        Resume My_Exit
    
End Sub

SQL Server trigger:

ALTER TRIGGER [dbo].[trg_Review_Allocate_on_Master]
ON [dbo].[tbl_Review] 
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    IF UPDATE (AllocatedUser)
    BEGIN
        DECLARE @MasterID int
        DECLARE @AllocatedUser varchar(50)
        DECLARE @AllocatedDate date
        DECLARE @NewStatus int

        --  GET MasterID and New Allocated User
        SELECT @MasterID = MasterID,
               @AllocatedUser = AllocatedUser,
               @AllocatedDate = AllocatedDate,
               @NewStatus = ReviewStatusID
        FROM inserted i
        INNER JOINtbl_Customer c ON i.CustomerID = c.CustomerID

        UPDATE tbl_Review
        SET    AllocatedUser = @AllocatedUser,
               AllocatedDate = @AllocatedDate,
               ReviewStatusID = @NewStatus
        FROM tbl_Review r
        INNER JOIN tbl_Customer c ON r.CustomerID = c.CustomerID
        WHERE c.MasterID = @MasterID
          AND c.CustomerID <> @MasterID
    END
END