Why can't I update this query

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO system.
There is a Vendor table, with a related Purchase Order (PO) table. If it
matters for purposes of this question, there is a PO_Details table for line
items on the PO, which serves as the junction table between the Vendor table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation, as
tblPO is related to tblPO_Details, which as I mentioned is a junction table.
The article is ambiguous in that it doesn't specify whether the problem lies
with a query that includes the three table involved in the many-to-many, or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not see
the solution.

BruceM wrote:

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO system.
There is a Vendor table, with a related Purchase Order (PO) table. If it
matters for purposes of this question, there is a PO_Details table for line
items on the PO, which serves as the junction table between the Vendor table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation, as
tblPO is related to tblPO_Details, which as I mentioned is a junction table.
The article is ambiguous in that it doesn't specify whether the problem lies
with a query that includes the three table involved in the many-to-many, or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not see
the solution.


Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better. The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

--
Marsh
MVP [MS Access]

To add to Marsh's excellent comments, if you think about it logically, how
will Access know which table to update? It's simply too complex to make a
decision and, thus, it leaves it in a non-updatable status.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"BruceM" wrote in message
...
This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system. There is a Vendor table, with a related Purchase Order (PO) table.
If it matters for purposes of this question, there is a PO_Details table
for line items on the PO, which serves as the junction table between the
Vendor table and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long
as I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that
changing the join type has accomplished nothing. An article in
Microsoft's MSDN2 library contains the following as a reason for a
non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation,
as tblPO is related to tblPO_Details, which as I mentioned is a junction
table. The article is ambiguous in that it doesn't specify whether the
problem lies with a query that includes the three table involved in the
many-to-many, or just any query with three or more tables, one of which is
part of a many-to-many relationship. If it applies to my difficulties I
do not see the solution.

"Marshall Barton" wrote in message
...
BruceM wrote:

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system.
There is a Vendor table, with a related Purchase Order (PO) table. If it
matters for purposes of this question, there is a PO_Details table for
line
items on the PO, which serves as the junction table between the Vendor
table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long
as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that
changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation,
as
tblPO is related to tblPO_Details, which as I mentioned is a junction
table.
The article is ambiguous in that it doesn't specify whether the problem
lies
with a query that includes the three table involved in the many-to-many,
or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not see
the solution.


Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better.

I never intended to edit the phone number from the PO form, only to display
it. The display-only fields were to be in locked controls. Do I understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have to
say I always thought multi-table Record Source queries were a strength of
Access/Jet, not a liability.

The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

Tried that. It didn't work.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it is a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past occasions.

--
Marsh
MVP [MS Access]

Thanks for the reply. If I was able to see the logic I would have had no
need to ask the question, but it eludes me. I identified fields as being
from specific tables, so I don't see the ambiguity. In answer to your
question, when there is only one field of a particular name in the dataset I
don't see why Access has a problem with identifying it. However, I doubt
it's a matter of using unique field names, as I suspect the situation would
have been exactly the same even if VendorID had a different name in all
three tables. I would like to understand, but can't quite grasp it.
By the way, the user-level security is working nicely (albeit in a test
environment for now), thanks to your assistance in an earlier thread. I
still need to work out using a form to check for the password, as you
described, but in that case I do see the logic based on your explanation, so
I expect it will work OK.

"Lynn Trapp" wrote in message
...
To add to Marsh's excellent comments, if you think about it logically, how
will Access know which table to update? It's simply too complex to make a
decision and, thus, it leaves it in a non-updatable status.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"BruceM" wrote in message
...
This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system. There is a Vendor table, with a related Purchase Order (PO)
table. If it matters for purposes of this question, there is a PO_Details
table for line items on the PO, which serves as the junction table
between the Vendor table and a Products table.
The Vendor table also has a related table for phone numbers. Therein
lies my problem. The PO form needs to contain Vendor information
including the phone number, and PO information such as PO_Date and
PO_Number. As long as I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down
the query for reasons I have not been able to discover. I do know that
changing the join type has accomplished nothing. An article in
Microsoft's MSDN2 library contains the following as a reason for a
non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation,
as tblPO is related to tblPO_Details, which as I mentioned is a junction
table. The article is ambiguous in that it doesn't specify whether the
problem lies with a query that includes the three table involved in the
many-to-many, or just any query with three or more tables, one of which
is part of a many-to-many relationship. If it applies to my difficulties
I do not see the solution.

Well, basically, try running the following update statement and see what
error you get.


Update tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
Set tblPhone.Phone = "111-111-1111"
Where tblPhone.MainPhone = True;

