Run-Time error '94': Invalid Use of Null??????

[evilcowstare via AccessMonster.com]

Hi I have a record lock function in my form which works fine until I click
New Entry, which would normally start a new blank record. I keep getting the
error message...

Run-Time error '94':
Invalid use of Null

If I click Debug it highlights the line...

If recordlock Then

in yellow. This is the bit that is in the Form_Current area.

Does anyone know what I need to do to get rid of it?

The code looks like this but with 99% of all of the fields inserted into it
inc its autonumber.

Private Sub Form_Current()
operativebutton.Enabled = Not IsNull(operativecombo)
sitebutton.Enabled = Not IsNull(AddressCombo)

If recordlock Then
Me.jobnumber.Locked = True
Me.datereceived.Locked = True

Else

Me.jobnumber.Locked = False
Me.datereceived.Locked = False

End If

If Me.recordlock = True Then
Me.recordlabel.Visible = True
Else
Me.recordlabel.Visible = False
End If


End Sub


Can anybody help me sort this out? Its the last thing I have to fix and then
my whole db is complete ! )

Thank You to anybody that gives it a go!

 

[ruralguy via AccessMonster.com]

Sometime you refer to "it" as "recordlock" and sometime as "Me.recordlock".
I thought it was a function at first but now it may be a control. What is it?

 

[evilcowstare via AccessMonster.com]

Hi recordlock is the name of a checkbox

Sometime you refer to "it" as "recordlock" and sometime as "Me.recordlock".
I thought it was a function at first but now it may be a control. What is it?

Hi I have a record lock function in my form which works fine until I click
New Entry, which would normally start a new blank record. I keep getting the

[quoted text clipped - 41 lines]

Thank You to anybody that gives it a go!

 

[ruralguy via AccessMonster.com]

It would be a better practice to always refer to the control in a consistant
manner. My preference is to use the Me. predicate so we can tell is is
probably a control.

<...getting down from soapbox...>
Is your CheckBox bound to a field in a table? Does that field have a default
value? Which line does the debugger highlight?

Hi recordlock is the name of a checkbox

Sometime you refer to "it" as "recordlock" and sometime as "Me.recordlock".
I thought it was a function at first but now it may be a control. What is it?

[quoted text clipped - 4 lines]

 

[evilcowstare via AccessMonster.com]

The checkbox has a table field that has no default value and is set up as a
yes/no field
As written above .....


If I click Debug it highlights the line...

If recordlock Then

Thanks if you can help )

It would be a better practice to always refer to the control in a consistant
manner. My preference is to use the Me. predicate so we can tell is is
probably a control.

<...getting down from soapbox...>
Is your CheckBox bound to a field in a table? Does that field have a default
value? Which line does the debugger highlight?

Hi recordlock is the name of a checkbox

[quoted text clipped - 3 lines]

 

[ruralguy via AccessMonster.com]

Either give your field a default (True of False) or use the Nz() function
when you look at the field.

If Nz(Me.recordlock,0) Then

Which will default a Null to False for the test.

The checkbox has a table field that has no default value and is set up as a
yes/no field
As written above .....

If I click Debug it highlights the line...

If recordlock Then

Thanks if you can help )

It would be a better practice to always refer to the control in a consistant
manner. My preference is to use the Me. predicate so we can tell is is

[quoted text clipped - 9 lines]

 

[evilcowstare via AccessMonster.com]

Hi I tried giving it a default and it did nothing and then added on top your
Nz code and it works fine!
Thanks Very much for your help, it was really appreciated. Out of interest
what does Nz mean?
Thank You !!!!

Either give your field a default (True of False) or use the Nz() function
when you look at the field.

If Nz(Me.recordlock,0) Then

Which will default a Null to False for the test.

The checkbox has a table field that has no default value and is set up as a
yes/no field

[quoted text clipped - 11 lines]

 

[ruralguy via AccessMonster.com]

You can look it up in VBA Help. It is a function to deal with Nulls.

Hi I tried giving it a default and it did nothing and then added on top your
Nz code and it works fine!
Thanks Very much for your help, it was really appreciated. Out of interest
what does Nz mean?
Thank You !!!!

