Form Before Update Cancel Event doesn't work (1 Viewer)

sportsguy

Finance wiz, Access hack
Local time
Today, 19:35
Joined
Dec 28, 2004
Messages
358
I have a data entry form based on a query on a table with primary keys. I am using before update to validate that there is data in the fields before saving. When the fields are null, the error message comes up, but then the form tries to save the record and I get error 2015 -

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_BeforeUpdate

    If IsNull(Me.Medical_Record) Then
        MsgBox "Please enter a Medical Record Nbr prior to Saving!", vbCritical, "DATA QUALITY"
        Me.Medical_Record.SetFocus
        Cancel = True
    ElseIf IsNull(Me.Date_RestraintStart) Then
        MsgBox "Please enter the Restraint Start Date prior to Saving!", vbCritical, "DATA QUALITY"
        Me.Date_RestraintStart.SetFocus
        Cancel = True
    ElseIf IsNull(Me.StartTime) Then
        MsgBox "Please enter the Restraint Start Time prior to Saving!", vbCritical, "DATA QUALITY"
        Me.StartTime.SetFocus
        Cancel = True
    ElseIf IsNull(Me.Type_of_Restraint) Then
        MsgBox "Please enter a Type of Restraint prior to Saving!", vbCritical, "DATA QUALITY"
        Me.Type_of_Restraint.SetFocus
        Cancel = True
    End If

Exit_BeforeUpdate:
    Exit Sub

Err_BeforeUpdate:

    Debug.Print Err.Number & " - " & Err.Description

    Goto Exit_BeforeUpdate

End Sub

Thanks in advance
sportsguy

Error message from debug is
2105 - You can't go to the specified record.

What am I missing?
 

bob fitz

AWF VIP
Local time
Today, 23:35
Joined
May 23, 2011
Messages
4,717
Have you tried stepping through the code one line at a time to find the line that throws the error
 

missinglinq

AWF VIP
Local time
Today, 19:35
Joined
Jun 20, 2003
Messages
6,423
Not sure the error message has anything to do with the posted code, since no where does it mention going to any Record, unless it's because you've Canceled Saving the Record and you tried moving to another Record...but in this kind of multiple validations you need to include the Exit Sub command...else it will simply 'drop through' all code until it reaches the final validation test. For example

Code:
If IsNull(Me.Medical_Record) Then
        MsgBox "Please enter a Medical Record Nbr prior to Saving!", vbCritical, "DATA QUALITY"
        Me.Medical_Record.SetFocus
        Cancel = True

needs to be

Code:
If IsNull(Me.Medical_Record) Then
        MsgBox "Please enter a Medical Record Nbr prior to Saving!", vbCritical, "DATA QUALITY"
        Cancel = True
        Me.Medical_Record.SetFocus
        Exit Sub
and so forth, for each validation.

Linq ;0)>
 
Last edited:

sportsguy

Finance wiz, Access hack
Local time
Today, 19:35
Joined
Dec 28, 2004
Messages
358
missinglinq

That was one part of the problem, so thank you! The other part was that the form was doing the validation in the BeforeUpdate code and the prior designer had done some in control validation which was failing it took me awhile to find that error!

Thanks everyone for reading and commenting. .

sportsguy
 

TerraD

New member
Local time
Tomorrow, 00:35
Joined
Nov 6, 2019
Messages
2
I tried to understand the idea behind the recommended code:
...needs to be

Code:
If IsNull(Me.Medical_Record) Then
        MsgBox "Please enter a Medical Record Nbr prior to Saving!", vbCritical, "DATA QUALITY"
        Cancel = True
        Exit Sub 
        Me.Medical_Record.SetFocus
and so forth, for each validation.
[/B]

I think 'Exit Sub' will cause the code of the Sub to be terminated at once and thus 'Me.Medical_Record.SetFocus' will never be executed. What am I missing?
 

missinglinq

AWF VIP
Local time
Today, 19:35
Joined
Jun 20, 2003
Messages
6,423
Nothing! You're right...trying to do too many things at once!

Corrected above!

Good catch!

Linq ;0)>
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:35
Joined
Oct 29, 2018
Messages
21,358
Nothing! You're right...trying to do too many things at once!

Corrected above!

Good catch!

Linq ;0)>
Hi Linq. It took over three (3) years, but you didn't think you'll get away with it forever, did you?
 

TerraD

New member
Local time
Tomorrow, 00:35
Joined
Nov 6, 2019
Messages
2
:) I know that all too well. Just wanted to make sure that I got that correct!

This thread helped me to find the reason of my problem anyway - it was far more basic: The Sub had been renamed somhow from 'Form_BeforeUpdateform' to 'Form_BeforeUpdateform_Err' - that way it was never executed. :mad:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:35
Joined
Oct 29, 2018
Messages
21,358
:) I know that all too well. Just wanted to make sure that I got that correct!

This thread helped me to find the reason of my problem anyway - it was far more basic: The Sub had been renamed somhow from 'Form_BeforeUpdateform' to 'Form_BeforeUpdateform_Err' - that way it was never executed. :mad:
Hi TerraD. Welcome to AWF!


Glad to hear the forum was able to help you out. Cheers!
 

Users who are viewing this thread

Top Bottom