validate a field by another table

[wz]

Hi,
I'd like to know how to set a validation rule for a field, so that the
values in it must be within a set which is defined in another table.

for example,
I have a field material.category, and it must be one of values in
category.name

What should I type in the expression builder?

thanks in advance,

Wei

 

[Sprinks]

Hi, WZ.

The easiest and best way to do this is to use a combo box. Flip on the
wizard in form design view (View, Toolbox, toggle on the button with the wand
and stars), and follow the prompts. Select Hide Key Field to display the
meaningful name rather than the code that represents it. The code, though,
will be stored in the underlying field.

Hope that helps.
Sprinks

 

[wz]

Sprinks,
Thanks for your reply.
I did use combo box in the field, but it's in the table, not in a form (I
have no form yet). And I have no control if a user picked one item from the
list then changes the value in it manually, since there is no validation. I'd
like to find a way so that even administrator can't mess up the field values.
Any idea about it?

thanks,

Wei

 

[Sprinks]

Hi, wz.

See Help on the Validation Rule property in table design view. You are
limited to an expression. The expression can use a domain aggregate
function, but only at the form level.

However, I would never let a user interact with a table directly. You have
so much more control on a form. The combo box can be set to Limit to List,
which prevents a user from entering anything but a valid value, and you can
use event procedures at the field and form level to ensure that all necessary
data has been entered and is valid.

Sprinks

 

[John Vinson]

Hi,
I'd like to know how to set a validation rule for a field, so that the
values in it must be within a set which is defined in another table.

for example,
I have a field material.category, and it must be one of values in
category.name

What should I type in the expression builder?

Well, I'm sorry to disagree with Sprinks here, but there is a
different and much better solution than Microsoft's abominable Lookup
misfeature.

Open the Relationships window. Add the Material table and the Category
table. Drag [Name] from the Category table to [Category] from the
Material table, and create a relationship; check the Enforce
Relational Integrity checkbox. This will establish a "constraint" on
the Material table, preventing you from adding any category which
isn't in the Category table.

John W. Vinson[MVP]

 

[Sprinks]

Thanks, John. Clearly that is the most fundamental solution. But I think
you misunderstood what I was recommending, which was a combo box, not the
Lookup feature, which you taught me years ago to ignore.

Sprinks

Hi,
I'd like to know how to set a validation rule for a field, so that the
values in it must be within a set which is defined in another table.

for example,
I have a field material.category, and it must be one of values in
category.name

What should I type in the expression builder?

Well, I'm sorry to disagree with Sprinks here, but there is a
different and much better solution than Microsoft's abominable Lookup
misfeature.

Open the Relationships window. Add the Material table and the Category
table. Drag [Name] from the Category table to [Category] from the
Material table, and create a relationship; check the Enforce
Relational Integrity checkbox. This will establish a "constraint" on
the Material table, preventing you from adding any category which
isn't in the Category table.

John W. Vinson[MVP]