I believe the error will be something to the effect that you need to specify
the table name for update.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"BruceM" wrote in message
...
Thanks for the reply. If I was able to see the logic I would have had no
need to ask the question, but it eludes me. I identified fields as being
from specific tables, so I don't see the ambiguity. In answer to your
question, when there is only one field of a particular name in the dataset
I don't see why Access has a problem with identifying it. However, I
doubt it's a matter of using unique field names, as I suspect the
situation would have been exactly the same even if VendorID had a
different name in all three tables. I would like to understand, but can't
quite grasp it.
By the way, the user-level security is working nicely (albeit in a test
environment for now), thanks to your assistance in an earlier thread. I
still need to work out using a form to check for the password, as you
described, but in that case I do see the logic based on your explanation,
so I expect it will work OK.

"Lynn Trapp" wrote in message
...
To add to Marsh's excellent comments, if you think about it logically,
how will Access know which table to update? It's simply too complex to
make a decision and, thus, it leaves it in a non-updatable status.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"BruceM" wrote in message
...
This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system. There is a Vendor table, with a related Purchase Order (PO)
table. If it matters for purposes of this question, there is a
PO_Details table for line items on the PO, which serves as the junction
table between the Vendor table and a Products table.
The Vendor table also has a related table for phone numbers. Therein
lies my problem. The PO form needs to contain Vendor information
including the phone number, and PO information such as PO_Date and
PO_Number. As long as I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down
the query for reasons I have not been able to discover. I do know that
changing the join type has accomplished nothing. An article in
Microsoft's MSDN2 library contains the following as a reason for a
non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my
situation, as tblPO is related to tblPO_Details, which as I mentioned is
a junction table. The article is ambiguous in that it doesn't specify
whether the problem lies with a query that includes the three table
involved in the many-to-many, or just any query with three or more
tables, one of which is part of a many-to-many relationship. If it
applies to my difficulties I do not see the solution.

BruceM wrote:
"Marshall Barton" wrote
BruceM wrote:

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system.
There is a Vendor table, with a related Purchase Order (PO) table. If it
matters for purposes of this question, there is a PO_Details table for
line
items on the PO, which serves as the junction table between the Vendor
table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long
as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that
changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation,
as
tblPO is related to tblPO_Details, which as I mentioned is a junction
table.
The article is ambiguous in that it doesn't specify whether the problem
lies
with a query that includes the three table involved in the many-to-many,
or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not see
the solution.


Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better.

I never intended to edit the phone number from the PO form, only to display
it. The display-only fields were to be in locked controls. Do I understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have to
say I always thought multi-table Record Source queries were a strength of
Access/Jet, not a liability.

The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

Tried that. It didn't work.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it is a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past occasions.


I thought you were trying to modify the phone field. Other
than that issue, I think I just added to your confusion.
I have more form record source queries than I can count that
use multiple tables and as long as only the primary table's
fields are being updated, there should be no problem. The
problem I was trying to address is in editing a many side
table's fields.

Just guessing now, but maybe something else going on. Can
you find anything unusual about the phones table? Try
joining that table just to the vendor table and see if the
vendor data can be modified.

--
Marsh
MVP [MS Access]

Thanks for the reply. I guess I didn't make it clear that I was just trying
to display, not edit, the vendor data. I'll have a command button or a
click event for the controls bound to the vendor data if the user needs to
go to the main vendor form, in which the phone numbers are displayed in a
list box. Phone numbers are added/edited by means of a pop-up form.
However, only a few users will be able to edit vendor data at all, so most
of the front ends will include just a message box to inform users they can't
change the data.
A query that combines tblVendor and tblPhone can be edited, as can a query
that combines tblPO and tblVendor. FWIW, the only indexed fields are the PK
and FK fields, and VendorName from tblVendor. It makes no difference if I
add fields individually or by using the asterisk to add all fields, nor if I
remove the parameter. I tried breaking the relationship between tblPO and
tblPO_Details (which is the junction table between tblPO and tblProducts) so
that a many-to-many relationship is completely out of the mix.
In order to get the phone numbers, which were in Phone1 and Phone2 fields in
tblVendor, into their own table I based an append query on a union query. I
wondered if this could have created some anomalies, so I create a new
tblPhone with no data. When I added tblPO, tblVendor, and the new tblPhone
to a query it returned no records because tblPhone had none, so I changed
the join between tblVendor and tblPhone to a left join, after which I could
see all of the PO records, but I still couldn't edit any fields. This tells
me that when I prepare the report that is the actual printed PO I can
include all three tables in its record source, but need to use the left join
in order to display a record regardless of whether there is a phone number.
However, this doesn't get me any closer to displaying the phone number in a
form based on an updatable query, unless I use a subform.
If a query that includes all three tables is supposed to be non-updatable I
can accept that, but if I should be able to edit the PO fields but cannot I
would like to discover why, and thereby learn something new.
I may end up using a subform to display the vendor information, including
phone, since it will be simpler to prevent edits or additions in the subform
than in individual controls, but if you have any thoughts or explanations
about why I can't edit any of the fields when all three tables are in the
query, I would be interested in hearing about it.

