Validation Code - Comparing 2 fields on a form

[AMBLY]

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_Before Update(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

[ADezii]

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_Before Update(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:

Not ([Level]=0 And [Status]="Complete")

This will allow every combination of Level and Status 'except' [Level]=0 AND [Status]=Complete

[AMBLY]

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