best way to stop accidental deletes of data from field (1 Viewer)

[hbrehmer]

Sorry but this is the WRONG event. You need to use the BeforeUpdate event so that you can cancel the save and prevent the bad data from becoming permanent. If you use the control's BeforeUpdate event, you will STILL need code in the form's BeforeUpdate event because if a user doesn't dirty a control, its events do not run so you would always allow a new record to be added even if the CustomerName is empty because your code on the control didn't run. Unless, I want to give immediate feedback, I use the Form's BeforeUpdate event for all validation. That way, the validation is always in one place and I don't need to do it twice. Also, you should make fields that cannot validly be empty required and set their AllowZeroLengthString properties to No.

Private Sub Form_BeforeUpdate(Cancel)
    If Me.CustomerName & "" = "" Then
        msgbox "Customer Name is required.", vbOKOnly
        Me.CustomerName.SetFocus
        Cancel = True
        Exit Sub
    End If
    If  Me.CustomerName <> Nz(Me.CustomerName.OldValue,0) Then
        x = MsgBox("Are you sure you want to change Customer Name?", vbYesNo + vbQuestion)
        If x = vbYes Then
        Else
            Msgbox "Name was not saved.  Press Save again to save other changes.", vbOKOnly
            Me.CustomerName.SetFocus
            Me.CustomerName = Me.CustomerName.OldValue
            Cancel = True
            Exit Sub
        End If
    End If
End Sub

This code needs more work because as written, it is only validating the Customer Name.

PS, I'm a little confused since you named the field CustomerName rather than CustomerID. Is it text or numeric? This code assumes numeric because your code assumed numeric.
Always use "Me." when referencing form/report controls. It is more efficient and gives you intellisense.
When working with bound forms, understanding which event to use for what purpose is the key.

Thank you for the great advise. I figured this out. I always get confused on the "Me." usage. Your description is very helpful.

 

[Isaac]

Well, it depends on which object's "update" we're talking about. If you choose to perform your validation immediately after someone enters something in the control, then the control's AfterUpdate event is perfectly reasonable. (Which is what I think you were actually talking about in your earlier post). If you choose to perform your validation on the Form level, then the Form's BeforeUpdate is where you want to be...To me anyway.

It just depends on when you would rather do the validation. Which can depend on how complex the form is, how long it takes to fill out, and how "soon" you want your user to know that what they have entered is invalid.

For example, if I was filling out an sales Order form, and there was a zip/postal code field, and if it could be determined by code that the postal code I'd entered was a "we don't do business here" code, I'd like to know right away (!), before I take the trouble of filling out the whole thing - thus the control's AfterUpdate event might make perfect sense. Even if there is a slight duplication required by having to have it on the form's BeforeUpdate event as well. It all depends on timing, effort and business process how it will come across to the user whether validation failure messages can wait until the very end to show, or whether they'd benefit from showing earlier, too.

Adding one thing I have done in the past. When doing validation on numerous controls, in the form's BeforeUpdate event, changing the color (maybe to red) of any control that fails validation is a nice touch. Helps the user quickly spot problems, and aligns with behavior they're used to from webpages and other apps.

 

[bob fitz]

IMHO validation can be done in the Before Update event of a control as well but validation in the Update event of the form is a must.

 

[Isaac]

Bob, I agree with you. The "must" is because the top priority must be making sure an invalid-filled out record is absolutely not allowed.
Then additional validations (such as those on Controls' events), might be warranted depending on how helpful it is to notify a user before waiting until the end.

 

[bob fitz]

Bob, I agree with you. The "must" is because the top priority must be making sure an invalid-filled out record is absolutely not allowed.
Then additional validations (such as those on Controls' events), might be warranted depending on how helpful it is to notify a user before waiting until the end.

Indeed

 

[Pat Hartman]

I guess we all agree that the FORM's BeforeUpdate event is the goto event for validation.