Set Field Property to Required

  • Thread starter Thread starter Jennifer
  • Start date Start date
J

Jennifer

I need to be able to set a fields required property to
true only if another fields value is true, else I it needs
to remain false. How do I do this?

Thanks in advance!
 
This example shows how to set a validation rule on the table so that the
field named "ObsoleteDate" must have a value if the yes/no field
"IsObsolete" is true.

1. Open your table in design view.

2. Open the Properties box (View menu).

3. Beside the Validation Rule property, enter something like this:
(IsObsolete = False) OR (ObsoleteDate Is Not Null)

The rule is satisifed if IsObsolete is not checked.
If IsObsolete is checked, the rule can be satisfied only if ObsoleteDate has
a value.

Note that the Validation Rule in the Properties box applies to the table. It
is different from the Validation Rule in the lower pane of the table design
window: that one applies to a field.
 
This example shows how to set a validation rule on the table so that the
field named "ObsoleteDate" must have a value if the yes/no field
"IsObsolete" is true.
Click to expand...
Surely this puts the table out of Second Normal form, since the IsObsolete
column is functionally dependent on the ObsoleteDate. To put it another
way, the value can be recreated in a query by the simple

SELECT (ObsoleteDate IS NOT NULL) AS IsObsolete
FROM etc



B Wishes


Tim F
 
Yes, as TC also observed, it is non-normalized.

However (ducking for cover), there may be times when non-normalizing is
justified. Have assumed that the actual case is more complex than this
example.
 
My main concern was the OPs desire to change the field's Required property
at runtime. I can't see any case where that would be legitimate. But I read
his need too literally. He was clearly referring (unknowingly) to a
validation rule.

Cheers,
TC
 
TC...
I have a database that keeps track of recuts, development
time, measurement criteria, etc. A Technician will begin
a "control log" by entering a record into the database so
it can be tracked. This job will then be completed with
the master being passed or failed or the job will be
aborted for various reasons.
The Technician may not know what the 1st and 2nd
measurements are or the End Criterion until the the job is
completed. When a job is completed the database needs to
look at those fields and make sure something is entered
before allowing the record to be marked as completed.
 
Ok, understood. I read your initial post too literally.

The simplest way to do what you want, would be to define a validation rule
on the table. Something like this:

Not Completed Or (Field1 is not null And Field2 is not null)

That rule says to Access, "When you save a new or altered record in this
table, check that (1) the Completed flag is False, OR, (2) Field1 and Field2
are both entered".

If a user tried to break that rule, the form would object with an error
message. If necessary, you could trap that error in the Form_Error event of
the form, and issue a more suitable message. Thus, you could implement the
check you want, with little or no VBA coding. Read online help for "table
validation rules".

HTH,
TC
 
Back
Top