Microsoft Access Field Validation on a Form

[WhyMeAndNotYou]

I am trying to create a validation rule for one field based upon the value of another field using the Form.Field validation rule (in Field2): =IIF(Field1Condition,IIF(Field2Condition,T12,F12),IIF(Field2Condition,T22,F22))

The validation rules appears to work in that the appropriate invalid data message appears. However, once the validation message box is closed, control is passed beyond the invalid data field without necessitating that the inconsistency be corrected?

Is this a bug in Access? or is there a "design feature" whereby only the message appears without the necessity of correcting the inconsistency?

 

[jfgambit]

What exactlty are you trying to validate? A number, a word...can yuo explain the textboxes function.

Just need a little more informtion...



"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[WhyMeAndNotYou]

I am not sure the additional information you requested is relevant since for the purposes of activating the validation rule the process appears to work (but not for holding control until the validation rule is satisfied). However, who knows what is going on behind the scenes:

Field1 is a long integer, Field2 is a date;
IIF(Field1 = value,
IIF(Field2 IS NULL,0,-1),
IIF(Field2 IS NOT NULL, 0,-1))
Control for the Text Box2 is Field2.
There is also a Text Box1 on the form with a Control of Field1.

 

[WildHare]

Just because a programmatic validation rule has been violated, that does not automatically "undo" the violation and place the cursor back in the offending field (as table-level violation would).

You need to
1) cancel the event which raised the rule (exit, lost focus, whatever)
2) null out the offending value
3) re-focus to the offending field.

This is not a "bug" - you just didn't finish your process.





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[WhyMeAndNotYou]

Using a simple rule, e.g., field1<10, the validation process when entering an incorrect value is:
a) the validation message appears; which upon closing,
b) the cursor is placed back in the field being validated; if the value is not corrected,
c) a message appears stating that the record could not be saved due to validation rule violation.

The problem for which an explanation is being sought is that for the more complex rule the validation process when entering an incorrect value is:
a) the validation message appears; which upon closing,
b) the cursor moves to the next field in the form; the validation rule is still violated but no message appears and an invalid situation is being allowed to exist.

 

[Rolliee]

nz(...) is a v ery nice function for this. It changes a null number to zero and a null string or date as &quot;&quot;. I think what you want in your early question is.

iif( isnull([field]), 0, -1) but I would use the NZ(..)


rollie@bwsys.net

 

[WhyMeAndNotYou]

Thank you for your suggestion, but the goal is not to convert a null value to a non-null value.

The validation rule should resolve itself to either False -0 [zero], or True - a non-zero value. Based upon the values in two fields, the conditional statement would either be True (resolving to a non-zero value) or False (resolving to a zero value). As evidenced by the appearance of the associated validation text, Access appears to recognize an invalid combination of field values, but does not prevent leaving the field to which the test is tied unless the invalid condition(s) are resolved.

 

[WildHare]

Exactly my point - your form-level validation is not the same as table level validation. When a table level (or actually, field-level) V/R is broken, the data engine will not allow progress into other fields in the record until the violation is absolved somehow.

But with procedural validation, it looks like you have to control the cursor yourself.



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[WhyMeAndNotYou]

I am not sure what you mean by procedural validation, but if you are using it synonymously with form level validation, then your conclusion is not 100% accurate.

I have tried testing various scenarios and the results appear to be inconsistent. If the validation rule for a test field on a form does not contain a reference to another field on the form, then the operation of the rule appears to be straightforward, i.e., control is returned to test field for data correction (it appears to operate in the same manner as a table.field validation rule).

However, if the validation rule for a test field on a form does contain a reference to another field on the form, then the operation of the rule appears to be inconsistent, partially dependent upon which field's rule is being violated and the complexity of the logical expression.

I was attempting to perform the validation with resorting to code, but if it is necessary to control which field gets focus, it appears that coding is the only option.

 

[WildHare]

By &quot;procedural validation&quot;, I meant something along the lines of

If FieldX <> y or FieldX = Z and FieldY < 10....

kind of stuff. But in any case, you're probably on target - I don't do a lot of this type of validation, but I think sometimes a lot depends on which EVENT you code the violation rule in - CHANGE, EXIT, LOST FOCUS, AFTER UPDATE, etc.

I have had, on occasion, the need to run data through a fairly complex verification process, and have resorted to a procedural call to do the verification. I've found that compound complex IF... statements tend to give me headaches so I often try to simplify matters with Select Case. Have you tried that?

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[WhyMeAndNotYou]

Thank you all for your suggestions, but in the end I just gave in (up?) and coded the validation rules using a macro and assigning it to the onexit action. The validation criteria was coded as the macro condition with the macro actions of Msgbox and GetFocus. In one attempt at creating an expression for the validation rule, Access reversed the &quot;true&quot; and &quot;false&quot; states (viz., returning a 0 (zero) when the compound condition was true and a -1 when the compound condition was false) - although it was tempting to run with that code, who would remember how to maintain it if it had problems in the future.

Again, thank you again for the suggestions.