Limiting data input in Access based on another field

T

The Unc

I am trying to limit data in a field, in a form, in Access based on what has
been input in a previous field. So if a 5 has been input in one field only an
A or an B can be input in the next field. If a 4 has been input then a B, C
or D can be input and if a 3 is input then a D or an E can be input in the
next field. I am using Access 2003 in 2000 format
 
G

Graham Mandeno

You can use the BeforeUpdate event of the second textbox to validate the
entry.

Select Case Me![Textbox1]
Case 5
If Not (Me![TextBox2] = "A" or Me![TextBox2] = "B") then
Cancel = True
End If
Case 4
If Not (Me![TextBox2] = "B" or Me![TextBox2] = "C") then
Cancel = True
End If
Case 3
If Not (Me![TextBox2] = "D" or Me![TextBox2] = "E") then
Cancel = True
End If
End Select

Of course, this won't stop a user from setting box 1 to 5 and box 2 to A,
then going back and setting box 1 to 3.

For this reason you should probably also check in the form's BeforeUpdate
event to ensure everything is OK before the record is saved.
 
M

Michel Walsh

Hi,




A table validation rule can do, but it seems you basically have a bigger
design problem. But as for the table validation rule can be involved, for
the exact scenario you mentioned, it could be:


((f2=5) IMP (f3 IN("A", "B")))
AND ((f2=4) IMP (f3 IN("B", "C", "D")))
AND ((f2=3) IMP (f3 IN("D", "E")))



Hoping it may help,
Vanderghast, Access MVP
 
Top