Conditional "Required"

[Ian]

I want to set a validation rule or "required" setting for
a date field in my table based on another field in the
same table. Specifically I have a check box and want to
force the user to enter a date if the check box is yes -

I'm not sure how to do this - also in my forms the two
(check box and date field) are side by each so if they
turn on the check box first it will violate the
validation rule before they get the chance to enter the
date field. Can someone guide me please.

Thanks.

 

[John Vinson]

I want to set a validation rule or "required" setting for
a date field in my table based on another field in the
same table. Specifically I have a check box and want to
force the user to enter a date if the check box is yes -

I'm not sure how to do this - also in my forms the two
(check box and date field) are side by each so if they
turn on the check box first it will violate the
validation rule before they get the chance to enter the
date field. Can someone guide me please.

Thanks.

Put the code to check the validation in the Form's BeforeUpdate event
(which is cancellable). For example:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me!chkTheCheckbox = True And IsNull(Me!txtDatefield) Then
MsgBox "Please fill in the date", vbOKOnly
Cancel = True
Me!txtDatefield.SetFocus
End If
End Sub

In a Table you can set a Table Validation rule but the error messages
can be rather user-hostile.

John W. Vinson[MVP]

 

[Allen Browne]

Use the Validation Rule for the *table*, instead of the Required property of
the field.

1. Open the table in design view.

2. Open the Properties box (View menu).

3. Beside the Validation Rule in the Properties box, enter something like
this:
([MyYesNo] = False) OR ([MyDate] Is Not Null)
using your actual date field names inside the square brackets.

The rule is satisified if the yes/no field is unchecked. If not, the only
way it can be satisfied is if the date field is not null.

Because the rule is applied at the record level, the user can make the entry
in whatever order they wish: it is only when the record is about to be saved
that the rule is applied.

 

[Guest]

Ended up using John's code - a little more complicated
but more user friendly. Still only took 5 seconds to
change - Thanks Again.

 

[Ian]

Another quick question - is it possible to add code to
the end of this to force the main form to refresh when
the line updates? (the area I put the code into is part
of a sub-sub form - I assume I'll have to change the
focus to the main form)

 

[John Vinson]

Another quick question - is it possible to add code to
the end of this to force the main form to refresh when
the line updates? (the area I put the code into is part
of a sub-sub form - I assume I'll have to change the
focus to the main form)

Yes but... why should anything on the mainform be changing if you're
entering data on a sub-subform?

John W. Vinson[MVP]