By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,829 Members | 1,204 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,829 IT Pros & Developers. It's quick & easy.

Validation Code - Comparing 2 fields on a form

P: 17
Hello!

Would be grateful for help with this one - a Record level Validation problem
I run Access 2000 on XP

A form has two fields:
1) fldLevel – it’s an Option Group with 4 choices = 1, 2, 3 and 0.
2) fldStatus – is a limited value-list (Combo) with 3 choices = Unassigned, In-Progress, Complete.

Any record in the form cannot be both Level_0 and Status_Complete

Scenario:
A record in the form has been (legally) set to Level_3 and Status_Complete.
For whatever reason, UserX has come in and tried to change the level to 0 (while the status remains as Complete). So, I want to

a) Prevent UserX’s action from being accepted
b) Revert fldLevel back to what it was before UserX attempted the action…….

I have this code which works fine for what I want – up to the point, that at the end of the code the focus is on Level 0, waiting for UserX to change it (s/he could now legally choose to make it Level 1, 2 or 3) but…and this is really my problem: I want the fldLevel focus to be moved back (revert to) to the Level3 it was on before User X tried the illegal change. How can I (or can I?) amend the code to do that?

Private Sub grpLevel_BeforeUpdate(Cancel As Integer)
If (Me![fldLevel]) = "0" And (Me![fldStatus]) = "Complete" Then
MsgBox "Your change is not accepted." & vbCrLf & _
"Level cannot be set to 0 if record is at Status 'Complete'. ", _
"Complete Status requires Level to be 1, 2 or 3 only ", _
vbOKOnly, " Level Invalid for Complete Status"
Cancel = True
End If
End Sub

Thanks for any help!
Regards, AMBLY
Jun 9 '07 #1
Share this Question
Share on Google+
2 Replies

ADezii
Expert 5K+
P: 8,750
Hello!

Would be grateful for help with this one - a Record level Validation problem
I run Access 2000 on XP

A form has two fields:
1) fldLevel – it’s an Option Group with 4 choices = 1, 2, 3 and 0.
2) fldStatus – is a limited value-list (Combo) with 3 choices = Unassigned, In-Progress, Complete.

Any record in the form cannot be both Level_0 and Status_Complete

Scenario:
A record in the form has been (legally) set to Level_3 and Status_Complete.
For whatever reason, UserX has come in and tried to change the level to 0 (while the status remains as Complete). So, I want to

a) Prevent UserX’s action from being accepted
b) Revert fldLevel back to what it was before UserX attempted the action…….

I have this code which works fine for what I want – up to the point, that at the end of the code the focus is on Level 0, waiting for UserX to change it (s/he could now legally choose to make it Level 1, 2 or 3) but…and this is really my problem: I want the fldLevel focus to be moved back (revert to) to the Level3 it was on before User X tried the illegal change. How can I (or can I?) amend the code to do that?

Private Sub grpLevel_BeforeUpdate(Cancel As Integer)
If (Me![fldLevel]) = "0" And (Me![fldStatus]) = "Complete" Then
MsgBox "Your change is not accepted." & vbCrLf & _
"Level cannot be set to 0 if record is at Status 'Complete'. ", _
"Complete Status requires Level to be 1, 2 or 3 only ", _
vbOKOnly, " Level Invalid for Complete Status"
Cancel = True
End If
End Sub

Thanks for any help!
Regards, AMBLY
You can prevent this situation before it ever occurs by setting the following Table Validation Rule:
Expand|Select|Wrap|Line Numbers
  1. Not ([Level]=0 And [Status]="Complete")
This will allow every combination of Level and Status 'except' [Level]=0 AND [Status]=Complete
Jun 10 '07 #2

P: 17
Thankyou ADezii ,
I appreciate your help :-)

I actually have more than one Validation, which applies to the table fields - so I think keeping the Validation at form level works best? In did try this out though, just on the one field as you suggested and it didn't work for me? Maybe I did something wrong....

But anyway, you did set me thinking, and I have now basically put all field Validations at record level in BeforeUpdate, and have added code to lock any record once it is put to Status Completed. Seems to do the trick!

Cheers
AMBLY
Jun 12 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.