"Marshall Barton" wrote in message
...
BruceM wrote:
"Marshall Barton" wrote
BruceM wrote:

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system.
There is a Vendor table, with a related Purchase Order (PO) table. If
it
matters for purposes of this question, there is a PO_Details table for
line
items on the PO, which serves as the junction table between the Vendor
table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein
lies
my problem. The PO form needs to contain Vendor information including
the
phone number, and PO information such as PO_Date and PO_Number. As long
as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down
the
query for reasons I have not been able to discover. I do know that
changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my
situation,
as
tblPO is related to tblPO_Details, which as I mentioned is a junction
table.
The article is ambiguous in that it doesn't specify whether the problem
lies
with a query that includes the three table involved in the many-to-many,
or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not
see
the solution.


Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better.

I never intended to edit the phone number from the PO form, only to
display
it. The display-only fields were to be in locked controls. Do I
understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have to
say I always thought multi-table Record Source queries were a strength of
Access/Jet, not a liability.

The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

Tried that. It didn't work.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in
the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it is
a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past occasions.


I thought you were trying to modify the phone field. Other
than that issue, I think I just added to your confusion.
I have more form record source queries than I can count that
use multiple tables and as long as only the primary table's
fields are being updated, there should be no problem. The
problem I was trying to address is in editing a many side
table's fields.

Just guessing now, but maybe something else going on. Can
you find anything unusual about the phones table? Try
joining that table just to the vendor table and see if the
vendor data can be modified.

--
Marsh
MVP [MS Access]

Thanks for the reply. I tried the experiment, and was unable to update when
I used the asterisks (the error message was atypically clear on that point),
but when I added individual fields instead, the update was successful for
those vendors who had a PO record (a PO had been created to buy something
from the vendor).
However, as I mentioned to Marshall, adding individual fields to the query
still resulted in a non-updatable query.
As I also mentioned to Marshall, I have no intention of modifying the phone
number or any other vendor information from the PO form. My only goal is to
display the vendor information (in locked text boxes). When I think about
it, there is probably no compelling reason to have that information (other
than VendorName, which appears on the PO form as the visible column in a
combo box bound to VendorID in tblPO) appear on the form, as long as it
shows up on the report. However, I would still like to solve the puzzle, or
if it is expected behavior I would like to understand it.

"Lynn Trapp" wrote in message
...
Well, basically, try running the following update statement and see what
error you get.


Update tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
Set tblPhone.Phone = "111-111-1111"
Where tblPhone.MainPhone = True;

I believe the error will be something to the effect that you need to
specify the table name for update.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"BruceM" wrote in message
...
Thanks for the reply. If I was able to see the logic I would have had no
need to ask the question, but it eludes me. I identified fields as being
from specific tables, so I don't see the ambiguity. In answer to your
question, when there is only one field of a particular name in the
dataset I don't see why Access has a problem with identifying it.
However, I doubt it's a matter of using unique field names, as I suspect
the situation would have been exactly the same even if VendorID had a
different name in all three tables. I would like to understand, but
can't quite grasp it.
By the way, the user-level security is working nicely (albeit in a test
environment for now), thanks to your assistance in an earlier thread. I
still need to work out using a form to check for the password, as you
described, but in that case I do see the logic based on your explanation,
so I expect it will work OK.

"Lynn Trapp" wrote in message
...
To add to Marsh's excellent comments, if you think about it logically,
how will Access know which table to update? It's simply too complex to
make a decision and, thus, it leaves it in a non-updatable status.

--

Lynn Trapp
Microsoft MVP (Access)
www.ltcomputerdesigns.com


