Trap Combo-Box error

[Guest]

I have a form with several combo-Box controls with VB error traps. If an
option is selected and then you “backspace†over the selection and then try
to move to a new field, the following error message appears.

“You tried to assign the Null value to a variable that is not a Variant data
type.â€

When you click help, the help provided is unhelpful but it does refer to
error “(Error 3162)â€. I need to trap this error and provide a more
user-friendly message. This is learly a system error message ( I am thinking
DOA ). How do I Trap the error?

 

[Ken Snell [MVP]]

Try using the BeforeUpdate event of the combo box and test to see if the
value in the combo box is Null or empty:

Private Sub ComboBox_BeforeUpdate(Cancel As Integer)
If Len(Me.ComboBox.Value & "") = 0 Then
MsgBox "You cannot leave an empty value in the combo box. Select
something!"
Cancel = True
Me.ComboBox.Undo
End If
End Sub

 

[Guest]

I tried this and it does not work. It also does not work in the _Exit event.
The meesage box pop-up apparently before the events are trigered. I also
can not trap the the error in the Form_Error event.

Again, this happens when you "backspace" while in a combo-box (creating a
null) and then leave the combo box. The message tittle says "Microsoft
Access" and the message says "You tried to assign the Null value to a
variable that is not a Variant data Type".
When you click the help cmd button, a help screen pops up with this
message "You tried to assign the Null value to a variable that is not a
Variant data type. (Error 3162)
You tried to assign a Null value to a variable that is not a Variant data
type. Use the Dim statement to declare the variable as a Variant, and then
try the operation again."

Is error 3162 a DOA error. If so, how do I trap it.

 

[Ken Snell [MVP]]

Is the combo box bound to a field in the form's record source? What type of
field (data type) if yes?

Do you have code running on the combo box's Change event? If yes, what is
it?
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

First, THANKS SO MUCH for taking the time to help!!!

The combo box is bound to the form’s record source and there is not any
“change-event code. Here is some additional info. The record source is a
query and the combo-box’s control source is a “look-up†field in one of the
tables within the query. The look-up field is a multi-column query that
“hides†the actual value so that you see test and not the key value.

When I run the query in “Datasheet view†and change the value of the control
source (backspace) for a record, I get the same message I get when in the
form. When I do this in the actual table, I do not get an error message.
The field in the table has the “limit to list†property set to YES, the
“required property†set to NO and the “Validation Rule property†is not set.

The Form_Error event does not trip so this must be a “JET†error but there
has to be a way to trap it. By the way, I am running Access-2003 (all
patches installed).

 

[Ken Snell [MVP]]

A combo box cannot be bound to a form's recordsource...it can only be bound
to a field that is in that recordsource. It appears that the combo box is
bound to a field in the query that is the form's recordsource.

Lookup fields are not highly recommended by us MVPs because they can mislead
you about what is actually in the field. In your case, the lookup field is
working ok with your combo box.

What type of field is the field that is bound to the combo box? Tell us more
info about the table and this field. Also, post the query's SQL statement
that you're using as the form's Recordsource.
--

Ken Snell
<MS ACCESS MVP>