Limit a dropdown box values depending on the value of another dropdown box

[morlan]

I have a drop down list of Sales Executives names and below that is another dropdown list for different cities.

I have a problem with my form where people choose the SalesExec and then accidentally choose the wrong city from the dropdown.

I want the user to choose the SalesExec and then the city dropdown box below it would be limited to the cities that the SalesExec actually works in.

Example:

The user selects 'John Raspete' and then the City combo box would only have London, New York and Paris as the available options.

I have created a table (tblSalesExec) with the SalesExec and their corresponding venues.


Note: Venue1, Venue2, Venue3 fields are ID lookups to tblVenue

tblSalesExec

ID | Forename | Surname | Venue1 | Venue2 | Venue3
-------------------------------------------------
1 | John | Raspete | London | New York | Paris
2 | Jen | Pazlmer | Tokyo | London | Madrid
3 | Mark | Holmon | London | Madrid | Amsterdam
4 | James | Pratt | Dublin | Madrid | Glasgow
5 | Gary | Piles | Dublin | London | Madrid



tblVenue
--------
1 London
2 New York
3 Paris
4 Tokyo
5 Mardrid
6 Glasgow
7 Dublin
8 Paris
9 Amsterdam


Any suggestions would be appreciated.

Thanks

 

[Mile-O]

Change those three fields Venue1, Venue2, Venue3...-> n

to Venue

The Venue is not dependant upon the SalesExec so move the venue out of the Sales Exec table altogether.

Add one more table that will deal with Assignments.

SalesExecID
VenueID

Make these fields a composite key and you have now created a junction table to better manage your comboboxes.


Your initial set-up is going to cause you problems due to the repeating groups. What happens if someone gets a fourth office they can work in? Are you just going to add a new field called Venue 4? A fifth venue, a tenth venue, etc?

 

[morlan]

OK Mile,

I see what you mean. I will redesign the table now

 

[morlan]

MIle, I have now designed an 'Assignments' table as recommended.

SalesExecID | VenueID
-----------------------------
1 | 3
1 | 7
1 | 9
2 | 2
2 | 4
2 | 8
3 | 4
3 | 7
3 | 8

Could you elaborate on the composite key and junction table?

 

[Mile-O]

Have a look at the tables, relationships, and combobox RowSources in this example.

 

[morlan]

Have a look at the tables, relationships, and combobox RowSources in this example.

Jaysus m8, thats fantastic.

Ill examine the workings of it now