Validation Rule for field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have a form which for a table and have the following column
ITEM
AMOUNT

I want to set a validation rule to the field AMOUNT. If the user input "A"
in ITEM, AMOUNT should >0. If the user input "L" in ITEM, AMOUNT should <0.

How can I do this?

Thanks!

Danny
 
Danny,

The Validation Rule has to be set at the Table level rather than the
field level. In design view of your table, select Properties from the
View menu. Then in the Validation Rule property, enter something like
this...
([ITEM]="A" And [AMOUNT]>0) Or ([ITEM]="L" And [AMOUNT]<0)

This may need some refining :-) For example, as I have written it
assumes that AMOUNT can not be =0, and it also assumes that ITEM can not
have anything else other than A or L.

An alternative to using the Validation Rule would be to use code on the
Before Update event of your form, to check the validity of the data entered.
 
In the After Update Event of the Amount Field put the following code
(assuming ITEM has been validated and can only be "A" or "L")

If ITEM = "A" then
If AMOUNT <=0 then
MsgBox "Invalid Entry - Must be > 0"
Amount = 0
Amount.SetFocus
End if
Else
If AMOUNT >=0 then
MsgBox "Invalid Entry - Must be < 0"
Amount = 0
Amount.SetFocus
End if
End if
 
On the before update event of the Amount field in form enter the code
If me.Item = "L" and me.AMOUNT > 0 then
msgbox "Amount must be Smaller then 0"
cancel=true ' It wont exit the field
SendKeys "{ESC}" ' To return the prev value
Else
If me.Item = "A" and me.AMOUNT < 0 then
msgbox "Amount must be Grater then 0"
cancel=true ' It wont exit the field
SendKeys "{ESC}" ' To return the prev value
End If
End if
 
Absolutely wrong. Never put validation rules in tables.
The proper way to do this would be in the Before Update event of the form.

Steve Schapel said:
Danny,

The Validation Rule has to be set at the Table level rather than the
field level. In design view of your table, select Properties from the
View menu. Then in the Validation Rule property, enter something like
this...
([ITEM]="A" And [AMOUNT]>0) Or ([ITEM]="L" And [AMOUNT]<0)

This may need some refining :-) For example, as I have written it
assumes that AMOUNT can not be =0, and it also assumes that ITEM can not
have anything else other than A or L.

An alternative to using the Validation Rule would be to use code on the
Before Update event of your form, to check the validity of the data entered.

--
Steve Schapel, Microsoft Access MVP

Hi all,

I have a form which for a table and have the following column
ITEM
AMOUNT

I want to set a validation rule to the field AMOUNT. If the user input "A"
in ITEM, AMOUNT should >0. If the user input "L" in ITEM, AMOUNT should <0.

How can I do this?

Thanks!

Danny
Click to expand...
Click to expand...
 
Klatuu,

What on earth are you talking about? The facility to put field-level
and table-level Validation Rules in your table design is one of Access's
great features. It is very often the correct, best, and easiest
approach. Why wouldn't you use this?
 
Steve,

I don't think it allows the control you need, for starters, and most
important, should you ever have to upsize, it creates a problem. Not all
database engines will recognize it.

Also, if you are modifying or debugging it is not readily obvious. I just
think it is better to do all validation using VBA in the form.

If it works for you and you like it, okay. I would not use it and I advise
that it not be used.
 
Klatuu,
I don't think it allows the control you need, for starters,
Click to expand...

Yes it does. It provides precisely and simply the exact control you
need if you have a field-level or table-level validation requirement.
and most
important, should you ever have to upsize, it creates a problem. Not all
database engines will recognize it.
Click to expand...

Well, this applies to a lot of data types, properties, etc... Do you
avoid them all for the same reason? Thereby denying yourself some of
Access's most attractive bebefits. I can't believe anyone would
consider this a valid design choice.
Also, if you are modifying or debugging it is not readily obvious.
Click to expand...

Yes it is. It wouldn't be obvious to you, maybe, because you don't use
it so therefore don't think of it.
I just
think it is better to do all validation using VBA in the form.
Click to expand...

No it's not. Certainly there are pleanty of scenarios where form-level
validation is applicable... but not in the examples we have been discussing.
If it works for you and you like it, okay. I would not use it
Click to expand...

Fair enough.
and I advise
that it not be used.
Click to expand...

In my opinion, you are doing a disservice to those you so advise.
 
Steve,
We obviously have different opinions on this. You will not convince me nor
I you. After 30 years of database and application design, I have developed
approaches that work for me. I am sure your's work for you.
 
Klatuu,

Access has only been in existence for 12 years. And even then, I don't
remember whether version 1 and 1.1 had table-level Validation Rules. So
your reference to 30 years is way off, with regard to the topic we have
been discussing.

I hope you haven't been under the impression I have ben trying to
convince you of anything. This is not my intention at all. When you
stated "Absolutely wrong. Never put validation rules in tables", you
made it sound like you know what you're talking about, and I just wanted
to point out to other readers that this is not so.
 
Where did I say Access for 30 years? I said application and database design.
Only 6 years in Access. Languages and Databases are all pretty much the same
conceptually.

Access, as well as other products, put features and functions into products
that may or may not be good ideas, but the help sell product.

Putting validation in tables and fields is one of the ways Access caters to
the non professional or less experienced designer. As long as you know you
will never upsize to SQL Server, Oracle, DB2, or another engine, then it is
okay.

In my world, I have to always consider that possibility. I try to design
and code my applications so they can be ported to other database engines and
VB with minimul modifications.

I wouldn't know about versions any earlier thn 97. That was the current
version when I started with Access. I moved from 10 years with Foxbase and
Foxpro to Access.

I apologize for the original statement that upset you. I do not mean that
you are not competent or knowledgeable. In my opinion field level validation
has some restrictions that I don't want to deal with. For example, in the
current application I am working with, there are some business rules that
change validation rules for a field based on values in other fields. Can you
do that with field level validation?
 
Klatuu said:
... For example, in the
current application I am working with, there are some business rules that
change validation rules for a field based on values in other fields. Can you
do that with field level validation?
Click to expand...

No, not with field-level validation. But table-level validation (which
is a different thing) is often applicable in such cases. And then, as I
mentioned before, there are also times where writing validating code is
more appropriate.
 
Back
Top