How to clear ComboBx value without making it NULL?

[mezzanine1974]

There are two ComboBox in a form. (ComboBx1 and ComboBox2)
ComboBox2 is providing data for a table field which is required a
value rather than NULL. Source of the ComboBx2 is coming from a query
for which ComboBx1 is a criteria. So, when you select a value in
ComboBx1, source of the ComboBx2 is changing accordingly.

For OnChange() and AfterExit() events of ComboBx1, I write these
simple codes to update the ComboBx2 automatically:

Form_Form1.ComboBx2.Value=Null
Form_Form1.ComboBx2.Requery

Since ComboBx2.Value can not be NULL because it is required a value,
first line of the code cause en error. How can I clear the ComboBx2,
while ComboBx1 is onChange() or AfterExit().

Thanks

 

[OldPro]

There are two ComboBox in a form. (ComboBx1 and ComboBox2)
ComboBox2 is providing data for a table field which is required a
value rather than NULL. Source of the ComboBx2 is coming from a query
for which ComboBx1 is a criteria. So, when you select a value in
ComboBx1, source of the ComboBx2 is changing accordingly.

For OnChange() and AfterExit() events of ComboBx1, I write these
simple codes to update the ComboBx2 automatically:

Form_Form1.ComboBx2.Value=Null
Form_Form1.ComboBx2.Requery

Since ComboBx2.Value can not be NULL because it is required a value,
first line of the code cause en error. How can I clear the ComboBx2,
while ComboBx1 is onChange() or AfterExit().

Thanks

One solution is to detach your form from the datasource, and handle
the data input manually. It is easy to check for NULL while saving a
record with the .edit and .update properties. Also, NZ( ) can be used
to force a default when a value is NULL.

 

[Allen Browne]

You have conflicting requirements: you want to make the value Null, and have
it as a required field at the same time. That's a contradiction.

One workaround might be to open the table in design view, and set the
field's Required property to No. Still in table design view, open the
Properties box, and set the Validation Rule to:
[Field1] Is Not Null
substituting your field name for Field1. At the field-level, the field is no
longer required (so you can assign Null to it), but at the record level, it
is still impossible to save the record if the field is null.

Be sure to use the table's validation rule (the one in the Properties box),
not that of the field (in the lower pane of table design.)

 

[mezzanine1974]

Hello Allen,

Thanks for your suggestion. First it sounds good, but when i try it,
the result is same.
What OldPro is saying above is quite reasonable. I will try to type
codes.

Thanks