You tried to assign the Null value to a variable that is not a Variant Data Type

[Lance]

Hello,

I am hoping to get some help on trapping this error, or finding a
workaround to avoid it.

I am getting the error "You tried to assign the Null value to a
variable that is not a Variant Data Type" when working in a subform
with the following setup.

I have a main form and a subform. The mainform pulls values from a
table called tblPrograms, of which the linking value is ProID. The
subform's job is to assign clients to the program (a one to many
relationship between clients and programs). ClientID is a primary key,
with no duplicates allowed on the form.

In the subform, the user is presented with only one combo box asking
them to select a user to add to the program. However, if a user selects
a name, and then decides they don't want to add that name, and deletes
the value from the combo box, the above error occurs. Obviously, they
have created a record, deleted the value, and have now left a null
value in a key field. My goal is to trap this problem before it
happens, but I am not sure how to approach it.

I have thought to put code in the "before update" of the combo box, but
that code does not trigger prior to the error generated automatically
by access.

Any ideas are greatly appreciated.

 

[Guest]

That is not what is happening. Look at the code in the After Update event of
the combo box. You will probably find a line similar to:

strSomething = DLookup("[SomeField]", "Some Table", "[SomeField] = '" &
Me.MyCombo Name & "'")

What the error message is tellin you is a piece of VBA code is trying to put
a Null value into a variable that is not a Variant type.

Find the offending line of code, or post the code here and we'll have a
look. Then there are a couple of way you can fix it. You can either change
the Dim of the variable to Variant, but then you will have to check it to see
if it is Null and take the appropriate action or you can use the Nz function
so that a Null value does not get into the variable. It will look like this:

strSomething = Nz(DLookup("[SomeField]", "Some Table", "[SomeField] = '" &
Me.MyCombo Name & "'"),"")

Now rather than an error, strSomething will be a zero length string. You
will then have to determine what to do when that happens.

 

[Baz]

Hello,

I am hoping to get some help on trapping this error, or finding a
workaround to avoid it.

I am getting the error "You tried to assign the Null value to a
variable that is not a Variant Data Type" when working in a subform
with the following setup.

I have a main form and a subform. The mainform pulls values from a
table called tblPrograms, of which the linking value is ProID. The
subform's job is to assign clients to the program (a one to many
relationship between clients and programs). ClientID is a primary key,
with no duplicates allowed on the form.

In the subform, the user is presented with only one combo box asking
them to select a user to add to the program. However, if a user selects
a name, and then decides they don't want to add that name, and deletes
the value from the combo box, the above error occurs. Obviously, they
have created a record, deleted the value, and have now left a null
value in a key field. My goal is to trap this problem before it
happens, but I am not sure how to approach it.

I have thought to put code in the "before update" of the combo box, but
that code does not trigger prior to the error generated automatically
by access.

Any ideas are greatly appreciated.

Try trapping the error in the Form's Error event.