Access Auto Population

Question

This is probably the dumbest question ever asked on here but I am a total and utter novice at Access.  this is my first ever foray.

Im buidling a DB from scratch and have built multiple tables. For this example, Imagine I have built Master (this will be the table where I input into), Continent, Country.

In the master table, I can easily lookup both the continent and the country from those tables to restrict what data i want entered into the master.  However, I want to be a little more dynamic.

Lets say I chose the country Germany.  I would want to autopopulate the continent of Europe.  Of course for Singapore i would autopopulate Asia.  And so on.  Its a VLookup basically in Excel speak.

Thanks for any help.  I dont want to get into VBA so I hope there is another solution.

Rgds

Rob

Answer 1

I had assumed you already have your tables set up as Apollo described, but then I got the combo boxes mixed up and thought you were selecting the continents first, but I had the names all mixed up, so forget what said that confused you

If you do not have any other data in the tables, then I think the one table that Scott described is all you need.

If you do have other data in those two tables, then use a query to join the table so the result looks like Scott's table.

Answer 2

Actually, I think you need a different approach. What I would do is a single lookup table like so:

tluCountries

CountryID (Primary Key Autonumber)

Country

Continent

Then in your data table, I would have a SINGLE combobox to select the Country. This combo would have the following properties:

RowSource: SELECT CountryID, Country FROM tluCountries ORDER BY Country:

Bound Column: 1

Column Count: 2:

Column Widths: 0";2"

You then select the country from the combo.This will store the ID of the country as a foreign key.

Since you know the continent once you know the country there is NO need for duplicating that info anywhere. If you want to do a report then you base the report on a query where you join your master table to tluCountries on CountryID. You can then add the County and Continent columns to your query. If you need to DISPLAY both the country and continent on your form there are ways to do that. In fact I have written a blog entry on that subject (see my sig for the address).

---

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2010 Blog: http://scottgem.wordpress.com Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 3

Thanks so much for the fast reply.  Im ok upto the part in bold italic...  I have no idea how to do this.  Apologies, I did say I was a newbie :-)

Instead, create a form bound to the table (or much better, a query based on the table).  Use two combo box controls, the first for the country and the second to the continent.  The country combo box can be based on the country table or, if you want the countries to be sorted, on a query to the country table.  The second combo box need to be based on a query to the continent table where thecountry ID field uses a criteria like:   =Forms!theform.[the country combo box] OR Forms!theform.[the country combo box] Is Nullyou'll need a line of VBA code in the xountry combo box's AfterUpdate event procedure AND in the form's Current event:   Me.[the continent combo box].Requery

Rob

Answer 4

You should consider having two tables to get started. 

Table1:  Continents

Table2: Countries

Define your tables as follows:

Continents

ContinentID   AutoNumber (PK) = Primary Key

ContinentName  Text

Countries

CountryID    AutoNumber (PK) = Primary Key

CountryName   Text

Country_FK_ContinentID   Number - Long Integer (FK) = foreign key to reference Continents table

To see the results on a form you can do the following:

Create a combo box: Countries

For the property Row Source, create a query that looks up Countries and Continents

    SELECT [CountryName], [ContinentName] FROM Countries INNER JOIN Continents ON [Countries].[Country_FK_ContinentID] = [Continents].[ContinentID] ORDER BY [CountryName];

Set the combo box properties:

    Limit to List = True

    Columns = 2

    Column Widths = 1, 0

Create a text box: Continents

Set the Control Source = Countries.Column(1)

Now when you choose a country from your combo box, the text box will display the value of the second column in your combo box.

No coding required!

Answer 5

First some advice.  Do NOT use lookup fields in a table. See http://www.mvps.org/access/tencommandments.htm for reasons why that is a bad thing to do.

Instead, create a form bound to the table (or much better, a query based on the table).  Use two combo box controls, the first for the country and the second to the continent.  The country combo box can be based on the country table or, if you want the countries to be sorted, on a query to the country table.  The second combo box need to be based on a query to the continent table where the country ID field uses a criteria like:

   =Forms!theform.[the country combo box] OR Forms!theform.[the country combo box] Is Null

you'll need a line of VBA code in the xountry combo box's AfterUpdate event procedure AND in the form's Current event:

   Me.[the continent combo box].Requery