Using the validation rule based on a value of a filed in the same table! (1 Viewer)

HAMMAMABUARQOUB · Jul 29, 2009

What??? what is that,,, yass you can...
Creat a table... for ex 3 colms... id, name, Maxallowed, input
remember when we want to use the validation rule prop in the table,, it wont allow us to refere to another filed in the same table,, but in forms it can be done with the secret magic of the [Forms] keycode!!
go to the Textbox props, open the data tab, in the vakidation rule put this:code

Code:

<=[Forms]![Table1]![MaxMark]

you can use a custome msg to appear to the user,, like:
you have exceded the amount,,,

that's wonderfull,,
here is a simple exam,,,
note,,, you can also make the MaxAlooed fild as invisible so you can judg better the code
try and say hi to Palestine
HAMMAM ABUARQOUB

boblarson · Jul 29, 2009

Umm, you can set a table level validation rule which refers to a value of a field in the same table. You just don't set it in the field validation rule, you set it in the TABLE's Validation Rule which you can get to by opening the table in design view and then select VIEW > PROPERTIES and the Table Properties dialog will open and then you have your Validation Rule in that. You can then set like:

[Field1]>=[Field2]

boblarson · Jul 29, 2009

And with Access 2010 you now will have "Data Macros" which will let you basically have Triggers in Access so you can set values based on the values of other fields when the values change.

HAMMAMABUARQOUB · Jul 29, 2009

[Field1]>=[Field2]

that was not acceptable by office 2000 upto office 2007 ,, i just tried!! all what i am sayin is that you can use this validation rule in forms rather than tables,, as i know,, and just tried! Access refuses to save the table design! but you are right about the table properities thing

thanks...
HAMMAM

boblarson · Jul 29, 2009

HAMMAMABUARQOUB said:
that was not acceptable by office 2000 upto office 2007 ,, i just tried!! all what i am sayin is that you can use this validation rule in forms rather than tables,, as i know,, and just tried! Access refuses to save the table design!

thanks...
HAMMAM

It certainly was and is available. And, it DOES work. But it only notifies you if you when it tries to update the record. I am enclosing a sample of an Access 2000 database where Field 1 must be greater than Field 2. I am also enclosing it as an Access 2002-2003 version.

Just try to enter a number in field 1 which is less than field 2 and then you need to try to exit the record so it tries to save.

Now, I'm not saying that this is the best way to do it, by any means. But I am saying that you are incorrect in that it does work and it CAN be used, if someone wants to. Your statement that it CAN'T be done is erroneous and therefore it should be stricken.

boblarson · Jul 29, 2009

By the way, your Screenshot shows you are trying to do it on a FIELD level validation and NOT, as I said, TABLE LEVEL validation.

boblarson · Jul 29, 2009

See - TABLE LEVEL:

HAMMAMABUARQOUB · Jul 29, 2009

i did it in the table props and it worked!
that's great..
but for finishing this thing i say i used it in a different way in forms!
that's all......
right you are

HAMMAMABUARQOUB · Jul 29, 2009

HAMMAMABUARQOUB said:
! but you are right about the table properities thing

thanks...
HAMMAM

soory but maybe i edited that reply simple secods b4 ur last reply.,
again thats for valuable contribution!
HAMMAM

boblarson · Jul 29, 2009

HAMMAMABUARQOUB said:
i did it in the table props and it worked!
that's great..
but for finishing this thing i say i used it in a different way in forms!
that's all......
right you are

Like I said, I would not recommend the table validation way as the way to go. I too, use forms and keep my users from the tables. So, I validate at the form level normally too. I just wanted to be clear that it actually WAS possible to validate two (or more) fields against themselves at the table level. It is probably something that very few people actually know that exists as it isn't really exposed (how many times do people go into the table properties dialog and then even if they do, who knows about the syntax for the validation anyway? Not many.

So, anyway, I am glad you see how that is done and I agree with you in that forms should be the way to go.

PaulWilson · Jun 10, 2011

Re: Using the validation rule based on a value of a field in the same table!

I have two yes/no flags in a table of phone numbers. One indicates if a phone number is active. The other idicates if a phone number is the preferred phone number for making contact. Obviously, if a number is preferred it must also be active. Would table level validation be appropriate in this case? If so, what expression would apply?

I attach a screenshot showing such a table with table properties.

PaulWilson

boblarson · Jun 13, 2011

Re: Using the validation rule based on a value of a field in the same table!

PaulWilson said:
I have two yes/no flags in a table of phone numbers. One indicates if a phone number is active. The other idicates if a phone number is the preferred phone number for making contact. Obviously, if a number is preferred it must also be active. Would table level validation be appropriate in this case? If so, what expression would apply?

I attach a screenshot showing such a table with table properties.

PaulWilson

Personally I would use the validation in the form's Before Update event and forego the table validation. However, if you are using a yes/no field then it should be -1/0 (-1 yes, 0 no) and so you would reverse your sign on your validation rule you are showing to <= instead of >=.

Using the validation rule based on a value of a filed in the same table! (1 Viewer)

HAMMAMABUARQOUB

Registered User.

Attachments

boblarson

Smeghead

boblarson

Smeghead

HAMMAMABUARQOUB

Registered User.

Attachments

boblarson

Smeghead

Attachments

boblarson

Smeghead

boblarson

Smeghead

HAMMAMABUARQOUB

Registered User.

HAMMAMABUARQOUB

Registered User.

boblarson

Smeghead

PaulWilson

Registered User.

Attachments

boblarson

Smeghead

Similar threads

Users who are viewing this thread