linking records

[Guest]

I am new to access. I currently have 3 different tables (Trips), (Countries)
and (Cities). In the table view it shows all the tables linked. I have 3
forms built using only 2. I would like to add the trip info on one form page
then hit a command button to go to the next form which has the country form
with the city subform.
As of right now I enter my info on the Trip form and hit the command button
to go to the Country form and it still shows the info from the last record.

My question is: How can I go from the trip form to the country form and
show a new record and still link it with Trip info.

Thank you

Cordell

 

[Guest]

Cordell:

Can each trip be to more than one city? If so you need another table
CitiesVisited say which models the many-to-many relationship between Trips
and Cities by having two foreign key columns e.g. CityID and TripID which
reference the primary keys of Cities and Trips. You'd then select each city
for a trip in a continuous view subform (based on the CitiesVisited table) in
a form based on the Trips table, the subform being linked to the parent form
on TripID and with a combo box bound to the CityID field.

If each trip can be to only one city then you'd simply have a combo box on
the Trips form bound to a foreign key CityID field in Trips.

In either of the above scenarios, as the Cities table would have a foreign
key field such as CountryID which references the primary key of Countries you
can show the country in an unbound text box on the form (or subform) with an
expression such as this as its ControlSource to look up the Country:

=DLookup("Country", "Countries", "CountryID = " & DLookup("CountryID",
"Cities", "CityID = " & [cboCityID]))

Where cboCityID is the combo box bound to the CityID field.

You can also do it more efficiently by including the Country in the
RowSource of the CityID combo box like so:

SELECT CityID, City, Country
FROM Cities INNER JOIN Countries
ON Cities.CountryID = Countries.CountryID
ORDER BY City;

The ControlSource for the unbound Country text box would then be:

=cboCityID.Column(2)

The Column proiperty is zero-based so Column(2) is the third column of the
combo box's RowSource.

Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken

Thank you for your help.

Cordell

Cordell:

Can each trip be to more than one city? If so you need another table
CitiesVisited say which models the many-to-many relationship between Trips
and Cities by having two foreign key columns e.g. CityID and TripID which
reference the primary keys of Cities and Trips. You'd then select each city
for a trip in a continuous view subform (based on the CitiesVisited table) in
a form based on the Trips table, the subform being linked to the parent form
on TripID and with a combo box bound to the CityID field.

If each trip can be to only one city then you'd simply have a combo box on
the Trips form bound to a foreign key CityID field in Trips.

In either of the above scenarios, as the Cities table would have a foreign
key field such as CountryID which references the primary key of Countries you
can show the country in an unbound text box on the form (or subform) with an
expression such as this as its ControlSource to look up the Country:

=DLookup("Country", "Countries", "CountryID = " & DLookup("CountryID",
"Cities", "CityID = " & [cboCityID]))

Where cboCityID is the combo box bound to the CityID field.

You can also do it more efficiently by including the Country in the
RowSource of the CityID combo box like so:

SELECT CityID, City, Country
FROM Cities INNER JOIN Countries
ON Cities.CountryID = Countries.CountryID
ORDER BY City;

The ControlSource for the unbound Country text box would then be:

=cboCityID.Column(2)

The Column proiperty is zero-based so Column(2) is the third column of the
combo box's RowSource.

Ken Sheridan
Stafford, England

I am new to access. I currently have 3 different tables (Trips), (Countries)
and (Cities). In the table view it shows all the tables linked. I have 3
forms built using only 2. I would like to add the trip info on one form page
then hit a command button to go to the next form which has the country form
with the city subform.
As of right now I enter my info on the Trip form and hit the command button
to go to the Country form and it still shows the info from the last record.

My question is: How can I go from the trip form to the country form and
show a new record and still link it with Trip info.

Thank you

Cordell