debugging query-forms

[News Boy]

I'd like to create a database to keep track of which routes go with which
carriers, and which substitute carriers are associated with them.

A carrier can have more than one route, but a route can have only one
carrier, and they change occasionally to frequently.

After a lot of trial and error, I have designed a simple database to test
Access' behavior. Forgetting the substitutes for the moment, I have
constructed 2 tables:

tblRoutes
ID (AutoNum), primary key
Route (Integer)
Carrier (Long Integer)

tblCarriers
ID (AutoNum)
LastName (String)
Account# (Long Integer), primary key

Carriers.Account# is related one-to-many to Routes.Carrier with referential
integrity and cascade update and cascade delete enabled, and tblRoutes will
naturally have more records than tblCarriers.

I'm trying to add records to the tables via a form with a query as the data
source:

SELECT Routes.ID AS Routes_ID, Routes.Route, Routes.Carrier, Carriers.ID AS
Carriers_ID, Carriers.LastName, Carriers.[Account Number]
FROM Routes RIGHT JOIN Carriers ON Routes.Carrier=Carriers.[Account Number]
ORDER BY Routes.Route;

This was built in the Access Wizard, as I am not familiar with SQL, yet, and
I've never used Access before and the help files are not very instructional
for design view.

If I try to enter a record for a route associated with a carrier already in
the table, I can type in the Routes.Carrier field and Access will
automatically fill in Carrier.LastName and Carrier.Account# boxes. But, if I
try to enter a new route with a carrier not yet in the table via the
Routes.Carrier field, I get the following error:

The Microsoft Jet database engine cannot find a record in the table
'Carriers' with the key matching field(s) 'carrier'.

.. . . meaning I need to enter the Carrier.Account# first. But, if I try to
enter a record via the Carrier.Account# field, and that carrier already
exists, I get an error message about not allowing duplicates in primary keys.

So, I need to have a single text box on the data entry form that can sense
which field to access: Routes.Carrier when the carrier already exists, and
Carrier.Account# when the carrier does not yet exist.

Is this accomplished via the query, or some switch in the form properties,
or how?

thank you for your help.

 

[louisjohnphillips]

I'd like to create a database to keep track of which routes go with which
carriers, and which substitute carriers are associated with them.  

A carrier can have more than one route, but a route can have only one
carrier, and they change occasionally to frequently.

After a lot of trial and error, I have designed a simple database to test
Access' behavior.  Forgetting the substitutes for the moment, I have
constructed 2 tables:

     tblRoutes
         ID (AutoNum), primary key
         Route (Integer)
         Carrier (Long Integer)

    tblCarriers
         ID (AutoNum)
         LastName (String)
         Account# (Long Integer), primary key

Carriers.Account# is related one-to-many to Routes.Carrier with referential
integrity and cascade update and cascade delete enabled, and tblRoutes will
naturally have more records than tblCarriers.

I'm trying to add records to the tables via a form with a query as the data
source:

SELECT Routes.ID AS Routes_ID, Routes.Route, Routes.Carrier, Carriers.ID AS
Carriers_ID, Carriers.LastName, Carriers.[Account Number]
FROM Routes RIGHT JOIN Carriers ON Routes.Carrier=Carriers.[Account Number]
ORDER BY Routes.Route;

This was built in the Access Wizard, as I am not familiar with SQL, yet, and
I've never used Access before and the help files are not very instructional
for design view.

If I try to enter a record for a route associated with a carrier already in
the table, I can type in the Routes.Carrier field and Access will
automatically fill in Carrier.LastName and Carrier.Account# boxes.  But, if I
try to enter a new route with a carrier not yet in the table via the
Routes.Carrier field, I get the following error:

The Microsoft Jet database engine cannot find a record in the table
'Carriers' with the key matching field(s) 'carrier'.

. . . meaning I need to enter the Carrier.Account# first.  But, if I try to
enter a record via the Carrier.Account# field, and that carrier already
exists, I get an error message about not allowing duplicates in primary keys.

So, I need to have a single text box on the data entry form that can sense
which field to access:  Routes.Carrier when the carrier already exists,and
Carrier.Account# when the carrier does not yet exist.

Is this accomplished via the query, or some switch in the form properties,
or how?

thank you for your help.

The table structure here is somewhat confusing. I expected to see a
schema in the form of:

tblRoutes
RouteId text(16) primary key,
RouteDescription text(32)

tblCarriers
CarrierID text(16) primary key,
LastName text(24)

tblCarrierRouteLink
RouteId text(16),
CarrierID text(16),
DateEffective datetime

The form would have a record source of tblCarrierRouteLink.

The tblCarrierRouteLink.RouteId would be restricted to the values in
tblRoutes.RouteID
and the tblCarrierRouteLink.CarrierID would be restricted to the
values in tblCarriers.CarrierID. These restrictions would be enforced
by a form that had the RouteID and CarrierID with a Row Source of
tblRoute and tblCarriers respectively.