Either give your field a default (True of False) or use the Nz() function
when you look at the field.

[quoted text clipped - 8 lines]

 

[missinglinq via AccessMonster.com]

Here's a question: Why does evilcowstare need to add the Nz function to get
his code to work, when I run the identical code (I gave him the hack he's
using yesterday or the day before) and have no problem running it without the
Nz? I've run a number of permutations including adding a new record? Is not
the value of a yes/no checkbox 0 if it's not checked?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

I'm pretty sure that until it is checked the first time, the field is a Null.

 

[missinglinq via AccessMonster.com]

Nope! I don't think so!

Private Sub Form_Current()
If Me.NewRecord Then
If IsNull(Me.RecordLock) Then
Response = MsgBox("It's Null")
End If
End If
End Sub

doesn't throw up the messagebox! On the other hand

Private Sub Form_Current()
If Me.NewRecord Then
LockCondition = Me.RecordLock.Value
Response = MsgBox(LockCondition)
End If
End Sub

pops up a messagebox with Me.RecordLock's value of Zero!

 

[missinglinq via AccessMonster.com]

Evil,

"Hi I have a record lock function in my form which works fine until I click
New Entry, which would normally start a new blank record. I keep getting the
error message..."

What do you mean by New Entry? Is this a custom command button? If so, what
code do you have behind it?

 

[ruralguy via AccessMonster.com]

Do you have a default value for the field set at the table level?

 

[ruralguy via AccessMonster.com]

MissingLinq,
My testing indicates that a Yes/No field without a default is Null until the
record is Dirty. With a default of No the value is 0 without the record
being Dirty.

 

[evilcowstare via AccessMonster.com]

Hi everyone, it all works fine now but for the people that wanted the details.
..

The checkbox is in the table and now has a default value of Null on top of
the code suggested above with the Nz (if i rememeber).
When i was talking about New Entry I just used the wizard on a button to go
to "New Record". It isnt a customised button.
When I did have the problem I also got the same error message when I went to
the Latest record and clicked next, using navigation buttons made with the
wizard.

Not really sure what the EXACT problem was, all I know is with the table
field saying Null as default and the additional Nz code it works fine.

Thanks to everybody for helping !!!
All the best
Jay

MissingLinq,
My testing indicates that a Yes/No field without a default is Null until the
record is Dirty. With a default of No the value is 0 without the record
being Dirty.

Nope! I don't think so!

[quoted text clipped - 16 lines]

pops up a messagebox with Me.RecordLock's value of Zero!

 

[missinglinq via AccessMonster.com]

No, RuralGuy! I have no default set at table or form level, and as my code
shows it tests as being zero not null!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[missinglinq via AccessMonster.com]

"Not really sure what the EXACT problem was, all I know is with the table
field saying Null as default and the additional Nz code it works fine."

Curiouser and curiouser! If at table level the default is Null, and by using
the Nz function in the form code it works, then it should work if both of
these are removed, since Nz essentially neutralizes the Null default!

Of course, the main thing is that you go it to work, and sometimes yuou have
to just settle for that!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

What version of Access are you using? Here's the code I was using:

Private Sub Form_Current()

If IsNull([Testing]) Then
MsgBox "Testing is Null"
Else
MsgBox "Testing = [" & [Testing] & "]"
End If

End Sub

I just used the Nav buttons to change records. [Testing] is the Yes/No field
in the table.

 

[missinglinq via AccessMonster.com]

I'm using Access 2000. If you look closely, you'll see that your code simply
combines the the two pieces of code I posted. When I run yours, the
messagebox pops up stating that Testing = 0! As I said, no defaults are set
at any level.

Ain't Access just grand!

 

[evilcowstare via AccessMonster.com]

LOL if it helps im using Access 2002.
Seems to be just something stupid that Access is having a laugh with, Im just
happy the error is gone )
Maybe it was just going on strike over all the code and stuff I have been
making it work out lately, its always the very last thing that you need to do
when it finds fault. Oh well, its all done and complete now, thanks to
everyone that gave me help when I got stuck, especially you missinglinq, your
code saved me more then once )
Hope you all have a fab weekend
All the best
Jay