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.

 

[Pat Hartman]

If you want to make a piece of reusable code, you can use the form's fields collection and check for fields that are required. Now for the tricky part. If your control names = ControlSource, then you can reference the controls using the Name property of the fields collection. If you prefix your control names or are sloppy and let Access name them text46 and combo88, then you are SOL unless you can figure out what the prefix might be for any given field. Is that what you are looking for?

I NEVER use Me.controlname.Undo EXCEPT if I am cancelling the update because the user is not authorized to make a change so no value will be acceptable. Otherwise, using .undo is rather unfriendly since the user always has to completely retype the entry and he won't know whether he made a typo or the value he is entering is just invalid.

 

[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.