Is it possible to validate a field in one table against another table?

[Philocthetes]

I'm building a production-oriented database that needs to track who did what task (many instances). I started with lookups and this morning learned how that was a poor decision for my context.

Now I'm trying a workaround that will limit workers to entries that match one of the rows in [Users].[Short_Name]. I can't seem to get the Validation Rule box to recognize the table-field reference.

I've tried multiple forms the parentheses that the error-on-save demands, with and without the brackets, and also tried to tweak a syntax I found somewhere in Help, Users!Short_Name. I think that last one just won't work with tables; Help had not examples of that.

I know I could just put a list in the Validation Text box, but I don't want to have to update that on a bunch of different tables when I have a Users table with a yes/no field for Active. When I was working with the lookups, it was easy to add a criterion Active=Yes to the lookup query.

Does this mean that despite all the good reasons to avoid lookups, I have to use one if I want to avoid manually updating Validation Text lists for a dozen (more, probably) fields?

 

[jdraw]

I'm not sure where you are in your database project, but here is a link to information on database planning and design.

I hope the info is useful to you.
Good luck with your project.

 

[Pat Hartman]

Open the Relationships window and add the two tables. Draw a join line between the FK (former lookup field) in one table to the PK in the other. Select the Enforce RI checkbox to enforce RI between the two tables.

So to link a worker to a task, add the WorkerID to tblTask and create the relationship between WorkerID on tblTask and WorkerID on tblWorker.

On your form, use a combo to display the list of available values. Lookups are only bad on tables. They are not bad on forms.

The reason they are problematic on tables is because they mask the actual value of the field. Therefore, when you look at the data, you think you are looking at a text string but in fact the stored value is numeric and what you are seeing is the text value from the lookup table.

Table levels lookups are a crutch for people who have no clue how to write a query. When you develop applications for others, you NEVER give them raw tables or queries to work with. They always work with forms where you have events that you can use to control what they do.

 

[The_Doc_Man]

Does this mean that despite all the good reasons to avoid lookups, I have to use one if I want to avoid manually updating Validation Text lists for a dozen (more, probably) fields?

No. It means your design is going in the wrong direction.

You are going around and around the mulberry bush but I think it comes back to a simple idea. Here's the question: Were you planning to let your users see actual tables and/or queries directly? If yes, you have already lost this battle.

Users should NEVER see anything but forms and reports (possibly including switchboard or dispatcher forms.) In THAT context, you can do things in appropriate form events to change filters, disallow access, or force the application to quit. You can put filters on forms that in a table would be difficult or impossible.

To answer the direct question, I don't recall having ever seen a successful case of cross-table validation using a table property. (But I'll have to admit I've never tried it myself.)

This article from Allen Browne discusses validations rules in depth.

http://allenbrowne.com/ValidationRule.html

The article offers a comment in passing about whether a validation rule could depend on other records or other tables. Allen says "Not easily." You don't know us well but I have to ask you to trust me on this one: If Allen says "Not easily" then it is NOTHING that a novice would want to try. Heck, it would give ME pause.

Pat's comment mirrors Allen's mention of using a "related table" as a way of validating. You have to read up on relational integrity to use this ability and I'm not sure it applies to what you are doing.

 

[Philocthetes]

On your form, use a combo to display the list of available values. Lookups are only bad on tables. They are not bad on forms.

Users should NEVER see anything but forms and reports (possibly including switchboard or dispatcher forms.) In THAT context, you can do things in appropriate form events to change filters, disallow access, or force the application to quit. You can put filters on forms that in a table would be difficult or impossible.

Now I remember some basics from years-ago training. I'll remember to use lookups if necessary on forms only, and that I can set default User entries on the forms for individual front-ends.

Most of my tiny group of users are moderately to fairly technical, but I still keep them working in forms, with the occasional read-only query.