Stop record save after validation fails

[ExcessionOCP]

Hi,



I am using my form's "Before Update" event to perform a bunch of validation on the entries that the user has made in the form. The validation logic is all working fine, however I'm finding that although the validation code works, and alerts the user to their mistakes, the "Before Update" event completes and the next actions that are bound to the save button (that I'm using to trigger the "Before Update") continue and the record is saved regardless.

I have tried using "End" in my validation code when it detects an error, but this closes the form, which isn't desirable.

I thought I could pass a return value from my error checking sub to the "On Click" event that saves the record, but you can't return values from a Sub.

I feel like I'm missing something obvious here - does anyone have any ideas? Thanks!

[Bob Fitz]

In the Beore Update event, use:
Cancel = True
to stop the update.

[Edgar]

The Before Update event comes with a Cancel parameter. It's set to a default argument of False. You must set it to true.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If isNull(someControl) Then
        Cancel = True
        Msgbox "Not saved"
    Else
        Msgbox "Saved"
    End if
End Sub

[Welshgasman]

Have you thought to lookup the syntax etc for that event?
https://learn.microsoft.com/en-us/of...reupdate-event

[ExcessionOCP]

Thanks all for your replies. I think I'm missing something fundamental though. I have a button on my form who's OnClick Event has the following code in it:

DoCmd.Save
DoCmd.Close acForm, "frmEnterLineItem" 'close the form
DoCmd.OpenForm "frmEnterLineItem" 'reopen the form for new data entry

I now understand that the "DoCmd.Save" command will trigger the form's BeforeUpdate event, and I can build my error-detection code within that.

My question is though, if the error detection code detects some incorrect data in the form and asks the user to redo it, how do I stop the main "DoCmd.Save" from firing once the BeforeUpdate event code finishes running? Do I need to put the "DoCmd.Save" into a while loop and detect the return value of the BeforeUpdate Sub? If so, how do I do that - I wasn't sure that Subs can return any values.

As you can tell I'm a bit stumped and hope someone can help.

Cheers,

Phil

[Welshgasman]

Your save is not doing what you think it is?
https://learn.microsoft.com/en-us/of...ess.docmd.save

Perhaps Google?
From https://www.google.com/search?q=save...hrome&ie=UTF-8

there is
https://answers.microsoft.com/en-us/...9-fef162fa3093

[orange]

Can you describe in simple English WHAT you are doing with the form(s) involved? Sometimes writing the process down will help clarify the requirement and approach. You could show us the code behind the form, or attach a copy of the database with instructions to highlight an issue.

Oooops I see Paul has responded while I was typing.

[moke123]

Simple enough to test. Try Edgar's code in post 3 and see what happens.

I thought I could pass a return value from my error checking sub to the "On Click" event that saves the record, but you can't return values from a Sub.

Put your validation code into a function and return a boolean.

Then you can use

Cancel = YourFunctionName

but you can't return values from a Sub.

You can, sort of, but a function is better suited.

Sub SubOne()
    Dim x As Integer
    x = 12
    SubTwo x
    Debug.Print x
End Sub


Sub SubTwo(ByRef x As Integer)
    x = 100
End Sub

[ExcessionOCP]

Welshgasman - Your insinuation that I haven't searched around on this topic before posting is unjustified. Believe it or not there are quite a few different google searches one can perform on a subject, so if I had missed one it's hardly surprising. I use this forum as a last-resort, not as a first.

[moke123]

how do I stop the main "DoCmd.Save" from firing once the BeforeUpdate event code finishes running?

Basic framework could look something like

Private Sub Command2_Click()

    If ChkData Then
        MsgBox "Nope, Bad Data"
        Me.Undo
    Else
        DoCmd.Close acForm, Me.Name
    End If
    
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Cancel = ChkData
End Sub

Private Function ChkData() As Boolean
    'your validation code here
    'if data is valid return false
    'if data is bad return true
End Function

[ExcessionOCP]

That looks awesome and exactly what I was looking for moke123 - thank you! I'll have a go at implementing that and let you know how I get on.