Drop down list with matching values only

[champion83]

Hi all,

I need support from access experts as I am trying to build Access Database and at the beginning I have a problem with building the table correctly.

I have to tables: tblDepartment and tblDataEntry with columns as below:

tblDepartment

ID Site Dept
1 London Loading
2 London Tipping
3 Manchester Goods In
4 Manchester Goods Out
5 Liverpool Recycling
6 Liverpool PI

tblDataEntry
ID Site Project ref Dept
1 Manchester MA-01
2 Liverpool LI-02
3 London LO-03
4 London LO-04
5 Liverpool LI-05
6 Manchester MA-06

The problem is in the tblDataEntry table in the Dept column I would like it to insert a drop down list which should be connected with Dept column from the tblDepartment table.

On drop down list I would like to see only the departments associated with a given site for example:

If someone completing the table chooses Manchester as the site, if they click on the Dept column in the same record, they will only show a list with two departments: Goods In and Goods Out


ID Site Project ref Dept
1 Manchester MA-01 Goods In or Goods Out

I tried a lot of solutions but I think I am doing something wrong .

I am asking you for help.

 

[Gasman]

Search this site or Google for 'Cascading Combo'

That is what you are wanting to create.

 

[champion83]

Search this site or Google for 'Cascading Combo'

That is what you are wanting to create.

Thank you for the very quick reply. I will try to solve this problem again tomorrow morning or tonight. At the moment my head needs some rest because all these problems are arising from nowhere.

As soon as I will test it, I'll let you know if it worked for me.

Thank you again and best regards.

Lucas

 

[Gasman]

Start with this thread, and it's links.

https://www.access-programmers.co.uk/forums/showthread.php?t=68514&highlight=cascading

 

[champion83]

I managed to create Cascading Combo boxes in a new database but I can't run it in my current database


All this works but only partially. I get a list of departments for a particular site, but the same values appear many times (about 16 times ).

Does anyone know what I'm doing wrong? :banghead:

 

[arnelgp]

you can use Total query or a simple "select distinct" query will eliminate any dups from the query.

 

[champion83]

A few days ago I was looking for the SQL query with distinct to remove duplicates. I don't know why I didn't manage to do it correctly.
Today I have created the query using "Query Builder" I just replaced "select" with "select distinct" and it works.

I still have to learn a lot.

Thank you all very much for help.