Limiting Lists (Access 97)

Topic

New Reply

Hi, can’t get my head round this one – can anyone help. I have two combi boxes Select 1 and Select 2. Select 1’s underlying table has 4 items id’d A, B, C and D. Select 2 has 10 items id’d 1:10.

What I am trying to do:

If Select1.Value = A then I want to show 1:10 in Select 2. If Select1.Value = B Then I want to show value 1 only, If Select1.Value = C OR D then I want to show values 1, 3 and 5. I have been trying to code as an After Update event on the Select 1 combi, but am not having any success as my syntax is not good. Can anyone help please? Many thanks.

Reply | Quote

Replies

Create a table with all possible combinations of Item and ID. It would look like this:

ItemID	OtherID
A	1
A	2
A	3
A	4
A	5
A	6
A	7
A	8
A	9
A	10
B	1
C	1
C	3
C	5
D	1
D	3
D	5

Let’s assume this table is named tblJoin.
Set the row source of Select2 to
SELECT OtherID FROM tblJoin WHERE ItemID = Forms!frmMyForm!Select1
where frmMyForm should be replaced by the name of your form.

In the AfterUpdate event of Select1, requery Select2:
Select2.Requery

If you need more columns in Select2, you’ll need to set its row source to a query that joins your original table and tblJoin.

Reply | Quote

Thanks Hans

Reply | Quote