"You tried to assign the Null value to a variable that is not a...

[Guest]

I have a form that is based on a table "Order Entry". I included a checkbox
with a conditional control source by creating a query in the form's record
source to include this field (chkOp120) from another table (Process Codes).
On the form I called the checkbox (ckTensileTest). I included the following
code in the form's On Current event:

If Me.chkOp120 = -1 Then
Me.ckTensileTest = Me.chkOp120
Else
Me.ckTensileTest = Me.[Tensile Test]
End If

The purpose is so that this check box will be checked automatically if the
corresponding field in the table Process Codes is checked, but will give the
user the option of checking the box if a tensile test is specifically
requested by the customer. The Process Codes table is related to the Order
Entry table by a field on the form called (ID_Process_Key). If, by chance,
the user leaves this field blank, or deletes the information in it, the above
error results. I know why this is happening, but the user does not, so I'm
trying to trap the error and let the user know that the ID_Process_Key field
cannot be left blank in order to preserve the relationship in the control
source. What event will catch this before the system error message appears?
Am I on the right track with this?

 

[Guest]

Please help!

 

[John W. Vinson]

I have a form that is based on a table "Order Entry". I included a checkbox
with a conditional control source by creating a query in the form's record
source to include this field (chkOp120) from another table (Process Codes).
On the form I called the checkbox (ckTensileTest). I included the following
code in the form's On Current event:

If Me.chkOp120 = -1 Then
Me.ckTensileTest = Me.chkOp120
Else
Me.ckTensileTest = Me.[Tensile Test]
End If

The purpose is so that this check box will be checked automatically if the
corresponding field in the table Process Codes is checked, but will give the
user the option of checking the box if a tensile test is specifically
requested by the customer. The Process Codes table is related to the Order
Entry table by a field on the form called (ID_Process_Key). If, by chance,
the user leaves this field blank, or deletes the information in it, the above
error results. I know why this is happening, but the user does not, so I'm
trying to trap the error and let the user know that the ID_Process_Key field
cannot be left blank in order to preserve the relationship in the control
source. What event will catch this before the system error message appears?
Am I on the right track with this?

A checkbox control cannot assume a NULL value - my only guess is that in some
cases [Tensile Test] is NULL, causing this error.

Try

Me.ckTensileTest = NZ(Me.[Tensile Test], 0)

John W. Vinson [MVP]

 

[Guest]

Actually, both [Tensile Test] and [chkOp120] are Yes/No fields, but from two
different tables(Order Entry and ProcessCodes). I'm trying to let the form
know which of them to use as the controlsource for the checkbox
[ckTensileTest] on the form. My code works acceptably well, but causes
problems when the text box [ID_Process_Key] is left blank, because it's
necessary for the relationship between the two tables to exist. I think I
understand the problem... Now I need to think of a way in which I can
salvage the functionality of the form for data entry while also preserving
the conditional record source for [ckTensileTest]. I'm toying with the idea
of adding a record to the ProcessCodes table that will indicate invalid data,
setting this as the default value for this field in the table, and using its
index value as a trigger for an error message that reminds the user that this
value must be changed before the record can be considered valid. I think
that by doing this, it will maintain the relationship between Order Entry and
ProcessCodes and not cause the error. It may also be useful to validate the
data in [ID_Process_Key]. I think that could work, but I'm not sure which
event to use to trigger the error message.
--
Why are you asking me? I dont know what Im doing!

Jaybird

I have a form that is based on a table "Order Entry". I included a checkbox
with a conditional control source by creating a query in the form's record
source to include this field (chkOp120) from another table (Process Codes).
On the form I called the checkbox (ckTensileTest). I included the following
code in the form's On Current event:

If Me.chkOp120 = -1 Then
Me.ckTensileTest = Me.chkOp120
Else
Me.ckTensileTest = Me.[Tensile Test]
End If

The purpose is so that this check box will be checked automatically if the
corresponding field in the table Process Codes is checked, but will give the
user the option of checking the box if a tensile test is specifically
requested by the customer. The Process Codes table is related to the Order
Entry table by a field on the form called (ID_Process_Key). If, by chance,
the user leaves this field blank, or deletes the information in it, the above
error results. I know why this is happening, but the user does not, so I'm
trying to trap the error and let the user know that the ID_Process_Key field
cannot be left blank in order to preserve the relationship in the control
source. What event will catch this before the system error message appears?
Am I on the right track with this?

A checkbox control cannot assume a NULL value - my only guess is that in some
cases [Tensile Test] is NULL, causing this error.

Try

Me.ckTensileTest = NZ(Me.[Tensile Test], 0)

John W. Vinson [MVP]