well I think maybe the safest and easiest approach for you is not to use that
Validation Rule property...
instead right click on the [CategoryTextBox] Control (or whatever it is
really called); select Properties and select Events and select the
AfterUpdate event.....chose Code (or is it MakeCode...I can't remember...)
This will open the VB window which maybe will freak you out at first but the
cursor will be exactly where it needs to be be....paste in:
IF [CategoryTextBox] = "Standard" THEN
[Score for Flowers TextBox] = 0
End If
A couple of assumptions here.....
a. you put in the exactly correct names between the brackets [ ]
b. the [Score for Flowers TextBox] field property in the TABLE is as a
number (rather than text)...and since you eventually will have decimals be
sure it is defined as a decimal.
once you have pasted in the If/then statement, you just close the VB window
using the red X box in the upper right, and 'save' the form design change
when you close it....
get this much to work.....i.e. where you type "Standard" and then when you
TAB button out of this control to the next box you should see a 0 appear in
the other box....
once you get it....then simply repeat the process changing "Standard" and 0
to what ever you need.
In the end you will have a bunch of If/Then statements stacked on top of
each other.....it is ok to leave blank lines between them for better
readability......and which ever one is valid is the one that will
fire....should work fine....
--
NTC
Lynlongley said:
The "if, then" approach sounds great, but I have the basic Access database,
and don't do a lot of programming, so I'm not sure how to implement your
logic. So, in the "Validation Rule" property I tried
iif([CategoryTextBox] = "Standard",[Score for Flowers TextBox] = 0,36)
I get an error: "Invalid SQL syntax - cannot use multiple columns in a
column-level CHECK constraint"
What would be the syntax for use in the "Validation Rule" property?
NetworkTrade said:
well it is very do-able....it just depends a little on specifics - and
whether or not you are comfortable doing any vba. I guess I wouldn't
consider it a vanilla feature in terms of the validation property - I would
tend to go with an If/Then statement in an AfterUpdate event.
i.e. one portion would be
IF [CategoryTextBox] = "Standard" THEN
[Score for Flowers TextBox] = 0
End If
now if they put in Novelty, Miniature, Other etc....do you generate a
message box warning of the possible value range or wait until they enter a
value and validate it?
some of it kind of depends on how you want to steer them thru the process.....
you could consider making up fields or check boxes for all the options and
then make these control visible conditionally....
more than one way to skin the cat.....
--
NTC
:
I am creating a database for assigning a score based on a category. I have
two feilds: "Categories" and "Score for Flowers" Depending on the value of
the [Categories] field, the score may apply, and may have a different
maximum. For example,
Categories: Standard, Novelty, Miniature, Other
Score for Flowers: If Standard then 0 (score not applicable), Novelty <=15,
Miniature <=15, Other <=10.
How do I create the validation rule that will limit the maximum based on the
value of the [Categories] field?
How do I set a default value based on the [Categories] field?
