 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Validation based on other fields
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? This is record level validation, and so, you must trigger the check just before the record is commited to the table. If the validation fails, the commit fails, error message is given and the user returns to that record. I would advise against writing field level validation when there are dependencies among the fields as in your case. > 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 [quoted text clipped - 7 lines] > value of the [Categories] field? > How do I set a default value based on the [Categories] field? 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.....  SignatureNTC > 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 [quoted text clipped - 7 lines] > value of the [Categories] field? > How do I set a default value based on the [Categories] field? 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? > 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 [quoted text clipped - 28 lines] > > value of the [Categories] field? > > How do I set a default value based on the [Categories] field? 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.... SignatureNTC > 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 [quoted text clipped - 39 lines] > > > value of the [Categories] field? > > > How do I set a default value based on the [Categories] field? Oh, that's how you do it. Thanks! > well I think maybe the safest and easiest approach for you is not to use that > Validation Rule property... [quoted text clipped - 76 lines] > > > > value of the [Categories] field? > > > > How do I set a default value based on the [Categories] field? I'd like to generate the warning message after they enter a value. How do I limit the entry? > 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 [quoted text clipped - 28 lines] > > value of the [Categories] field? > > How do I set a default value based on the [Categories] field? |
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.