CancelEvent not working.

  • Thread starter Thread starter gee
  • Start date Start date
G

gee

I have a form where the user fills in all the fields. Then they can save the record or close it.
When they go to save, on the onclick event, the first thing that is supposed to happen is to check
and make sure all the required fields are populated. Then, if it finds a null value, it is supposed
to cancel the event, alert the user to fill in the field, then set the focus to the corrosponding
field.

If all the fields show to be populated, it then runs an update query to add the information to a
table. This all works fine as long as the sql string is contained in the if/else statement. But if
I place it after- it still tries to run the sql when there is a null value? It would be ok add it
to the if/else statement, but there are several events I want to use that will require the fields to
be validated, so I want to place the if/else statement in a module so I can call it whenever I need
it. (which I did and it works the same) below is a sample of the code. What I am wondering is why
isn't the cancel event working after it finds a null value?


If IsNull(Forms!frmRIANew!fldManufac) Then
DoCmd.CancelEvent
MsgBox "The Manufacturer field is required."
Forms!frmRIANew!fldManufac.SetFocus
ElseIf IsNull(Forms!frmRIANew!fldPartDesc) Then
DoCmd.CancelEvent
MsgBox "The Description field is required."
Forms!frmRIANew!fldPartDesc.SetFocus
ElseIf IsNull(Forms!frmRIANew!fldEquip) Then
DoCmd.CancelEvent
MsgBox "The Equipment field is required."
Forms!frmRIANew!fldEquip.SetFocus
end if

docmd.runsql .........
 
Hi Gee,

Docmd.Cancel event only applies to Form events, it is not a mechanism for
structuring your VBA. Instead, try using a local boolean variable to
indicate whether to proceed with the SQL - for example in the following, the
docmd.runsql command will only execute if fok is true. The branches of the
If condition set the value to false when the validations fail.

dim fOk as boolean
fok = true

If IsNull(Forms!frmRIANew!fldManufac) Then
fok=false
MsgBox "The Manufacturer field is required."
Forms!frmRIANew!fldManufac.SetFocus
ElseIf IsNull(Forms!frmRIANew!fldPartDesc) Then
fok=false
DoCmd.CancelEvent
MsgBox "The Description field is required."
Forms!frmRIANew!fldPartDesc.SetFocus
ElseIf IsNull(Forms!frmRIANew!fldEquip) Then
fok=false
DoCmd.CancelEvent
MsgBox "The Equipment field is required."
Forms!frmRIANew!fldEquip.SetFocus
end if

if fOk then
docmd.runsql .........
endif
 
Thank you Sandra.

I took what you gave me and was able to make it work when calling it from a module. What I don't
understand though, is why it workd inside the if/else statement, but didn't work outside of it?
Isn't a command button part of the form's events? The option to save was triggered from a command
button on the form.

thank you agian!

greg
 
Back
Top