Null Field to Interupt Closing

[Gee]

I am using this code to check for blank fields in BeforeUpdate:

If NZ(Trim(Me!Date),vbnullstring) = vbnullstring then
Msgbox "PLEASE ENTER DATE",,"Missing date"
Me!Date.setfocus
Cancel = True
Exit Sub
End if

It almost works, except the form goes ahead and closes anyway. The user
clicks the close button and the message box pops up if the field is empty,
they click OK and instead of keeping the form open and then setting focus on
the empty field, it closes the form.
How can I get it to interupt the close and keep the form open?
Thank you in advance for any help you can give me.
Gee

 

[RonaldoOneNil]

Put the code in the On Unload event instead.

 

[Stefan Hoffmann]

hi Gee,

If NZ(Trim(Me!Date),vbnullstring) = vbnullstring then

You should use a meaningful name for your control, e.g. txtInvoiceDate.

How can I get it to interupt the close and keep the form open?
Thank you in advance for any help you can give me.

Use the Form_Unload event:

Private Sub Form_Unload(Cancel As Integer)

Cancel = Me.IsDirty And (Len(Trim(Nz(txtInvoiceDate.Value, ""))) = 0)

End Sub


mfG
--> stefan <--

 

[Gee]

Didn't work...I click the close button and it still closes after I click OK
on the pop up button. It still doesn't leave the form open and set focus on
the blank field.
Any other ideas?
This is driving me mad.

 

[Gee]

Hey!
I figured it out so for future reference, I put it in my Close Commandas an
If, Then, Else:
Private Sub Command114_Click()
On Error GoTo Err_Command114_Click
If Nz(Trim(Me!Date), vbNullString) = vbNullString Then
MsgBox "Date Field is required", , "Missing date"
Me!Date.SetFocus
Cancel = False
Else
DoCmd.Close

Exit Sub
End If

Exit_Command114_Click:
Exit Sub

Err_Command114_Click:
MsgBox Err.Description
Resume Exit_Command114_Click

End Sub