Limiting data input in Access based on another field

[Guest]

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

 

[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.

 

[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

 

[Graham Mandeno]

Merci, Michel. I'd forgotten about the IMP operator.

 

[onedaywhen]

I'd forgotten about the IMP operator.

Indeed, which could make it unsuitable from a code maintenance point of
view.

I prefer the case (SWITCH) approach because is is more readable e.g.

CREATE TABLE Test (
f2 INTEGER NOT NULL,
f3 CHAR(1) NOT NULL,
CONSTRAINT f2__f3__valie_interaction
CHECK (1 = SWITCH(
f2=5 AND f3 IN ('A', 'B'), 1,
f2=4 AND f3 IN ('B', 'C', 'D'), 1,
f2=3 AND f3 IN ('D', 'E'), 1,
TRUE, 0))
);

Jamie.

--