Need to set a required field to null

[Alex]

FieldOne and FieldTwo are controls on my form that have a required data field
in my table. A user types in a value in FieldOne, then selects a value in
FieldTwo from a combo box. If the user deletes/changes what he's typed in
FieldOne, I need FieldTwo set to null so that the user knows he has to pick
another choice in FieldTwo. Her is my code:

Private Sub FieldOne__Change()

Me.FieldTwo = ""
Me.FieldThree = ""

End Sub

I kept getting an error msg. that I couldn't set the required FieldOne to
null. To fix that, in the table I set the Allow Zero Length to Yes. Now, a
user is able to close the form without a value in FieldTwo. Is there a way I
can set FieldTwo to null if the value in FieldOne is changed without getting
the error message and still require FieldTwo before closing? Thanks.

 

[Scott Lichtenberg]

A simple way would be to put some code in the BeforeUpdate event of your
form to check whether the fields have valid data. If not, throw out a
message box and set Cancel=True. This will stop the record from being
saved.

 

[Alex]

I need the fields set to required in the table or users might change fields
directly in the table. Any other ideas?

 

[Scott Lichtenberg]

You do have a problem. I don't know any way to allow a null value into the
field if you have it defined as required. However, there is a way around
this. If your form is a single record form, rather than a continuous form,
you can try the following:

Set the visible property of Field2 to false. Create an unbound combo box on
your form.

In the OnCurrent event, set the value of the unbound combo box to the value
in the field. This way, the combo box will show whatever value the field
has when you move into a record. Since the combo box is unbound, you can
set its value to null when your Field1 changes.

In the BeforeUpdate event of the form, check that your combo box is not
null, then set your field to whatever is in the combo box.

One thing you will have to watch out for is that changing the combo box will
not make the form dirty and will not trigger the before update event. If
your user changes Field1, you shouldn't have any problem, however, if he
changes only the combo box, you won't update. You can get around this by
setting the value of an existing field to itself (Me!Field1 = Me!Field1).

Hope this helps

 

[Alex]

Thanks Scott. I unrequired the fields in the table, set the Before_Udate in
the form and hid the table so people won't change it. Thanks for your help.