Question

From: Dean
Sent: Tuesday, October 15, 2002 10:53 AM
To: Len Fawcett
Subject: Access 2000

I attended an Access 2000 programming course with yourselves at your London office in July 2001 and now find myself in a position where I am stuck with an issue that I am sure Access 2000 is capable of dealing with.

The lady lecturer who took our course explained that if at any time we had any questions relating to Access 2000 we could contact her for some help. I can’t remember the lady’s name and so I thought I would send my query to customer support in the hope they can either answer it or re-route it to some one who can. I hope I haven’t “run out of warranty” on support

The issue is that I am trying to restrict my choices in a combo box based on the chosen values in a previous combo box on the same form / record. In other words once I have selected a group (via a combo box) I only want to see members of that group available for selection in the next combo box.

I hope you can help as this is driving me mad!!

Thanks in advance

Regards,

Dean

Answer

From: andy fawcett20 [mailto:andyfawcett20@hotmail.com]
Sent: Thursday, October 17, 2002 6:11 PM
To: dean
Cc: alisonmilner@hotmail.com
Subject:

Dean,
one technique you could use would be to use a filter by form technique.
Here the second dropdown, could be based on a query where the criteria
was driven from the value of the first drop down, eg. in the query it
would ref Forms!formname!comboboxname in the criteria.

For example imagine a form where you want to restrict a combo box to
display
towns form a country rather than all towns for all customers (using
northwind.mdb example)

I create a form called formComboDemo, with 2 combo boxes cbocountry and
cboCity CboCountry has rowsource SELECT DISTINCT [country] FROM
customers;

cboCity has rowsource SELECT DISTINCT [Customers].[City] FROM Customers
WHERE ((([Customers].[Country])=[forms]![formCombodemo]![cboCountry]));

this is the ref to the country combo
"[forms]![formCombodemo]![cboCountry]"

this will work the first time but if you select another country does
not update so you need to add code to change event of cboCountry

Private Sub cboCountry_Change()
 Me.cboCity.Requery
End Sub


to enter code, select change event from property sheet, click on ...,
select
code builder and paste in Me.cboCity.Requery


Hope this helps

Andy Fawcett (f1 developer)