error 3162 - Null value to non-variant type (combo box)

Hi, I've seen this posted several times but still don't understand how to resolve this issue in my case.

I have a combo box on a form,  cboRealtors. This combo box shows a list of realtors that it gets from a simple query on the tblRealtor table. The combo box is bound to a column in the form recordset called ID_Realtor.

Initially, the combo box is empty. Then, you can select a realtor from the drop-down list and everything works fine. Problem is,  if you want to remove any selections from the combo box to leave it blank again, you get this error:

error 3162 - "You tried to assign the null value to a variable that isn't a variant data type."

in the underlying table,  Required=Yes. There is no default value, however looking at the table, all of the unnassigned ID_Realtor fields have a 0 in them.

I don't think adding any code to the AfterUpdate or OnExit procedures for the combo box will work--this error gets thrown before either of those are executed.

Also, I read somewhere that Access may be confused about which variable to assign the value to? I'm confused about this as there's no controls on the form with the same name ID_Realtor and I never defined any other variables in my code with a Dim statement with that name. The only place that name exists is the field name in the underlying table.

Anyone know how I can delete a realtor and leave the combo box empty after selecting one without getting this error?

Who is Participating?
harfangConnect With a Mentor Commented:
Hello goldylamont

Are you certain that the combo box is in fact empty? From your explanations, unselected ID_Realtor fields are not blank, but contain zeros instead. This is the first problem, because that is an invalid ID. Since it does not exist, the combo has no "translation" for the value 0 and shows an empty string (or a null value) in the combo.

To debug this, start by unhiding the first column. Find the property "Columns Widths" and delete its content. You will then really see what is or isn't in the combo.

To clean-up your table, you will need several things:
 • Remove the Required property from the field
 • If it has a validation rule, allow explicitely for Null, e.g. >0 Or Null
 • Run an update query to replace all the 0's with Null
 • Create a true relationship between your tables, with enforce relational integrity (well, it would be better to have that, but it's not essential)
 • Make sure you can manually in the table write an ID and delete it.

What you want is the default behavior. We need to find what you changed to make it work otherwise.


rockiroadsConnect With a Mentor Commented:
So you are trying to delete from the combobox?

That field on the table - does it allow for nulls? does it say its a mandatory field?
also u store just the ID? dont u store the description? I take it you are trying to delete from tblRealtor?

An alternative is perhaps to create a button called cmdDel labelled Del Realtor

then this code will run some sql that deletes the selected Realtor, then what u do is requery the combobox to refresh the list
goldylamontAuthor Commented:
here's an example of my situation:

tblMain (used by the form)


cboRealtor -- this combo box shows all of the realtors from tblRealtor and lets you select one of them. it then stores the ID_Realtor value into tblMain, so that the Main record now has an association to a realtor.

<<So you are trying to delete from the combobox?>>
Let's say I select a realtor in the combo box. Fine. Then let's say I don't want that realtor selected anymore. I want the combo box blank just like it was initially. That's what I'm trying to do.

<<That field on the table - does it allow for nulls? does it say its a mandatory field?>>
It is not mandatory, but it also does not allow for Nulls. I'd prefer to leave it this way as it is a Foreign Key.

<<also u store just the ID? dont u store the description? I take it you are trying to delete from tblRealtor?>>
I am only storing the ID_Realtor in tblMain. tblRealtor has the descriptions if I need them. No, I am not trying to delete a realtor from tblRealtor--I only want to delete the realtor association in the combo box on the form, so the the record in tblMain isn't associated with a realtor.

thanks for the response. hope this makes things clearer.


7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

goldylamontAuthor Commented:
Update: I just went into SQL Server and made it so that the ID_Realtor field could accept Null values, and I still get the same error. So, the issue doesn't occur when trying to update the field in the table. The error is being thrown before any table updates and before the AfterUpdate or OnExit events. what gives?

thanks so much,
Not sure if its because its in a combobox.

Have u tried the alternative is using a button?

private sub cmdDel_Click

   dim sSql as String
   sSql = "DELETE FROM tblRealtor WHERE ID_Realtor = " & cboRealtor.Column(0)

   DoCmd.RunSQL sSql

end sub
ok, forgot the requery


or perhaps reset rowsource

cboRealtor.Rowsource = cboRealtor.RowSource
goldylamontAuthor Commented:
I don't want to delete a Realtor from the Realtor table. All I want to do is have nothing in the Combo box. Ok, let's say I start a new record on the form. Then I go to the cboRealtor combo box and select "Jon Doe" as a realtor for this record. Then, I go...hold up, I don't really want Jon Doe as a realtor. I want to have the combo box be blank again, just like it was before I selected Jon Doe. So, I don't want Jon Doe's name in the cboRealtor box anymore--but "Jon Doe" is STILL in the list of Realtors. I'm not deleting Jon Doe as a realtor. All I want to do is clear the combo box. How do you clear the value of the combo box without getting this error?
sorry for misunderstanding

why dont u try this then

cboRealtor.Value = ""

this u can add when u go to a new record for example

Now what u say, I created a combo, which has a rowsource

I just cleared the value and that seemed to work

but mine is on Access, yours is based on SQL Server

It might be worth playing with the settings for the combo
Quite difficult to say when u can't reproduce it
goldylamontAuthor Commented:
thanks for trying rockiroads...but where would I add this code? I've already added code like this to my AfterUpdate or OnExit trigger events for the combo box but it never gets executed because the error is thrown BEFORE any of these triggers occur.

from before:
"I don't think adding any code to the AfterUpdate or OnExit procedures for the combo box will work--this error gets thrown before either of those are executed."

<<but mine is on Access, yours is based on SQL Server>>
true, but it makes no difference in this case. i get the error before any updates are made to any records in Access or Sql Server. no triggers are thrown. it's not the table saying that it can't accept a null value. the problem occurs on the form itself before any events on the control are triggered (that i know of).

kamal, as a test, try this

create a button called cmdClear and on its click event, clear it e.g.

private sub cmdClear_Click
    cboRealtor.Value = ""
end sub

does this work?
goldylamontAuthor Commented:
aaaaargh! i get the Exact same error! i add the button and get the same error.

nice try though. this combo box does NOT want to be empty for some silly reason. but initially, it IS empty which is so confusing.
Kelvin SparksConnect With a Mentor Commented:
In your original post, you said the underlying table had "Required=Yes" - you cannot delete from a table with that setting. Also, if your tables are in SQL Server, check there's no trigger on the table to prevent NULL values in that field. They're often missed, especially if table has been upsized from Access and set to use triggers
goldylamontAuthor Commented:
thanks gang. it actually was the fact that the field did not allow nulls that was the issue. What i still don't understand is why NONE of the Access form triggers fired before trying to write the value to the tables. that is what was confusing to me, because I did all kind of "error trapping" to prevent the code from writing any NULLS to the table but that code could never get executed because the error happened before any of the trigger procedures that it was in.

is there any way around this? is this a common problem? what if I don't want the field to accept Nulls and I want to use error trapping to prevent an error? How could this be done.


To trap (almost) all errors that can happen when you use a form, you need the Form_Error event. You could theoretically monitor all user input using the control's Change event, and replace the text by a default value as soon at it is empty (so the field cannot be cleared), but this seems odd.

When the error event is raised, you can examine not only the error number, but also the active control (Me.ActiveControl) and take appropriate action. You can ignore other errors to get the default message as well.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.