Validation Rule using fields from another table (1 Viewer)

[Frozzle]

Hi All,

I was just curious as to how you could possibly use a validation rule and have a field from another table as the maximum.

What im trying to achieve is to make it so that certain events are allowed to have a maximum points of 5 where as a couple bigger events are allowed to have 20 points as maximum and i have set up in another table a maximum points field for each event or if there was any other ideal way of doing this.

 

[theDBguy]

Hi. Welcome to AWF!

Not sure if that's possible at the table level. See if you can use DLookup() in a Validation Rule. If not at the table level, it might work in a form. Otherwise, maybe you can also try using data macros.

 

[The_Doc_Man]

Insofar as I recall, there is no way to build a field-based or table-based validation rule that crosses to another table, other than based on relational integrity. However, your criteria description of a numeric range comparison is not something that could be implemented via relational integrity.

I'm with theDBguy except I'll say it more strongly. To do this, if you want that rule in place, you MUST use it via a form that is capable of using code to perform validations.

Our best resource for validation rules is an Allen Browne article, here is the link:

Microsoft Access tips: Validation Rules

How and when to use Validation Rules on your fields and tables, in Microsoft Access databases.

allenbrowne.com

If Allen Browne doesn't offer a cross-table validation rule as part of a field or table property, then you probably cannot do that kind of validation except through forms. Which implies that direct data entry to a table cannot be allowed. You HAVE to use forms. On the other hand, that is what we usually recommend anyway. Direct data entry to tables or queries in datasheet view usually leads to disaster.

 

[arnelgp]

see Before Change datamacro of table1 in design view.
goto datasheet view of same table and enter some data.

 

[The_Doc_Man]

Oops - good point, Arnel. Forgot about data macros because I never used them when they first came out.