Update Query - Error 3113

[AFSSkier]

I have a two Update Queries. The 1st one ran & the 2nd one didn't. They both
have worked last week. The 2nd one has worked well until today. When run
it, I get the following error "Can't update '(expression)'; field not
updatable. The help is not help "Can't update <field name>; field not
updatable. (Error 3113). It will display in datasheet view, but will not run.

I need a human's Help on this one!

 

[Jerry Whittle]

Please post the SQL of the update query.

 

[John Vinson]

I have a two Update Queries. The 1st one ran & the 2nd one didn't. They both
have worked last week. The 2nd one has worked well until today. When run
it, I get the following error "Can't update '(expression)'; field not
updatable. The help is not help "Can't update <field name>; field not
updatable. (Error 3113). It will display in datasheet view, but will not run.

I need a human's Help on this one!

Please post the SQL of the query. Not all queries are in fact
updateable, but there are various reasons why they might not be!

SOMETHING must have changed since yesterday though. Have you tried
Compact and Repair? Or you may want to copy the SQL text of the query
into a new query, and then compact and repair to recreate the query
from scratch.

John W. Vinson[MVP]

 

[AFSSkier]

Here is the SQL:

UPDATE [COMBINED DATA] LEFT JOIN CLPPrice ON [COMBINED DATA].ITEMNO =
CLPPrice.ITEMNO SET [COMBINED DATA].PK = [CLPPrice]![PACK], [COMBINED
DATA].SIZE = [CLPPrice]![SIZE], [COMBINED DATA].[DESC] = [CLPPrice]![DESC],
[COMBINED DATA].[Store CLP UNIT COST] = [CLPPrice]![UNIT COST], [COMBINED
DATA].[whs CASE COST W/FRT] = [CLPPrice]![COST+FRT], [COMBINED DATA].[whs
CASE SELL] = [CLPPrice]![SELL], [COMBINED DATA].[AIMS RETLUPC] =
[CLPPrice]![RETLUPC], [COMBINED DATA].AFSCAT = [CLPPrice]![AFSCAT], [COMBINED
DATA].[VNDR-NAME] = [CLPPrice]![VNDR-NAME], [COMBINED DATA].DISCCD =
[CLPPrice]![DISCCD], [COMBINED DATA].[Cat Mgr] = [CLPPrice]![CM], [COMBINED
DATA].[DEPT NUM] = [CLPPrice]![DEPT], [COMBINED DATA].Department =
[CLPPrice]![DEPARTMENT], [COMBINED DATA].[FUT-DC-DT] =
[CLPPrice]![FUT-DC-DT], [COMBINED DATA].[FUT-DC-CD] = [CLPPrice]![FUT-DC-CD],
[COMBINED DATA].VENDOR = [CLPPrice]![VENDOR], [COMBINED DATA].REPODAY =
[CLPPrice]![REPODAY], [COMBINED DATA].[TD-AMT] = [CLPPrice]![TD-AMT],
[COMBINED DATA].[TD-END-DT] = [CLPPrice]![TD-END-DT], [COMBINED
DATA].[RETL-OI] = [CLPPrice]![RETL-OI], [COMBINED DATA].[RETL-OI-END-DT] =
[CLPPrice]![RETL-OI-END-DT], [COMBINED DATA].[TMPR-AMT] =
[CLPPrice]![TMPR-AMT], [COMBINED DATA].[TMPR-END-DT] =
[CLPPrice]![TMPR-END-DT], [COMBINED DATA].PVTLBL = [CLPPrice]![PVTLBL],
[COMBINED DATA].DISC = [CLPPrice]![DISC];

 

[Jerry Whittle]

LEFT JOIN. You probably need an equal/inner join or a subquery.

Also the join doesn't seem to be on the primary key of the COMBINED DATA
table. You have a [COMBINED DATA].PK field that might be getting updated more
than once. Access won't allow that if the PK field is the primary key.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL:

UPDATE [COMBINED DATA] LEFT JOIN CLPPrice ON [COMBINED DATA].ITEMNO =
CLPPrice.ITEMNO SET [COMBINED DATA].PK = [CLPPrice]![PACK], [COMBINED
DATA].SIZE = [CLPPrice]![SIZE], [COMBINED DATA].[DESC] = [CLPPrice]![DESC],
[COMBINED DATA].[Store CLP UNIT COST] = [CLPPrice]![UNIT COST], [COMBINED
DATA].[whs CASE COST W/FRT] = [CLPPrice]![COST+FRT], [COMBINED DATA].[whs
CASE SELL] = [CLPPrice]![SELL], [COMBINED DATA].[AIMS RETLUPC] =
[CLPPrice]![RETLUPC], [COMBINED DATA].AFSCAT = [CLPPrice]![AFSCAT], [COMBINED
DATA].[VNDR-NAME] = [CLPPrice]![VNDR-NAME], [COMBINED DATA].DISCCD =
[CLPPrice]![DISCCD], [COMBINED DATA].[Cat Mgr] = [CLPPrice]![CM], [COMBINED
DATA].[DEPT NUM] = [CLPPrice]![DEPT], [COMBINED DATA].Department =
[CLPPrice]![DEPARTMENT], [COMBINED DATA].[FUT-DC-DT] =
[CLPPrice]![FUT-DC-DT], [COMBINED DATA].[FUT-DC-CD] = [CLPPrice]![FUT-DC-CD],
[COMBINED DATA].VENDOR = [CLPPrice]![VENDOR], [COMBINED DATA].REPODAY =
[CLPPrice]![REPODAY], [COMBINED DATA].[TD-AMT] = [CLPPrice]![TD-AMT],
[COMBINED DATA].[TD-END-DT] = [CLPPrice]![TD-END-DT], [COMBINED
DATA].[RETL-OI] = [CLPPrice]![RETL-OI], [COMBINED DATA].[RETL-OI-END-DT] =
[CLPPrice]![RETL-OI-END-DT], [COMBINED DATA].[TMPR-AMT] =
[CLPPrice]![TMPR-AMT], [COMBINED DATA].[TMPR-END-DT] =
[CLPPrice]![TMPR-END-DT], [COMBINED DATA].PVTLBL = [CLPPrice]![PVTLBL],
[COMBINED DATA].DISC = [CLPPrice]![DISC];

--
Thanks, Kevin

Please post the SQL of the query. Not all queries are in fact
updateable, but there are various reasons why they might not be!

SOMETHING must have changed since yesterday though. Have you tried
Compact and Repair? Or you may want to copy the SQL text of the query
into a new query, and then compact and repair to recreate the query
from scratch.

John W. Vinson[MVP]