Cancel = True error

[kwor]

I have a form that requires some mandatory information. The following code checks the form before updating.


Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Surname) Or IsNull(Me.Address) Or IsNull(Me.Suburb) Then
MsgBox ("Surname, Address and Suburb are mandatory"), vbInformation
Me.Surname.SetFocus
Cancel = True
End If
End Sub

This works correctly but after the msgbox is displayed, another Access message is displayed:
"The setting you entered isn't valid for this property"
This message is caused by the "Cancel = True" command.
How do I stop this?

 

[dhookom]

The code worked as state for me. What happens if you comment out the setfocus line?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya kwor . . .

Try this instead:

[blue]   MsgBox "Surname, Address and Suburb are mandatory!", vbInformation[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kwor]

Duanne
No change if I comment out the setfocus line.
If I comment out the cancel = true line, the message does not appear. However, the form closes and add the partially completed record.

Aceman
I tried that - no difference.

The code was in Access 2003 format running under Access 2007. I tried running it under A2003 but it made no difference.

 

[PHV]

What about Me.Undo ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kwor]

I have found the errant code but a problem remains.

The form has a "Close" button. The code in "On click" event caused the message:

If Me.Dirty Then Me.Dirty = False
DoCmd.Close

Removing the "dirty" line stopped the message.

Now for the problem.
When the user clicks "Close", the "On Click" event is called and tries to close the form.
Then the "Form_BeforeUpdate" event is called to check for mandatory data fields. If fields are determined to be empty, the message is displayed and the update is cancelled - no record written to the table. However, the form still closes. The close operation should be stopped so that the user can enter all the required data.

 

[PHV]

Replace this:
If Me.Dirty Then Me.Dirty = False
with this:
If Me.Dirty Then Exit Sub

So the user can't click the close button without save or cancel before.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kwor]

The following code on the "Close" event works but..

If Me.Dirty Then
If IsNull(Me.Surname) Or IsNull(Me.Address) Or IsNull(Me.Suburb) Then
MsgBox "Surname, Address and Suburb are mandatory", vbInformation
Me.Surname.SetFocus
Exit Sub
Else
DoCmd.Close
End If
End If

The "Form_BeforeUpdate" event is now not called, so the user cannot cancel the data entry even if the Esc key is pressed.
The "Close" event works but the user cannot cancel the data entry and all fields must be entered.

 

[PHV]

Why should the user click a Close button to save or cancel ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kwor]

My original thoughts were that if a user wanted to cancel any changes and not save the record, they could press the Esc key and close the form. My head hurts, so I have included a "Cancel" button and everything works.
Thanks to all that have given me guidance.



Private Sub Close_Click()
On Error GoTo Err_Close_Click

If IsNull(Me.Surname) Or IsNull(Me.Address) Or IsNull(Me.Suburb) Then
MsgBox "Surname, Address and Suburb are mandatory", vbInformation
Me.Surname.SetFocus
Exit Sub
Else
Forms!frmContacts.Requery
DoCmd.Close
End If

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click

If Me.Dirty Then
DoCmd.RunCommand acCmdUndo
DoCmd.Close
Else
DoCmd.Close
End If

Exit_Cancel_Click:
Exit Sub

Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click

End Sub