Form and Table level validation

[optiaccess]

Hello there,
I have a form in which I want to validate fields.
The following code is working well in the Form_BeforeUpdate event

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.OrderNumber) Or Me.OrderNumber& "" = "" Then
    MsgBox "Must enter Order Number", vbInformation
    Cancel = True
    With Me.OrderNumber
        .SetFocus
        .Undo
    End With
End If
End Sub

I would also like Access to check fields in case i'm missing something in my code, so I set the Required property to yes and added the following in the Form_Error event

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
        If DataErr = "3314" Then
            MsgBox "Must enter data.", vbExclamation
            Response = acDataErrContinue
        End If
    End Sub

But now my Form_BeforeUpdate Message is not showing neither before nor after the one from the Error event.

I want Form_Error and Form_BeforeUpdate both to show their custom message.
Would it be posible to allow access to check required fields and at the same time use my validations in the Form_BeforeUpdate Event?

 

[NauticalGent]

A couple of observations.
I do not think the Me.OrderNumber.Undo is necessary; the Cancel = True is supposed to retire all old values prior to the last time the record was saved.

Also, I stopped using Table-level validation such as required fields for this very reason. You can do ALL your validation checks on the Before Update event and handle your errors there. I have found that Case statements work great for this.

 

[optiaccess]

Thanks for your suggestions, and you are both right about the .Undo. I forgot to mention that I Just want to prevent some empty fields.

What I'm looking for is a way to temporarily keep the required property while I finish my code on beforeUpdate, but I guess I'll have to disable required on each field as I add them into my form validation.