goldylamont
asked on
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?
thanks,
goldy
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
thanks so much,
goldy
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
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
cboRealtor.Requery
or perhaps reset rowsource
cboRealtor.Rowsource = cboRealtor.RowSource
cboRealtor.Requery
or perhaps reset rowsource
cboRealtor.Rowsource = cboRealtor.RowSource
ASKER
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
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
ASKER
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
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
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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°)
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°)
ASKER
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