Prevent form/record update

[Pat Hartman]

Your validation code Must go in the Form's BeforeUpdate event if it involves checking for empty values or comparisons between multiple values.

To keep the form from closing after displaying the error, you have to cancel the form's unload event. To know whether to cancel the event requires a form level variable that is set in various events.

Option Compare Database
Option Explicit
Private bError As Boolean
....
Private Sub Form_Current()
    bError = False
....
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.SomeField & "" = "" Then   ' control is empty
        Cancel = True
        bError = True
        Me.SomeField.SetFocus
        Exit Sub
    End If
....

Private Sub Form_Unload(Cancel As Integer)
    If bError = True Then
        Cancel = True
        bErrror = False
        Exit Sub
    End If

 

[Shinobi]

Well thanks for your input Pat but I've tried applying your code and unfortunately it doesn't have the desired effect. I tried to edit it a bit to fit my need but still no luck.

The crux of this is the msg box that both informs the user that they are unable to save the record with the empty field and also give them the option to continue with the action (i.e. closing or navigating records) or to cancel the action and continue editing the record.

So do you have any theories on how this can be achieved using your version? Cheers

 

[Pat Hartman]

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.SomeField & "" = "" Then   ' control is empty
        Cancel = True
        If MsgBox("some error msg.  Press Yes to cancel the update.  Press No to fix the problem.",vbYesNo) = vbYes Then            
            Me.Undo
            bError = False
            Exit Sub
        Else
            Me.SomeField.SetFocus
            bError = True
            Exit Sub
        End If
    End If

PS it helps us to actually see the code that isn't working. I'm good but I'm not omniscient .