Prev: Design theory for maintaining historical address communications
Next: How to design relationship between tables
From: Chris on 5 Jan 2010 20:07
I have created a look up table and the fields are SuburbID (primary key)
Suburb, State, District, Map Ref.

Then I have created a form that allows me to put in addresses of companies
and my question is - how can I, on this form, select the suburb from a drop
down box and it automatically comes up with the state, district and map ref
in three separate boxes so I don't have to enter this information.

Thanks
From: fredg on 5 Jan 2010 22:26
On Tue, 5 Jan 2010 17:07:01 -0800, Chris wrote:

> I have created a look up table and the fields are SuburbID (primary key)
> Suburb, State, District, Map Ref.
>
> Then I have created a form that allows me to put in addresses of companies
> and my question is - how can I, on this form, select the suburb from a drop
> down box and it automatically comes up with the state, district and map ref
> in three separate boxes so I don't have to enter this information.
>
> Thanks

Using unbound text controls, set their control source to:
=[ComboName].Column(1)
=[ComboName].Column(2)
etc.
Combo boxes are zero based, so Column(1) is actually the second
column.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
From: John W. Vinson on 6 Jan 2010 00:24
On Tue, 5 Jan 2010 17:07:01 -0800, Chris <Chris(a)discussions.microsoft.com>
wrote:

>I have created a look up table and the fields are SuburbID (primary key)
>Suburb, State, District, Map Ref.
>
>Then I have created a form that allows me to put in addresses of companies
>and my question is - how can I, on this form, select the suburb from a drop
>down box and it automatically comes up with the state, district and map ref
>in three separate boxes so I don't have to enter this information.
>
>Thanks

The state, district, and map should simply NOT EXIST in any table other than
the Suburb table.

Just store the SuburbID, and use a Query linking to the Suburb table to look
up these values. That query can be used as the recordsource of a Report (to
print the address information, say).

Fred's suggestion will let you display (but not store) these fields on your
form. That's the correct approach.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Design theory for maintaining historical address communications
Next: How to design relationship between tables