Varable Validation rules

[webrabbit]

How do I make the validation rules for a field to be variable based on the value(s) in one or more other fields, and allow these rules to be maintained by an admin-level (non-programmer) user?

 

[lupins46]

Very difficult I would say.
At table level you can't reference one field from another anyway.
Therefore you would have to have a form and code to build the rule and post the resulting code into the correct event procedure. Possible but not something I would ever want to do.

 

[jsteph]

At table level you can't reference one field from another anyway.

Sure you can. But still this won't help with making it dynamic, so the Form-level code is the only way.

However, you don't need to post code each time, just make it table-driven and run a validation function in Before_Update or you can even do '=MyValidatiionFunction' in the form fields Validation Rule.
--Jim

 

[lupins46]

'Sure you can'
You have a different version of ACcess to me then.

Help:
For field and record validation rules, the expression can't contain user-defined functions, domain aggregate or aggregate functions, the Eval function, or CurrentUser method, or references to forms, queries, or tables. In addition, field validation rules can't contain references to other fields. For records, expressions can include references to fields in that table.

 

[jsteph]

You need to right-click on the table titleb bar and go to Table Properites. It's a Table-Level validation rule where you can reference fields within that table.
--Jim

 

[jsteph]

I should have been more specific--it's a 'Record-Level' validation rule that you set at the Table Properties.
--Jim

 

[jebry]

Hi webrabbit!

What sort of changes are you expecting the admins to want to make? If they are simple changes like changing a date or other value to compare it against, then you can do this fairly simply. Just set up a parameter table that contains the necessary values and then make a simple form based on the table. Whenever the db is opened run a macro that will open this form in hidden mode and then you can reference the values from the form to do your validation. Set up a button that will make the form visible when the admins want to change the values and put an update button on the form that saves the changes and makes the form hidden again. Then your validation routine will be referencing the new values.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[webrabbit]

Entry Rules

1. An Entrant record has fields Entrant#, Class, Project, and other fields not germane to this problem.
a. The Entrant# is of course the primary key
b. The Class can have one of four values.
c. The Project can have any value from the Project table, except that most Projects are limited to certain Classes.
2. An Entry record has fields Entrant#, Entry_type, and other fields not germane to this problem.
a. The Entrant# is a reference to the Entrant Table and is also unique in the Entry table.
b. The Entry_type can have any value from the Entry_type table, except that some Entry_types are limited to certain Classes, or Projects, or both.

The validation rules for 1c & 2b vary from contest to contest. Updating the Project and Entry_type tables is easy; but how do I apply the secondary rule for 1c and 2b, and the tertiary rule for 2b, and put them in a table so they can be updated?

 

[jsteph]

Of course this can't be done at the table-level in Access. But with a validation function called in the Before_update (which will also catch inserts) you can do pretty much anything--if any check fails, set Cancel=True and post a message either in a msgbox or a status field on the form.

For 1c, ust scan the project table for a match on the (new)Project/Classes subtable--this will be what you can modify. This table will hold 2 fields, Project # and Class#, it's basically a list of valid project/class pairings. Same for 2b--but here the table looks like it might contain EntryType,Project#, and Class, or it might have a relationship with the Project/Class table--I'm not completely clear on all the rules.
--Jim

 

[jsteph]

...correction in the second paragraph change:

For 1c, ust scan the project table for a match

to

For 1c, just scan the project field for a match

--Jim