working around the null value/variant type error

[lscheer]

I have read several posts on this forum about this problem, but none of the solutions presented so far help me.

I have a combo box w/ three columns in it (2 of which are invisible). When a user enters data in the combo box, the other two columns are used to update info in other fields. The combo box represents the company name a person works for. If the person quits working there, but wants to stay on our mailing list as an individual, they would no longer have data in the company field, so it would need to be deleted. Of course when it is deleted, we get this error:

You have tried to assign the null value to a variable that is not a variant data type.

I'm assuming because there is no event to address what to do with the other two columns of information?

So I figured, if i have a "blank" record in the combo box recordsource, users can select the blank record, but I have to figure out a way to idiot-proof it also. Basically, I figured, if I use something like the following code, I could set the combo box to the blank record value behind the scenes when necessary.
Note: "0" is the Id of the "blank" dummy record in my combo box

If Me.OrgName Is Null Then
Me.OrgName.Column(0) = "0"
Me.OrgName.Column(1) = ""
Me.OrgName.Column(2) = ""

Any ideas on how to make this work?

 

[Franknstuff]

This worked for me.

If IsNull([OrgName]) = True then

 

[lscheer]

This doesn't work...I'm still getting the "you have tried to assign the null value to a variable that is not a variant data type" error...

any other ideas?

 

[Franknstuff]

ok

Try Under form properties the On current button.

Check1.Visible = True


If IsNull([VendorID1]) = True Or [VendorID1] < 1 Then
Check1.Visible = False
End If

What this did for me was remove the button if nothing was there.

 

[Franknstuff]

Sorry

This is some other problem that I was working on. That was all I know.

 

[lscheer]

thanks, anyway!

 

[sparlaman]

might this work?

What I do for a similar situations to yours (if I'm understanding correctly) is add a yes/no field to the table that is running the combo box and then in my query I pull only the records with a no value so if one of the combo options needs to be removed it isn't totally removed from the records that contain that value, but it won't show in the list anymore. Leaving you without null values in any records. See my example below:

Table to store the combo list values (instead of a value list)
tblAnimals (fields & data)
AnimalID Animal Inactive? (yes/no field)
1 Cat not checked
2 Dog not checked
3 Fish not checked
4 Bird not checked
5 Hampster checked


Query to run the combo box (instead of a value list)
qryAnimals (fields & criteria)
AnimalID Animal Inactive?
NO

Hampster will no long be in the list but any old records containing hampster as the animal will not be disturbed and there will be no null values to contend with

Hope this helps.
Shel



_______________________________________________________


working around the null value/variant type error

--------------------------------------------------------------------------------

I have read several posts on this forum about this problem, but none of the solutions presented so far help me.

I have a combo box w/ three columns in it (2 of which are invisible). When a user enters data in the combo box, the other two columns are used to update info in other fields. The combo box represents the company name a person works for. If the person quits working there, but wants to stay on our mailing list as an individual, they would no longer have data in the company field, so it would need to be deleted. Of course when it is deleted, we get this error:

You have tried to assign the null value to a variable that is not a variant data type.

I'm assuming because there is no event to address what to do with the other two columns of information?

So I figured, if i have a "blank" record in the combo box recordsource, users can select the blank record, but I have to figure out a way to idiot-proof it also. Basically, I figured, if I use something like the following code, I could set the combo box to the blank record value behind the scenes when necessary.
Note: "0" is the Id of the "blank" dummy record in my combo box

If Me.OrgName Is Null Then
Me.OrgName.Column(0) = "0"
Me.OrgName.Column(1) = ""
Me.OrgName.Column(2) = ""

Any ideas on how to make this work?