Newbie Question: How to make a table lookup field depend on value in another column

[Torchwood]

Hi - I'm new here, and very new to Access. I'm pretty experienced with Excel and VBA, but have come across a problem at work that I know really needs to be solved using Access - so time for me to learn! I'm Googling for as much as I can, but I'm going to get stuck quite a few times with probably simple things! Thanks for your patience ...

(Using Windows 7, Access 2010 btw)

Q1

Say I have a table called Locations that lists Countries and Cities:

USA, Detroit
USA, Chicago
USA, New York
UK, London
UK, Liverpool
UK, Birmingham
France, Paris
France, Le Mans

I then have another table for inputing details on people I know. This table would have columns called Country and City. I've figured out how to make the Country column a "lookup column" so that I can only enter USA, UK or France. But how do I make the City column show only the appropriate list of cities relevant to the country that I've selected (e.g. Paris or Le Mans if France is the selected country).

Thanks!



Q2 to follow later

[pbaldy]

In a form you'd use cascading combos:

Baldy-Cascading Combos

By the way, most of us dislike the lookup field in table design:

http://access.mvps.org/access/lookupfields.htm

[Torchwood]

Great - thanks Paul. I had played with a Form previously, and got the 2 combo boxes to sort of work, but couldn't get the second to update when the first was changed ... I can see how your code fixes that

Interesting article on lookup fields. I'm assuming principle is to have users input their data using forms and not directly into the tables themselves. Then, the form to provides the control and validation of the entered data; i.e. we don't have this control embedded directly into the table itself.

As I'm just starting out with all this, I can easily avoid the use of lookup fields right from the start ...

EDIT: Ah! The 10 Commandments of Access #2

[pbaldy]

Happy to help, and welcome to the site! Most experienced developers, myself included, never let users into tables or queries, only forms and reports. you have virtually no control if they are in the table directly.

[smithse]

Happy to help, and welcome to the site! Most experienced developers, myself included, never let users into tables or queries, only forms and reports. you have virtually no control if they are in the table directly.

Hey pbaldy,

Yes, not letting users into the 'heart' of the system requires about 10-fold the number of hours of development... but it pays off! I'd rather write all those nasty SQL statements than bind forms! Yuck! LOL

[pbaldy]

Most of my forms are bound; why not let Access do most of the work? In limited circumstances, I do use unbound forms. Jason has a decent discussion of the issue here:

http://www.baldyweb.com/BoundUnbound.htm