Checking Total Values of Fields = Total Quantity in Form

Question

I am creating a form and have 1 Total Quantity and 3 other separate fields.

I would like to check and prompt when user input is more than the Total Quantity.

Example:

Total Quantity: 10

Site 1: 5

Site 2: 3

Site 3: 1

If the total quantity of Site 1, Site 2 and Site 3 is more than 10, I would like to halt save data or prompt an error.

PS: New to Access 2016 currently, pardon if this is a basic question.

Thank you.

Answer 1

Set form's Before Update event to "[Event Procedure]" and the actual field names inside the brackets [] below.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    With Me
        If Nz(![Site 1], 0) + Nz(![Site 2], 0) + Nz(![Site 3], 0) > ![Total Quantity] Then
            Cancel = True
            MsgBox "Invalid quantity."
        End If
    End With
End Sub