Auto-fill field based on another field

[lucrosus]

I have the following tables:

PROVINCE
id
province

CITY
id
province
city

COMPANY
id
company name
company address
company city
company province

This is what I did:

In Table PROVINCE, I entered several provinces.

In Table CITY, I used lookup on the field province to make a drop down menu showing the provinces I have entered, and entered the cities.

In Table COMPANY, How do I make it so that when I choose a city, in the field company province, the corresponding province will be filled up automatically?

Thanks!

 

[anvil19]

Hi lucrosus

To do this you use Auto-lookup. It's quite coplicated to set up but it works very well.

Firstly, you need to create a one-to-many relationship between the two tables. Start a query and add the tables to the query builder with the City table on the left and the COMPANY table on the right.

Link the Province field in the CITY table to the Company.Province field of the COMPANY table. Then add the following fields to the query (Just double-click them) :[COMPANY.id], [COMPANY.company name], [COMPANY.company address], [COMPANY.company city] and CITY.Province. Save the query.

Now when you add data to the COMPANY table the province field will be filled automatically from the CITY table.

try that!

anvil19

 

[lucrosus]

I tried but it did not work. Is there something I am missing? I clicked on query, design query, and did as you instructed. After saving the query called query1, went into the company, but after typing the city name, nothing changed in the province name.

 

[anvil19]

Hey again lucrosus

Just a thought. Did you create a one-to-many relationship between the tables with the CITY table on the left and the COMPANY table on the right? This is vital to the autolookup process.

anvil19

 

[lucrosus]

This is what I did:

Created the following tables:

PROVINCE
Province

CITY
City
Province

COMPANY
Company Name
Company Address
Company City
Company Province
Company Phone 1
Company Phone 2
Company Fax
Company Mobile Phone
Company Email
Company Homepage
Company Notes

In Table PROVINCE, I entered 2 records.

In Table CITY, I clicked on Field Province, clicked on Lookup Tab, selected Combo Box, on Row Source, opened the SQL Query Builder, selected Table PROVINCE, double clicked on Province.

So far, so good. When I opened Table City, I entered City Name, on the next field - Province - there is a drop down box which I can select the name of the Province.

This is where I am stucked.

First of all, how do I create one to many relationship? Is this the one located under Tools/Relationship?

Please bear in mind I am not familiar with the terms used in Microsoft Access as I have never used it before.

THANK YOU SO MUCH!

 

[lucrosus]

Anyone? Please? Thanks!

 

[Abulafia]

First of all, your structure needs a few tweaks.

tblProvinces
ProvinceID (Autonumber) - Primary key
Province (text)

tblCities
CityID (autonumber) - Primary Key
City (text)
ProvinceID (number) - Foreign key

tblCompanies
CompanyID (autonumber) - Primary key
CompanyName (text)
CompanyAddress (text)
CityID (number) - Foreign Key


You don't need the province in the Company table as you have already related the province in the City table. This would only duplicate data and create a non-key dependancy (a violation of Third Normal Form).

In the example I've done before (pay attention to the field names and also the names of the tables - they follow an accepted standard) join the two fields called CityID to each other in the relationships window (select enfore Referential Integrity - Delete Cascaded records). Repeat this action for the ProvinceID fields.

This is properly normlised now.

 

[lucrosus]

I finally got it! THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!