Override Validation Rule

[Guest]

Hi,

I have validation rules built into my existing form.

For instance, a facility may only be built inside a SFR or MFR zoning area.
What's the best way to build a function into the form that will let the user
(maybe with password or other warning screen) override the validation rule?

Ideally it would be great if, when a user enters an invalid value, a warning
dialog displays showing the valid values (which my form currently does). The
perhaps there could be a diaglog that says: "Are you sure you want to
continue?" The user may then press "yes" and be prompted for a password.
Thanks for any suggestions. Ruben

 

[Guest]

Well you could try making your own validationrule in VBA and handle it from
there. Try something like:

Private Sub test_AfterUpdate() '-> Your testfieldname here
Dim strValidation As String
strValidation = "= Test" '-> Place your condition here

Me.test.ValidationRule = strValidation
Me.test.ValidationText = "not ok" '-> Place your message here

If Me.test <> strValidation Then
MsgBox Me.test.ValidationText

..... '-> ask something from the user and decide to go on. You could ask for
confirmation.
else
...... '-> leave it as it is
End If

End Sub


hth

 

[Guest]

If you say you have the validation rule built into the form, I am assumming
you have either VBA code or a macro that fires on the Before Update event of
the form or of the control. If it is not in a Before Update event, it will
do no good. The After Update event cannot be canceled.

If you can tell me how you are doing the validation, I can offer a
suggestion on how to accomplish your objective.

 

[Guest]

Klatuu,

Thanks for the offer. The validation rule is actually built into the table
design. When you look in the design view of a table, the is a general tab at
the bottom left-hand corner of the window that has 'Validation Rule' and
'Validation Text'.

So for instance, one of my fields "StreetClass" has a 'Validation Rule' of
"="RESID" and 'Validation Text' of "Street Type Must be Residential only!"
If a user tries to enter something other than "RESID" on the form, a message
box will appear automatically (NOT something that I programmed in using a
macro or VBA) displaying the 'Validation Text'.

I want to be able to, in certain instances, override this validation rule
from the form interface by possibly using some kind of process where a
message box displays "Are you sure you want to enter {that value}? If the
user pushes yes he/she will be prompted for a password. If the password
passes then the value will be allowed. It doesn't necessarily need to be
exactly as stated, just as long as there's some way of being able to override
the validation from the form. Thanks again. Ruben.

 

[Guest]

I must have misread your post. I was under the impression the rule was at
the form level.
You cannot override a table or field level validation. This level of
validation is there to strictly enforce the rules.
If it is necessary to occasionally allow other values in the field, you will
need to remove the validation from the table field and write a Before Update
event procedure that will check the value and allow the change.

 

[Guest]

Okay I'll try that thanks.

I must have misread your post. I was under the impression the rule was at
the form level.
You cannot override a table or field level validation. This level of
validation is there to strictly enforce the rules.
If it is necessary to occasionally allow other values in the field, you will
need to remove the validation from the table field and write a Before Update
event procedure that will check the value and allow the change.

 

[Guest]

Maurice,

thanks for your reply. Most of the code you provided makes sense to me.

Quick question about the logistic of the AfterUpdate( ) [is that considered
a conditional function or what?]:

Is the record considered updated after the user steps out of the field in
the form (say, a text box?). The reason I ask is because I want to know if i
can use one subroutine for all of the fields in the form for which I want to
apply validation rules, or if I have to create a new subroutine for each
validation rule.

Also, does the field name go inside the parenthesis? Should it look like
this:

Private Sub test_AfterUpdate(Me.[ZoningCode])...or

Private Sub test_AfterUpdate( ) Me.[ZoningCode]

also, I'm not sure about the part where I need to ask the user to decide to
go on and enter a different string or to leave the string as it is.

If Me.test <> strValidation Then
MsgBox Me.test.ValidationText


MsgBox "Are you sure you want to go on?"
User clicks no and goes back to change the value

else

User clicks yes and the value stays the same
End If
End Sub

thanks for your suggestion. I think that's the best approach at this point.
Ruben