 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2005validate a field by another table
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 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 > Hi, > I'd like to know how to set a validation rule for a field, so that the [quoted text clipped - 9 lines] > > Wei 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 > Hi, WZ. > [quoted text clipped - 20 lines] > > > > Wei 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 > Sprinks, > Thanks for your reply. [quoted text clipped - 32 lines] > > > > > > Wei >Hi, >I'd like to know how to set a validation rule for a field, so that the [quoted text clipped - 5 lines] > >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] 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 [quoted text clipped - 18 lines] > > John W. Vinson[MVP] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.