Solved

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

Posted on 2006-06-19
14
961 Views
Last Modified: 2011-10-03
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?

thanks,
goldy
0
Comment
Question by:goldylamont
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
Comment Utility
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
0
 

Author Comment

by:goldylamont
Comment Utility
here's an example of my situation:

tblMain (used by the form)
ID_Main
ID_FirstName
......
ID_Realtor


tblRealtor
ID_Realtor
ID_RealtorName
....

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.

goldy

0
 

Author Comment

by:goldylamont
Comment Utility
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,
goldy
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, forgot the requery

cboRealtor.Requery

or perhaps reset rowsource

cboRealtor.Rowsource = cboRealtor.RowSource
0
 

Author Comment

by:goldylamont
Comment Utility
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?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:goldylamont
Comment Utility
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).

thanks,
kamal
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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?
0
 

Author Comment

by:goldylamont
Comment Utility
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.
0
 
LVL 58

Accepted Solution

by:
harfang earned 250 total points
Comment Utility
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.

Cheers!

(°v°)
0
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 150 total points
Comment Utility
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
0
 

Author Comment

by:goldylamont
Comment Utility
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.

thanks,
goldy
0
 
LVL 58

Expert Comment

by:harfang
Comment Utility
goldylamont

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.

(°v°)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now