Updating a "County" field once a "town" field has been enter 1

[mobile2]

I have a list of 4000+ towns and their relevant county, which will be 1 of 33 names. At present the users input both fields manually. I would like the county field to update automatically once the user has entered a town. If the list of towns and relevant counties is stored in the database, there must be a way it can look up the table and insert the relevant county automatically. Any easy suggestions?

 

[danvlas]

If the list is already stored in the database, then just create a relationship between the table where you enter data and that list, on the Town field. The form where you enter data should have the recordsource based on both tables:

Select YourTable.*, TownList.County From YourTable Left Join TownList On YourTable.Town = TownList.Town;

Whenever you enter a town in the corresponding field from YourTable, the county will be displayed in the field coming from the TownList table.

I assumed Town is unique in TownList table...

Good luck

Daniel Vlas
Systems Consultant

 

[mobile2]

The form where the towns are recorded already has a record source. This source is a query which includes a parameter query on the Company Name field. Will I be able to add an expression to the town field within this query that will pick up the county automatically and insert it on the form?

 

[danvlas]

Just include the Towns table in that query and make sure the join type is 2 or 3 (whichever leads to an arrow pointing to the Towns table).


Daniel Vlas
Systems Consultant

 

[mobile2]

Apologies for not getting back sooner. Thank you for that very easy solution.

My colleague who inputs the data is very happy!