Required Field error not caught by Exit button (1 Viewer)

[stepone]

Hello all,

I have a table with 4 required fields, and a form to add new entries to the table. While testing, if I don't enter a value in one of the required fields, and press the standard 'X' close button at the top right of the form, I get an error message "The field xxxxx.xxxxx cannot contain a Null value because the Required property for this field is set to true. Enter a value in this field".

However, I also have added an 'Exit' button to the form with the following VBA code ;

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

    DoCmd.Close

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_cmdExit_Click
    
End Sub

If I leave one of the required fields empty and press this button the form closes with no error message, and does not update the record.

Can anyone tell me why the database reacts differently to the standard 'X' close button and my button which uses 'docmd.close' ? It's driving me mad.

Thanks,
StepOne

 

[RuralGuy]

Have a look here: http://allenbrowne.com/bug-01.html

 

[ghudson]

Check out my Enable/Disable The Control Box X Button sample.

The form named "fTestCancelFromUnloadEvent" in the sample db will demonstrate how you can prevent a user from closing a form and also the application unless they do exactly what you want them to do.

You need to place all of your "validation testing" in the forms BeforeUpdate event.

 

[stepone]

You need to place all of your "validation testing" in the forms BeforeUpdate event.

Thanks G, that will be useful - but I think I am still missing something. If I put all my validation in the beforeUpdate event, I seem to have problems cancelling the calling action.

I have 3 buttons on my form ; "Print Job", "Add another Job", "Exit".

For each of these the first thing I do is a Save Record - at that point the beforeUpdate event fires, and validation runs. But, if (for example) the user presses 'Exit' and validation fails in the beforeUpdate event, the update is cancelled, but the rest of the code runs, and the form still closes, without any warning that the update failed.

So when I call 'DoCmd.RunCommand acCmdSaveRecord', how do I comunicate back to the calling function if the update fails ?

I'm probably missing something very simple - sorry to be a pain !

StepOne

 

[workmad3]

easiest way would be to get rid of the X close button. Just go to the form properties and switch close button to no

 

[ghudson]

You need to post your code before I can answer. Post all the code in one of your buttons and also post all of the the code in your forms before update event. Include the sub names as well.

 

[stepone]

Hi G,

Thanks for your help with this. Here is code from the forms beforeUpdate event, my "Print Process Sheet" button, and my sub "checkKeyFields" which does the validation. I've cut out some of the stuff to make it simpler to read, but basically the logic is all there. Bizarrely, clearing out the junk seems to make it work, but I would appreciate you taking a look to see if my logic is correct. The only slight problem now is that if validation fails I get my message box saying 'please enter all key fields' and then an Access error "2501 The RunCommand action was cancelled". Would the best thing to do be to trap this 2501 error in the cmdPrint_Click routine and do nothing if it fires, since I can assume my error message will have been displayed ?

Thanks again for your help - I can hack my way around a lot of these things, but my goal is to do things properly, with clean solutions and the minimum of custom code, as PatHartman I think says - we'd be amazed at how little code he actually writes.

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

    Dim stDocName As String
    Dim stWhereCondition As String
        
    stDocName = "rptProcessSheet"
    stWhereCondition = "db_iJobReference = " & txtJobReference
    
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport stDocName, acPreview, , stWhereCondition

Exit_cmdPrint_Click:
    Exit Sub

Err_cmdPrint_Click:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_cmdPrint_Click
    
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

    If checkKeyFields = False Then
        Cancel = True
    End If

exit_Form_BeforeUpdate:
    Exit Sub

Err_Form_BeforeUpdate:
    MsgBox Err.Number & " " & Err.Description
    Resume exit_Form_BeforeUpdate

End Sub

Public Function checkKeyFields() As Boolean
On Error GoTo err_checkKeyFields

    If Nz(Me.txtJobReference, "") <> "" And _
       Nz(Me.cmbUserID, "") <> "" And _
       Nz(Me.cmbClientID, "") <> "" And _
       Nz(Me.txtCustomer, "") <> "" And _
       Nz(Me.frmSubJobInfoTypes![cboinformationtype], "") <> "" Then
        checkKeyFields = True
    Else
        checkKeyFields = False
        MsgBox "Please enter all Required Fields (marked with '*').", vbExclamation, "Error"
    End If

exit_checkKeyFields:
    Exit Function

err_checkKeyFields:
    MsgBox Err.Number & " " & Err.Description
    Resume exit_checkKeyFields
    
End Function

 

[ghudson]

I would also add your valadation code to the top of event that is calling the report.

    If checkKeyFields = False Then
        Exit Sub
    End If

That way you can stop the process [exit the sub] if one of your key fields is null. And yes, trap for the cancel error # 2501 for that is happening when the report is cancel either by code of by the user clicking the print reports cancel option.

 

[Pat Hartman]

I always trap the 2501 and ignore it since it is meaningless.