If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Validation Rules
I have a field called "Type" that I want to add to my table. In this field,
I will be using a list box with my own values that I will add. However, if a person selects one of these values that happens to be "Will not be incorporated".....I would like them to be required to enter the "reason" in another text box. Is there a validation rule/text that can work with this problem? Emmz |
#2
|
|||
|
|||
Validation Rules
Not really at the table level. Validation rules are for entry into that field
so if they enter something then need to enter something else in another field, it won't work. However you can do what you want in a form using some VBA code. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Emmz" wrote: I have a field called "Type" that I want to add to my table. In this field, I will be using a list box with my own values that I will add. However, if a person selects one of these values that happens to be "Will not be incorporated".....I would like them to be required to enter the "reason" in another text box. Is there a validation rule/text that can work with this problem? Emmz |
#3
|
|||
|
|||
Validation Rules
Jerry Whittle wrote:
Not really at the table level. Validation rules are for entry into that field so if they enter something then need to enter something else in another field, it won't work. However you can do what you want in a form using some VBA code. True, in Access there is really no table level validation, there is column level validation and row level validation. To have table level validation, you'd need check constraints, which isn't supported through the Access interface. But, this challenge is solvable from the Access interface using row level validation. While in table design view, bring up the properties dialog (hit Alt+Enter), then use something like the below in the validation rule property (also, stuff some meaningful text into the validation text property) Iif([Type]="Will not be incorporated", Len([ReasonField] & ""),1) 0 Perhaps, in stead of using text as datatype for your "Type" field, use a lookuptable and a numeric PK/FK? -- Roy-Vidar |
#4
|
|||
|
|||
Validation Rules
On Sep 18, 10:14 pm, RoyVidar wrote:
in Access there is really no table level validation, there is column level validationand row level validation. To have table level validation, you'd need check constraints, which isn't supported through the Access interface. Well, CHECK constraints can be created and maintained using the Access interface via SQL DDL code and, being engine-level constraints, data entered via the interface (and everywhere else) gets checked against such constraints. So 'support' indeed exists but I would agree they are not 'exposed' well in the Access interface. I think there are some fundamental problems to exposing CHECK constraints in the Access interface. Have you ever wondered what a table level CHECK constraint expression builder would look like? Let's face it, the Access Query builder tool thing cannot write subqueries and what truly table level CHECK constraint would not involve a subquery?! Also, the interface has a 'hard-coded' assumption of a one- to-one relationship between column and validation rule, ditto for row but, worse, it's referred to as the Table Validation Rule and is exposed in the table properties! Obviously, a table can have more than one CHECK constraint. So I think leaving their creation and maintenance to those who can write SQL is probably for the best. One 'nice to have' would be the definition of the failure message (ditto other flavours of constraint e.g. PK and FK), as is the case with Validation Rules. But I think it's essential that copying a table's 'structure' using the Access interface should also copy its CHECK constraints and I consider this omission a bug. And what conclusions can we draw from the fact that Access Help has no mention of CHECK constraints? (merely poor editorship, IMO). Jamie. -- |
Thread Tools | |
Display Modes | |
|
|