data validation?

[Darla123]

Hi everyone. I have 2 fields on my form. I am looking for some sort of data validation where if field A has a value of 1, then there are 5 possibilities that field B can have. If field A has a value of 2, there are 9 different possibilities that field B can have. Field A and B are bound to a table. What is the best way to accomplish this? Is there some sort of code I can add?

[zmbd]

1) One could use a bit of code to run against a table (as below) on a recordset to determine if the second entry is valid based on the first. I wouldn't hard code the allowed values in the VBA, instead, using a table. From a user standpoint, this would be difficult at best to use.


2) A better user experience; however, more difficult to implement would be to use a table for a combobox OR listbox for both controls.

Using a combobox for combobox1 (CBO1)

optionsone
PK,userfriendly
1 , a
2 , b
etc...

For combobox2 (CBO2) the table would be

FilteredOptions
PK, FK_From_optionsone, AllowedOptions
1 , 1                 , 201
2 , 1                 , 202
3 , 1                 , 203
4 , 1                 , 204
5 , 1                 , 205
6 , 2                 , 222
7 , 2                 , 223
8 , 2                 , 224
9 , 2                 , 225
etc....

(now of course in a normalized database, [AllowedOptions] would most likely be in another table and this would be just another foreign key to that table

For the CBO2:
Now what I would do here is create a query that uses the value from combobox1 as part of the WHERE:
For example

SELECT AllowedOPtions
FROM FilteredOptions 
WHERE ([FilteredOptions]![FK_From_OptionsOne]=
    [ComboBox1]);

This becomes the row source for the control.
This is NOT the control source. The control source may be unset or set to reflect/save the value in a table.

For CBO1
The record source would be the first table (options).

More detail can be found here: Cascading Combo/List Boxes