Enforce Combo Box Selection

[Kirk P.]

I've got this code in the RowSource property of a combo box called cboLocale.
It restricts the choices in Locale based on the users selection in
cboSite_ID.

SELECT DISTINCT LOCALE, CLIENT_ID
FROM tblSales_MTD
WHERE
(((CLIENT_ID)=[Forms]![frmADM_Assign]![sfrmADM_Assign].[Form]![cboSite_ID]));

The problem is, in the On Exit event of cboLocale, I have this code:

If IsNull(Me.cboLocale) Then
Cancel = True
With Me![cboLocale]
.Requery
.SetFocus
.Dropdown
End With
MsgBox "You must select a locale!", vbCritical, "Error Notice"
End If

In many cases, the value in Locale is appropriate when it is a zero length
string, and the combo box in Locale will correctly display a "blank" when
that is the appropriate choice. I have verified the fact that the select
query serving as the RowSource for Locale does not return Nulls, but it
appears the code in the Exit event still interprets the zero length strings
as Nulls, thus firing the error and not even allowing me to enter a space or
anything.

 

[ruralguy via AccessMonster.com]

Does your ComboBox have a ControlSource?

I've got this code in the RowSource property of a combo box called cboLocale.
It restricts the choices in Locale based on the users selection in
cboSite_ID.

SELECT DISTINCT LOCALE, CLIENT_ID
FROM tblSales_MTD
WHERE
(((CLIENT_ID)=[Forms]![frmADM_Assign]![sfrmADM_Assign].[Form]![cboSite_ID]));

The problem is, in the On Exit event of cboLocale, I have this code:

If IsNull(Me.cboLocale) Then
Cancel = True
With Me![cboLocale]
.Requery
.SetFocus
.Dropdown
End With
MsgBox "You must select a locale!", vbCritical, "Error Notice"
End If

In many cases, the value in Locale is appropriate when it is a zero length
string, and the combo box in Locale will correctly display a "blank" when
that is the appropriate choice. I have verified the fact that the select
query serving as the RowSource for Locale does not return Nulls, but it
appears the code in the Exit event still interprets the zero length strings
as Nulls, thus firing the error and not even allowing me to enter a space or
anything.

 

[Kirk P.]

Yes, the control source for cboLocale is Locale.

Does your ComboBox have a ControlSource?

I've got this code in the RowSource property of a combo box called cboLocale.
It restricts the choices in Locale based on the users selection in
cboSite_ID.

SELECT DISTINCT LOCALE, CLIENT_ID
FROM tblSales_MTD
WHERE
(((CLIENT_ID)=[Forms]![frmADM_Assign]![sfrmADM_Assign].[Form]![cboSite_ID]));

The problem is, in the On Exit event of cboLocale, I have this code:

If IsNull(Me.cboLocale) Then
Cancel = True
With Me![cboLocale]
.Requery
.SetFocus
.Dropdown
End With
MsgBox "You must select a locale!", vbCritical, "Error Notice"
End If

In many cases, the value in Locale is appropriate when it is a zero length
string, and the combo box in Locale will correctly display a "blank" when
that is the appropriate choice. I have verified the fact that the select
query serving as the RowSource for Locale does not return Nulls, but it
appears the code in the Exit event still interprets the zero length strings
as Nulls, thus firing the error and not even allowing me to enter a space or
anything.

 

[ruralguy via AccessMonster.com]

Does that field allow Nulls?

Yes, the control source for cboLocale is Locale.

Does your ComboBox have a ControlSource?

[quoted text clipped - 26 lines]

 

[Kirk P.]

The required property is set to No. The Allow Zero Length property is set to
Yes. I never want to introduce a Null in the locale field, but a ZLS is fine.

Does that field allow Nulls?

Yes, the control source for cboLocale is Locale.

Does your ComboBox have a ControlSource?

[quoted text clipped - 26 lines]

as Nulls, thus firing the error and not even allowing me to enter a space or
anything.

 

[ruralguy via AccessMonster.com]

I assume that it is a Text field. Have you tried giving it a Default Value
of ""?

The required property is set to No. The Allow Zero Length property is set to
Yes. I never want to introduce a Null in the locale field, but a ZLS is fine.

Does that field allow Nulls?

[quoted text clipped - 5 lines]

 

[Kirk P.]

It is a text field. I have tried setting the default value to "", but the
error message still fires when I exit the locale combo box. The combo box
proposes the "blank" as an option when appropriate for the Site ID selected,
but selecting the "blank" is still interpreted as a null and kicks off the
error message.

I assume that it is a Text field. Have you tried giving it a Default Value
of ""?

The required property is set to No. The Allow Zero Length property is set to
Yes. I never want to introduce a Null in the locale field, but a ZLS is fine.

Does that field allow Nulls?

[quoted text clipped - 5 lines]

as Nulls, thus firing the error and not even allowing me to enter a space or
anything.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Well, there are conditions where a Null in the field is valid and you are not
testing for them (it?). It appears that the code here can not differentiate
between a Null and a ZeroLengthString (ZLS) so how about testing to see if a
Null is ok before actually checking for the Null?

It is a text field. I have tried setting the default value to "", but the
error message still fires when I exit the locale combo box. The combo box
proposes the "blank" as an option when appropriate for the Site ID selected,
but selecting the "blank" is still interpreted as a null and kicks off the
error message.

I assume that it is a Text field. Have you tried giving it a Default Value
of ""?

[quoted text clipped - 7 lines]