Allow null value in a field

[jubiiab via AccessMonster.com]

Hi!

I have a form with a simple code:

Private Sub Form_Open(Cancel As Integer)

If [Service] < Now() Then
MsgBox "Service date overdue!", vbInformation, "Service Date"

End If
End Sub

The code above works fine if I just have one record in my form with a date.
If the service field is empty I get an error message because the form has a
null value in that field. I want the option to have that field to be empty.
What should I add more to the code?

 

[Allen Browne]

Form_Open is too early.

Move the code into the Current event of the form (if you want to be notified
every time you view a record that's overdue), or into the BeforeUpdate event
of the form (not text box) if you want to be notified when you actually
enter the date.

If you just want a nice, unobtrusive notification whena date is overdue, add
a text box to the form and set its Control Source to:
=IIf([Service] < Now(), "Overdue!", Null)
Make the text big and red. It will automatically show up as you move from
record to record.

 

[jubiiab via AccessMonster.com]

No it’s not too early. My users want to be notified when the form opens –
trust me! Please just help me with the code and question I have above. I know
you can solve it

Form_Open is too early.

Move the code into the Current event of the form (if you want to be notified
every time you view a record that's overdue), or into the BeforeUpdate event
of the form (not text box) if you want to be notified when you actually
enter the date.

If you just want a nice, unobtrusive notification whena date is overdue, add
a text box to the form and set its Control Source to:
=IIf([Service] < Now(), "Overdue!", Null)
Make the text big and red. It will automatically show up as you move from
record to record.

Hi!

[quoted text clipped - 15 lines]

empty.
What should I add more to the code?

 

[jubiiab via AccessMonster.com]

Help please!!!!

No it’s not too early. My users want to be notified when the form opens –
trust me! Please just help me with the code and question I have above. I know
you can solve it

Form_Open is too early.

[quoted text clipped - 14 lines]

 

[Benjamins via AccessMonster.com]

Hi,

You can try the isNull Function:

If IsNull([Services]) then
'Code that you want the system to do if the value is null
Else
If [Service] < Now() Then MsgBox "Service date overdue!", vbInformation,
"Service Date"
end if

Hi!

I have a form with a simple code:

Private Sub Form_Open(Cancel As Integer)

If [Service] < Now() Then
MsgBox "Service date overdue!", vbInformation, "Service Date"

End If
End Sub

The code above works fine if I just have one record in my form with a date.
If the service field is empty I get an error message because the form has a
null value in that field. I want the option to have that field to be empty.
What should I add more to the code?

 

[jubiiab via AccessMonster.com]

Hi

I tried the code below but its not working. I dont want any Msgbox if the
field is empty, only a Msgbox when its overdue. I get this error message when
i run the form:

Run-time error ‘2427’:
You entered an expression that hos no value

I think I have to Add some code where I wrote "Do nothing" - but what????


Private Sub Form_Open(Cancel As Integer)

If IsNull([Service]) Then
'Do nothing
Else
If [Service] < Now() Then
MsgBox "Service date Overdue!", vbInformation, "Service Date"

End If
End If
End Sub

Hi,

You can try the isNull Function:

If IsNull([Services]) then
'Code that you want the system to do if the value is null
Else
If [Service] < Now() Then MsgBox "Service date overdue!", vbInformation,
"Service Date"
end if

Hi!

[quoted text clipped - 12 lines]

null value in that field. I want the option to have that field to be empty.
What should I add more to the code?

 

[Allen Browne]

Try what was suggested.

 

[BruceM]

Allen's point is that there are no records loaded when the form's Open event
runs. If you are asking Access to evaluate a single record then it needs to
be "looking at" that record. If you do as Allen suggested and move the code
to the form's Current event it will run for every record. Same thing if you
use the highlighted text box.
If you try what was suggested rather than rejecting it out of hand I predict
you will be pleasantly surprised.

Help please!!!!

No it's not too early. My users want to be notified when the form opens -
trust me! Please just help me with the code and question I have above. I
know
you can solve it

Form_Open is too early.

[quoted text clipped - 14 lines]

empty.
What should I add more to the code?

 

[jubiiab via AccessMonster.com]

But its not evaluating just a single record! In my main form i have a sub
form where all service dates are showed. In this sub form I added the code
above. It runs as I want. If the date is overdue the massage are showed.

I just want to know what I should add in the code so its not executing that
code if there are no dates!

In the subform dates are turning red if they are overdue.

Allen's point is that there are no records loaded when the form's Open event
runs. If you are asking Access to evaluate a single record then it needs to
be "looking at" that record. If you do as Allen suggested and move the code
to the form's Current event it will run for every record. Same thing if you
use the highlighted text box.
If you try what was suggested rather than rejecting it out of hand I predict
you will be pleasantly surprised.

Help please!!!!

[quoted text clipped - 8 lines]

 

[BruceM]

That's some rather important information missing from your initial posting.
Which option did you use, the expression in the text box or the code in the
Current event? If the latter, and if the subform is Continuous, it will not
work as you like, but there are options that will work.

But its not evaluating just a single record! In my main form i have a sub
form where all service dates are showed. In this sub form I added the code
above. It runs as I want. If the date is overdue the massage are showed.

I just want to know what I should add in the code so its not executing
that
code if there are no dates!

In the subform dates are turning red if they are overdue.

Allen's point is that there are no records loaded when the form's Open
event
runs. If you are asking Access to evaluate a single record then it needs
to
be "looking at" that record. If you do as Allen suggested and move the
code
to the form's Current event it will run for every record. Same thing if
you
use the highlighted text box.
If you try what was suggested rather than rejecting it out of hand I
predict
you will be pleasantly surprised.

Help please!!!!

[quoted text clipped - 8 lines]

empty.
What should I add more to the code?