Auto Entry of Fields from another table

[music_al]

I have a simple table called CUSTOMER with contact details on there...

First Name
Surname
Date of Birth
Address 1
Address 2
Address 3
Town/City
County
Post Code
email
etc

I have a second table called TOWN_COUNTY_REGION that lists all the towns and cities in the UK. Each Town/City is listed with its corresponding County and Region. This table has 3 fields...

Town/City
County
Region

The TOWN field in the CUSTOMER table is populated by the user selecting from a list of towns in the TOWN_COUNTY_REGION table. When a user clicks the town that they are in, I would like the appropriate COUNTY and REGION fields to be automatically populated in the CUSTOMER table.

So, for example, if the user clicks LIVERPOOL from the list of towns and cities, Merseyside and Northwest should automatically be populated in the other 2 fields.

How do I do this ??


Many thanks

Allan

 

[Dennisk]

First you need to include County and Region in the Combo box.

Secondly set the default value of the county/region to reference the appropriate column of the combo box.

or if the change event of the combo box set the county/region again using the column qualifier of the combo box.

eg

cboMyComboBox.Column(0) - get the first column etc ........................

 

[music_al]

Hi

Thank you but I dont really understand this either.

I am a complete novice when it comes to access. I have created 2 simple tables and thats about it.

This sounds like VB coding ?


Allan

 

[Dennisk]

only a little.

first a maxim

Tables are for STORING data
Forms are for ENTERING Data.

Never use a combo box lookup directly on a table.

so

Start by creating a query as the record source for the combo box use the town table and the 1st column is the Primary key, Followed by the Town, Count and region. Sort by town.

create a form and set the record source to the customer table

Create a combo box and bind that to the town and set the column width = 0 for the first column ie the Primary key..

(as the county is already in the database in the town table there is no need to include in the customer table.

Search the Forum for Normalisation.)

create an unbound textbox and disable it.
set the default value to

=YourComboxNameHere.Column(2)

see what happens when you select an item in the combo box.

although this only works for new records so create an on change event for the combo
and include something like this

yourTextBoxName=YourComboxNameHere.Column(2)

 

[music_al]

Thank you for this.

I feel like Im getting closer but when I change the town city on the form, the value in the county box isnt changing ???

 

[GaryPanic]

check inside this zip file in there should be a combo box drop down - looka t the after update code (...) and it opens up in coding and you will see what it is doing)
(I am doing this from memeory and not froma PC that has zip on it ...)
g

 

[music_al]

Thank you for this but this didnt really help me - Im not sure what Im looking for.

I have to stress Im a complete novice to this.

My simple understanding of it was, I have 3 columns of data (3 fields in the Town_County_Region table). I am selecting the town from column 1 of the table and want the values in the corresponding columns 2 and 3 to be returned.

I would still need to be able to search for Customers via county or region in the future. Will this effect what type of control I need to use?

 

[Rich]

If you already have the County in one table why are you storing it in another?

 

[GaryPanic]

what you need to do is doa qry on your town/coun ty/region
now make a combo box based onthe town/countryregion qry
make it on town
now do an after update country/region etc as per the sample I gave
you will need these 2 extra fields added tou tour underlying table and have tem vidible on your form

 

[GaryPanic]

zip the d/base up[ - this is a 5 min job -

 

[music_al]

I have a definitive list of all towns and cities in the UK and their corresponding counties and regions. I bought this list from the Royal Mail/Post Office and it is all accurate, normalised and without errors.

One thing I wanted to avoid with this database was user TYPING in certain parts of the address which could have led to errors. For example, if users had to enter an address in Edinburgh in Scotland, I didnt want them to spell it

Edinborough
or
Edinbraw
etc, etc

I wanted the County and Region to be DISPLAYED from this accurate table of data.

 

[music_al]

Thanks Gary

I opened up you file but it makes no sense to me and I cant find any code In there

I am a complete novice to this.

 

[Rich]

I have a definitive list of all towns and cities in the UK and their corresponding counties and regions. I bought this list from the Royal Mail/Post Office and it is all accurate, normalised and without errors.

One thing I wanted to avoid with this database was user TYPING in certain parts of the address which could have led to errors. For example, if users had to enter an address in Edinburgh in Scotland, I didnt want them to spell it

Edinborough
or
Edinbraw
etc, etc

I wanted the County and Region to be DISPLAYED from this accurate table of data.

Displaying the data and storing it twice are two separate entities, the second of which violates good db design. Search here for examples of cascading combo boxes, many have been posted here

 

[music_al]

Here is the offending file

If anyone can give me a bit of practical help, I will name a tree in my garden after you

Thank you

Allan