Avoid Null value in ComboBox - MS Access 2000

[James P.]

Help,

In Access 2000, I have bound column - comboBox called cboPriority. In
design, I put in three values: High, Low, Medium. The default value
is Medium. When I run the form, it shows the default value in the
comboBox, and those values in the list, without any problem.

The problem is if I highlight the value in the comboBox, spacebar (to
erase the default value "Medium"), and hit Tab (to get out of the
comboBox), I got an error message saying: "You tried to assign the
Null value to a variable that is not a Variant data type".

I would like to display an appropriate message before this error
message comes out. I think the most appropriate place to put my
msgbox is in the Change event so I check for Null value here like:

Private sub cboPriority_Change
If IsNull(cboPriority) or cboPriority = "" then
msgbox ""Please select a value from the list"
exit sub
End If
End Sub

However, if I put a break in the code here, do the action above:
highlight, spacebar, Tab: I saw cboPriority still shows the default
value "Medium" so it never executes my message box code.

I also tried to capture the error message before it comes out with my
own message box in Lost Focus, Exit, Before Update and After Update
events of this comboBox but so far this error message keeps coming out
first.

Any help and suggestions are appreciated.
James

 

[Wayne Morgan]

"You tried to assign the Null value to a variable that is not a Variant

data type".<

This gives the impression that you are trying to assign the value of the
combo box to a variable in code. If so, do you know where this is being
done?

In the BeforeUpdate of the combo box try something like:

If Nz(cboPriority,"") = "" Then
msgbox ""Please select a value from the list."
Cancel = True
End If

 

[James P.]

This gives the impression that you are trying to assign the value of the
combo box to a variable in code. If so, do you know where this is being
done?

In the BeforeUpdate of the combo box try something like:

If Nz(cboPriority,"") = "" Then
msgbox ""Please select a value from the list."
Cancel = True
End If

Wayne,
Thanks for responding. I have no problem with assigning value to the
combo box. In fact, it's been done. All I want is make the user
select a value from the list, and won't allow them to enter junk nor
empty value.

 

[Wayne Morgan]

James,

In reference to the error message, I was referring to assigning a value the
other direction, not TO the combo box but FROM the combo box to a variable.

To limit the selection to the list, set the combo box's Limit To List
property to Yes. This won't prevent them from clearing the combo box
(setting it to Null), though. However, the BeforeUpdate code I posted in the
previous message should take care of that. If you want to prevent them from
not doing anything to the combo box when creating a new record (i.e. leaving
it Null when creating a new record), you could set the fields Required
property in the table or use the form's BeforeUpdate event to check the
value of the combo box to make sure it's not Null.

 

[James P.]

James,

In reference to the error message, I was referring to assigning a value the
other direction, not TO the combo box but FROM the combo box to a variable.

To limit the selection to the list, set the combo box's Limit To List
property to Yes. This won't prevent them from clearing the combo box
(setting it to Null), though. However, the BeforeUpdate code I posted in the
previous message should take care of that. If you want to prevent them from
not doing anything to the combo box when creating a new record (i.e. leaving
it Null when creating a new record), you could set the fields Required
property in the table or use the form's BeforeUpdate event to check the
value of the combo box to make sure it's not Null.

Wayne,

Again, thank you for responding. In responding to your suggestion, I
do have Limit To List = Yes. This error message fires before the
BeforeUpdate event fired so nothing I can do yet. Finally, change it
to a Required field could not help me with this error message either.

I finally found a way to do it from Google: capture the error in
Form_Error:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Const EmptyValueErr = 3162
Dim stfMsg As String

If DataErr = EmptyValueErr Then
Response = acDataErrContinue
stfMsg = "Please select a value from the list."
MsgBox stfMsg, vbInformation
End If
End Sub

It worked for me. Thanks, Wayne.