Update Query

[mattc66 via AccessMonster.com]

Okay - I am trying to Update the tblCustomer Fld UsrDef1 with data from my
qryArCustAll SalespersonCode. When I run it I get a message that the
"Operation must use an updateable query". The qryArCustAll is a union query,
but the records I want to update are in a table. Can anyone explain what I am
doing wrong?


UPDATE tblCustomer, qryArCustAll SET tblCustomer.UsrDef1 = qryArCustAll.
SalesPersonCode;

Thanks
Matt

 

[John W. Vinson]

Okay - I am trying to Update the tblCustomer Fld UsrDef1 with data from my
qryArCustAll SalespersonCode. When I run it I get a message that the
"Operation must use an updateable query". The qryArCustAll is a union query,
but the records I want to update are in a table. Can anyone explain what I am
doing wrong?


UPDATE tblCustomer, qryArCustAll SET tblCustomer.UsrDef1 = qryArCustAll.
SalesPersonCode;

Thanks
Matt

You need to JOIN tblCustomer to qryArCustAll somehow, in order to tell Access
*which* record in tblCustomer should get updated with *which* record in
qryArCustAll. Your query right now is what's called a "Cartesian Join" - if
you have 1000 records in tblCustomer and 500 in qryArCustAll, it will give you
all 500,000 possible combinations! You better be glad it's ISN'T updateable!

My guess is that a query joining a UNION query isn't going to be updateable
(union queries aren't), but you could try joining it on some appropriate field
- and since I have no idea what's in your query, or how the tables are
related, I don't know what that field might be (CustomerID maybe...?)