MsgBox based on specified criteria

[P-chu via AccessMonster.com]

A little assistance with this one please. I have a single form for data
entry. One field is a [date] and the other one is [result]. The [date] is
not to be put in until the form is being closed out, sometimes it is in
transient. So if I put in a date, but forget to change the result to done, I
want a msgbox on that will popup as I am attempting to close the form. I
also do not want the form to close unless I change result to done or remove
the date instead. Here is what I have:

Private Sub Form_Close()
If Not IsNull[date] and [result] = "pending" Then
msgbox "Either fill in the done result or delete the completion date."
End If
End Sub

I'm getting a compile error on the [date] or the not isnull. Any suggestions
are greatly. I'm sure it's probably something simple I am just not looking
at correctly.

Thanks.

 

[Allen Browne]

1. The form's Close event is too late.
Any edits have already been saved by this time.
Use the form's BeforeUpdate event instead.

2. You omitted the function brackets for IsNull.
Try:
IsNull([date])

3. Cancel the event if the data isn't right:
Cancel = True

(Note that a field named date can give you problems too.)

 

[Stuart McCall]

A little assistance with this one please. I have a single form for data
entry. One field is a [date] and the other one is [result]. The [date]
is
not to be put in until the form is being closed out, sometimes it is in
transient. So if I put in a date, but forget to change the result to
done, I
want a msgbox on that will popup as I am attempting to close the form. I
also do not want the form to close unless I change result to done or
remove
the date instead. Here is what I have:

Private Sub Form_Close()
If Not IsNull[date] and [result] = "pending" Then
msgbox "Either fill in the done result or delete the completion date."
End If
End Sub

I'm getting a compile error on the [date] or the not isnull. Any
suggestions
are greatly. I'm sure it's probably something simple I am just not
looking
at correctly.

Thanks.

Yes it is something simple you're overlooking. This line:

If Not IsNull[date] and [result] = "pending" Then

should read:

If Not IsNull([date]) and [result] = "pending" Then

Notice the parens surrounding [date].

Also, you need to put your code in the form's Unload event, so you can set
the Cancel argument to True, thereby preventing closure of the form:

Private Sub Form_Unload(Cancel As Integer)
If Not IsNull[date] and [result] = "pending" Then
msgbox "Either fill in the done result or delete the completion date."
Cancel = True
End If
End Sub

Incidentally, date is a reserved word in Access so you need to either
*always* remember to use square brackets as you are doing here, or change
the name to something more specific, like InvoiceDate for example.

 

[P-chu via AccessMonster.com]

I'm not really using the control field name "date". That was just an example
to indicate that field is requiring a date. However, I tried using the
information on the Unload and also BeforeUpdate, but I still got a compile
error on this line:

If Not IsNull([date]) And [result] = "pending" Then

I used the parenthesis as suggested, which makes perfect since.

Anything else I am overlooking please?
Thanks.

A little assistance with this one please. I have a single form for data
entry. One field is a [date] and the other one is [result]. The [date]

[quoted text clipped - 20 lines]

Thanks.

Yes it is something simple you're overlooking. This line:

If Not IsNull[date] and [result] = "pending" Then

should read:

If Not IsNull([date]) and [result] = "pending" Then

Notice the parens surrounding [date].

Also, you need to put your code in the form's Unload event, so you can set
the Cancel argument to True, thereby preventing closure of the form:

Private Sub Form_Unload(Cancel As Integer)
If Not IsNull[date] and [result] = "pending" Then
msgbox "Either fill in the done result or delete the completion date."
Cancel = True
End If
End Sub

Incidentally, date is a reserved word in Access so you need to either
*always* remember to use square brackets as you are doing here, or change
the name to something more specific, like InvoiceDate for example.

 

[P-chu via AccessMonster.com]

I got it working. Thanks to everyone.

I'm not really using the control field name "date". That was just an example
to indicate that field is requiring a date. However, I tried using the
information on the Unload and also BeforeUpdate, but I still got a compile
error on this line:

If Not IsNull([date]) And [result] = "pending" Then

I used the parenthesis as suggested, which makes perfect since.

Anything else I am overlooking please?
Thanks.

A little assistance with this one please. I have a single form for data
entry. One field is a [date] and the other one is [result]. The [date]

[quoted text clipped - 25 lines]

*always* remember to use square brackets as you are doing here, or change
the name to something more specific, like InvoiceDate for example.