The user's job would be to select the RouteId and the CarrierID
combinations from drop-down list boxes and supply the DateEffective in
a text edit field.

With this data structure, you can accommodate one carrier handling one
or more routes, idle carriers, unassigned routes and carriers who
assume route duties temporarily.

 

[News Boy]

Thank you for your suggestion. Sadly, it does not solve the problem. The
question is, how to enter a New carrier without getting an error message?

For example, if I want to assign a route to someone Not Yet in the data
base, I can't enter their key into tblCarrierRouteLink.CarrierID because they
don't exist in tblCarriers. So the control box on the form must reference
tblCarriers.CarrierID.

On the other hand, if I want to assign a route to someone who Is Already in
the data base, and the form's control box references tblCarriers.CarrierID, I
get an error message about duplicate primary keys. So the control box on the
form must Not reference tblCarriers.CarrierID.

There are many examples of similar structures - one supplier, many products;
one customer, many orders - in the Help files. They all use two tables -
Suppliers and Products, Customers and Orders. They all show a similar
situation as mine - one carrier, many routes. But None of them explain how
to set up a form that can add a record to one table but not the other. How
can I use forms to add an Order to a Customer, a Product to a Supplier, a
Route to a Carrier? That's the question.

Here is a sample of the data:

Routes
5
14
17
37
78
103

Carriers
johnny
jenny
julie
joey

Johnny has Route 5, Jenny has 14 and 37, Julie has 17, joey has 78, and
wants to take over 103 as well. It's very easy to go in and edit the tables
by hand, but How do I set up the form to point Joey to 103 without getting
the Duplicate Key error? And, what happens when Jerry (not in the database
yet) comes along wanting Route 105 (not in the database yet)? Again, I can
edit the tables by hand, but this method is far from idiot-proof, and other
users will not understand it.

How do I set up the form to add a record to one table but not the other,
when the form accesses, refers to, updates, both tables? That's the question.

I'd like to create a database to keep track of which routes go with which
carriers, and which substitute carriers are associated with them.

A carrier can have more than one route, but a route can have only one
carrier, and they change occasionally to frequently.

After a lot of trial and error, I have designed a simple database to test
Access' behavior. Forgetting the substitutes for the moment, I have
constructed 2 tables:

tblRoutes
ID (AutoNum), primary key
Route (Integer)
Carrier (Long Integer)

tblCarriers
ID (AutoNum)
LastName (String)
Account# (Long Integer), primary key

Carriers.Account# is related one-to-many to Routes.Carrier with referential
integrity and cascade update and cascade delete enabled, and tblRoutes will
naturally have more records than tblCarriers.

I'm trying to add records to the tables via a form with a query as the data
source:

SELECT Routes.ID AS Routes_ID, Routes.Route, Routes.Carrier, Carriers.ID AS
Carriers_ID, Carriers.LastName, Carriers.[Account Number]
FROM Routes RIGHT JOIN Carriers ON Routes.Carrier=Carriers.[Account Number]
ORDER BY Routes.Route;

This was built in the Access Wizard, as I am not familiar with SQL, yet, and
I've never used Access before and the help files are not very instructional
for design view.

If I try to enter a record for a route associated with a carrier already in
the table, I can type in the Routes.Carrier field and Access will
automatically fill in Carrier.LastName and Carrier.Account# boxes. But, if I
try to enter a new route with a carrier not yet in the table via the
Routes.Carrier field, I get the following error:

The Microsoft Jet database engine cannot find a record in the table
'Carriers' with the key matching field(s) 'carrier'.

. . . meaning I need to enter the Carrier.Account# first. But, if I try to
enter a record via the Carrier.Account# field, and that carrier already
exists, I get an error message about not allowing duplicates in primary keys.

So, I need to have a single text box on the data entry form that can sense
which field to access: Routes.Carrier when the carrier already exists, and
Carrier.Account# when the carrier does not yet exist.

Is this accomplished via the query, or some switch in the form properties,
or how?

thank you for your help.

The table structure here is somewhat confusing. I expected to see a
schema in the form of:

tblRoutes
RouteId text(16) primary key,
RouteDescription text(32)

tblCarriers
CarrierID text(16) primary key,
LastName text(24)

tblCarrierRouteLink
RouteId text(16),
CarrierID text(16),
DateEffective datetime

The form would have a record source of tblCarrierRouteLink.

The tblCarrierRouteLink.RouteId would be restricted to the values in
tblRoutes.RouteID
and the tblCarrierRouteLink.CarrierID would be restricted to the
values in tblCarriers.CarrierID. These restrictions would be enforced
by a form that had the RouteID and CarrierID with a Row Source of
tblRoute and tblCarriers respectively.

The user's job would be to select the RouteId and the CarrierID
combinations from drop-down list boxes and supply the DateEffective in
a text edit field.

With this data structure, you can accommodate one carrier handling one
or more routes, idle carriers, unassigned routes and carriers who
assume route duties temporarily.