Checking for null on close

[magmike]

I have a field that I want to ensure is not forgotten on a form's
close after new record is created.

I have used the following code on a forms Close Buttons OnClick event,
and I did something wrong! Help me figure it out if you have a minute.

Thanks - the code:

Private Sub Close_Click()
On Error GoTo Err_Close_Click
Dim stMessage As String
Dim cmdClose As String

stMessage = "Choose Male or Female"
cmdClose = "DoCmd.Close"

IIf ContactGender Is Not Null, cmdClose, MsgBox(stMessage)

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

When I click the close button, regardless of whether the field is null
or not, it displays the message, then a second one that says, Object
Required.

Thanks in advance!

magmike7

 

[Jeff Boyce]

The If ... Then ... Else statement you included seems to be missing the
"Then" and "Else" keywords.

Check Access HELP for the correct syntax for this function...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Abu]

If the field is always required, you could have table level validation
to make sure the data is entered.
1. To do this, open the table with this field in design view.
2. Click on the field you want to validate.
3. Under the General Properties, enter the validation rule 'Is Not
Null' without the quotes.
4. Under Validation Text, enter 'Choose Male or Female.' <-- This can
be any message you want the user to be given.

This will always make sure before the record is saved, the field has a
value.

If you wish to go the code way, and based on your problem description,
try the code below:

Private Sub Close_Click()
On Error GoTo Err_Close_Click
Dim stMessage As String

stMessage = "Choose Male or Female"

If IsNull(Me.ContactGender) Then
MsgBox stMessage
Else
DoCmd.Close acForm, Me.Name
End If

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

 

[John W. Vinson]

I have a field that I want to ensure is not forgotten on a form's
close after new record is created.

The Close event is too late: the erroneous data has already been written to
disk before your Close code even starts executing. Use the form's BeforeUpdate
event (which will fire even if the user tries to save the record in some way
other than closing the form).

I have used the following code on a forms Close Buttons OnClick event,
and I did something wrong! Help me figure it out if you have a minute.

Thanks - the code:

Private Sub Close_Click()
On Error GoTo Err_Close_Click
Dim stMessage As String
Dim cmdClose As String

stMessage = "Choose Male or Female"
cmdClose = "DoCmd.Close"

IIf ContactGender Is Not Null, cmdClose, MsgBox(stMessage)

Take a look at the online help for IIf. This is simply meaningless code, I'm
surprised it even compiles!

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsNull(Me!ContactGender) Then
MsgBox "Choose Male or Female"
Me!ContactGender.SetFocus
Cancel = True
End If
End Sub