Update Query problem

[gator44]

I am trying to use an Access update query to change values in one table to
the values of another table using a macro to do so. I get the error “you
cannot update ‘expression’ ; field not updateable†for the following:

UPDATE [Account Balance] INNER JOIN [Update Balance Record]
ON [Account Balance].[Invoice#] = [Update Balance Record].[Invoice#]
SET [Account Balance].Balance_Due = [Update Balance Record].[New Balance];

The update works fine when activating the query thru a macro manually, but
will not complete when it is part of another macro that contains other
queries.

Would appreciate suggestions....Thanx

 

[Ken Snell \(MVP\)]

ACCESS / Jet often cannot run update queries when the primary key of the
table being updated is not in the query's data set. In these cases, build a
query that uses DLookup to get the desired update value:

UPDATE [Account Balance]
SET [Account Balance].Balance_Due =
DLookup("New Balance", "Update Balance Record", "[Invoice#] = '" &
[Account Balance].[Invoice#] & "'")

In the above query, I'm assuming that the field Invoice# is a text field. If
it's a numeric field, then the query would be this:

UPDATE [Account Balance]
SET [Account Balance].Balance_Due =
DLookup("New Balance", "Update Balance Record", "[Invoice#] = " &
[Account Balance].[Invoice#])

 

[gator44 via AccessMonster.com]

ACCESS / Jet often cannot run update queries when the primary key of the
table being updated is not in the query's data set. In these cases, build a
query that uses DLookup to get the desired update value:

UPDATE [Account Balance]
SET [Account Balance].Balance_Due =
DLookup("New Balance", "Update Balance Record", "[Invoice#] = '" &
[Account Balance].[Invoice#] & "'")

Thanks Ken…Tried text version as suggested and received error:

Microsoft Office Acecss didn't update 1 field(s) due to a type conversion
failure., 0 record(s) due to key violations ,0 record(s) due to ...

I made sure that primary field in both tables was Invoice# (text) and that
both Balance fields were currency. The update query showed the field to be
updated as normal, but when run (disregarding the error and continuing) the
field to be updated was completely erased. Looks like I’m close but what’s
the conversion failure?

 

[Ken Snell \(MVP\)]

The error message ("type conversion failure") indicates that the data types
are not the same for the [Account Balance].Balance_Due and the [Update
Balance Record].[New Balance] fields. It also might indicate that you're
trying to store too large or too small a value into the [Account
Balance].Balance_Due field (though this seems unlikely for your scenario).

Create and run this query and let's see what the data values are for the
intended update -- are they what you expect?

SELECT [Account Balance].*,
DLookup("New Balance", "Update Balance Record", "[Invoice#] = '" &
[Account Balance].[Invoice#] & "'") AS NewBalanceValue
FROM [Account Balance];

--

Ken Snell
<MS ACCESS MVP>

ACCESS / Jet often cannot run update queries when the primary key of the
table being updated is not in the query's data set. In these cases, build
a
query that uses DLookup to get the desired update value:

UPDATE [Account Balance]
SET [Account Balance].Balance_Due =
DLookup("New Balance", "Update Balance Record", "[Invoice#] = '" &
[Account Balance].[Invoice#] & "'")

Thanks Ken.Tried text version as suggested and received error:

Microsoft Office Acecss didn't update 1 field(s) due to a type conversion
failure., 0 record(s) due to key violations ,0 record(s) due to ...

I made sure that primary field in both tables was Invoice# (text) and that
both Balance fields were currency. The update query showed the field to be
updated as normal, but when run (disregarding the error and continuing)
the
field to be updated was completely erased. Looks like I'm close but what's
the conversion failure?

 

[Karen]

I'm having the same problem. I can run these update queries manually and
they update all the records; however, when I run them using code, it only
updates a few records. I've used the suggested DLookup, but it is still not
updating all of my records.

This was my first update query:

UPDATE (tblA INNER JOIN tblB ON tblA. Item = tblB. Item) INNER JOIN tblC ON
tblB.COO = tblC.COO
SET tblA.MADE = [tblC]![ MadeIn];

After reading these posts, I made two queries with DLookups:

UPDATE tblA SET tblA.Made = DLookUp("COO","tblB","[tblB]![Item] = '" &
[Item] & "'");

UPDATE tblA SET tblA.Made = DLookUp("MadeIn","tblC","[tblC]![COO] = '" &
[Made] & "'");

Any recommendations are appreciated.

 

[Ken Snell \(MVP\)]

Tell us more about how you're running them "by code". Also, both of your
second queries are updating the same field, is that correct?