"BruceM" wrote in message
...
This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID) INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system. There is a Vendor table, with a related Purchase Order (PO)
table. If it matters for purposes of this question, there is a
PO_Details table for line items on the PO, which serves as the junction
table between the Vendor table and a Products table.
The Vendor table also has a related table for phone numbers. Therein
lies my problem. The PO form needs to contain Vendor information
including the phone number, and PO information such as PO_Date and
PO_Number. As long as I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down
the query for reasons I have not been able to discover. I do know that
changing the join type has accomplished nothing. An article in
Microsoft's MSDN2 library contains the following as a reason for a
non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my
situation, as tblPO is related to tblPO_Details, which as I mentioned
is a junction table. The article is ambiguous in that it doesn't
specify whether the problem lies with a query that includes the three
table involved in the many-to-many, or just any query with three or
more tables, one of which is part of a many-to-many relationship. If
it applies to my difficulties I do not see the solution.

I can't explain it. Barring something funny about your
query, it should work. How you created the table and the
relationships should have no effect as long as the query is
properly constructed, which it looks like it is.

Someone else may have an idea, but the only straw I can
think of to grasp at is some kind of corruption. Make a
backup copy of the mdb file, then try compacting the db,
recreating the query from scratch or even creating a fresh
mdb and importing everything.
--
Marsh
MVP [MS Access]


BruceM wrote:
Thanks for the reply. I guess I didn't make it clear that I was just trying
to display, not edit, the vendor data. I'll have a command button or a
click event for the controls bound to the vendor data if the user needs to
go to the main vendor form, in which the phone numbers are displayed in a
list box. Phone numbers are added/edited by means of a pop-up form.
However, only a few users will be able to edit vendor data at all, so most
of the front ends will include just a message box to inform users they can't
change the data.
A query that combines tblVendor and tblPhone can be edited, as can a query
that combines tblPO and tblVendor. FWIW, the only indexed fields are the PK
and FK fields, and VendorName from tblVendor. It makes no difference if I
add fields individually or by using the asterisk to add all fields, nor if I
remove the parameter. I tried breaking the relationship between tblPO and
tblPO_Details (which is the junction table between tblPO and tblProducts) so
that a many-to-many relationship is completely out of the mix.
In order to get the phone numbers, which were in Phone1 and Phone2 fields in
tblVendor, into their own table I based an append query on a union query. I
wondered if this could have created some anomalies, so I create a new
tblPhone with no data. When I added tblPO, tblVendor, and the new tblPhone
to a query it returned no records because tblPhone had none, so I changed
the join between tblVendor and tblPhone to a left join, after which I could
see all of the PO records, but I still couldn't edit any fields. This tells
me that when I prepare the report that is the actual printed PO I can
include all three tables in its record source, but need to use the left join
in order to display a record regardless of whether there is a phone number.
However, this doesn't get me any closer to displaying the phone number in a
form based on an updatable query, unless I use a subform.
If a query that includes all three tables is supposed to be non-updatable I
can accept that, but if I should be able to edit the PO fields but cannot I
would like to discover why, and thereby learn something new.
I may end up using a subform to display the vendor information, including
phone, since it will be simpler to prevent edits or additions in the subform
than in individual controls, but if you have any thoughts or explanations
about why I can't edit any of the fields when all three tables are in the
query, I would be interested in hearing about it.

"Marshall Barton" wrote.
BruceM wrote:
"Marshall Barton" wrote
BruceM wrote:

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO
system.
There is a Vendor table, with a related Purchase Order (PO) table. If
it
matters for purposes of this question, there is a PO_Details table for
line
items on the PO, which serves as the junction table between the Vendor
table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein
lies
my problem. The PO form needs to contain Vendor information including
the
phone number, and PO information such as PO_Date and PO_Number. As long
as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down
the
query for reasons I have not been able to discover. I do know that
changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my
situation,
as
tblPO is related to tblPO_Details, which as I mentioned is a junction
table.
The article is ambiguous in that it doesn't specify whether the problem
lies
with a query that includes the three table involved in the many-to-many,
or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not
see
the solution.


Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better.

I never intended to edit the phone number from the PO form, only to
display
it. The display-only fields were to be in locked controls. Do I
understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have to
say I always thought multi-table Record Source queries were a strength of
Access/Jet, not a liability.

The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

Tried that. It didn't work.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in
the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it is
a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past occasions.


I thought you were trying to modify the phone field. Other
than that issue, I think I just added to your confusion.
I have more form record source queries than I can count that
use multiple tables and as long as only the primary table's
fields are being updated, there should be no problem. The
problem I was trying to address is in editing a many side
table's fields.

Just guessing now, but maybe something else going on. Can
you find anything unusual about the phones table? Try
joining that table just to the vendor table and see if the
vendor data can be modified.