CODEWITHSUNDEEP
sqlms-access
Tucker Black
Tucker Black

Reputation: 21

MS Access Update Query Keeps Asking for "Parameter Value"

I will try to simplify the situation as best as I can... I work with a team of field service technicians. We share a lot of our tools and equipment between us because SOMEONE doesnt want to spend money on us. I am using MS Access to develope a way to track who has what tool or specific piece of equipment out of a pool of about 2000+ items. I have got the process figured out down to one last step. It gos like this:

  1. Tech opens a form that shows current location/ownership of all 2000+ items, which is pulled off a SharePoint List and inserted into a local table.
  2. Tech makes changes to local table.
  3. When Tech is done, the Sharepoint List gets updated FROM the local table (This is where I am stuck).

I have followed every tutorial, walk through, and work around that I can understand. But every time I run the Query, Access asks me for the "Parameter Value" of each field I am trying to update. Mind you, everything I have found shows an example of how to update one field in a table. I am trying to update 5 fields per row. When I let Access build the SQL for this it looks like:

UPDATE Table1 INNER JOIN Table2 
ON Table1.[Asset ID] = Table2.[Asset ID] 
SET Table1.Department = Table2.[Department], Table1.[Cal Status] = Table2. 
[Cal Status], Table1.[Return Date] = Table2.[Return Date], Table1.Comments = 
Table2.[Comments], Table1.[Cal Due] = Table2.[Cal Due], Table1.Active = 
Table2.[Active];

The fields are not misspelled, I already checked and double checked that.

I have seen some comments about how Access has problems with UPDATE. But I am self taught with everything I do and I cant switch to something else until I understand this. I just started with Access and SQL at the begining of this week, so please be patient with me.

EDIT

UPDATE Assets
SET Assets.Department = AssetsSharePoint.[Department],
Assets.[Cal Status]   = AssetsSharePoint.[Cal Status],
Assets.[Return Date]  = AssetsSharePoint.[Return Date],
Assets.Comments       = AssetsSharePoint.[Comments],
Assets.[Cal Due]      = AssetsSharePoint.[Cal Due],
Assets.Active         = AssetsSharePoint.[Active]
FROM Assets
INNER JOIN AssetsSharePoint ON Assets.[Asset ID] = AssetsSharePoint.[Asset 
ID];

This gives me a "Syntax Error. Missing Operator" Then Highlights FROM

EDIT 2

If I cut it down to 1 field, leave out the FROM, and include WHERE it works. I dont want to make 5 seperate queries for this. But I guess I am going to have to....

Upvotes: 2

Views: 914

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522302

Your syntax is off, and it should be UPDATE ... SET ... FROM:

UPDATE t1
INNER JOIN Table2 t2
    ON t1.[Asset ID] = t2.[Asset ID]
SET
    t1.Department    = t2.[Department],
    t1.[Cal Status]  = t2.[Cal Status],
    t1.[Return Date] = t2.[Return Date],
    t1.Comments      = t2.[Comments],
    t1.[Cal Due]     = t2.[Cal Due],
    t1.Active        = t2.[Active]

Upvotes: 0

Rene
Rene

Reputation: 1093

Try this:

UPDATE Assets, AssetsSharePoint
SET Assets.Department = AssetsSharePoint.[Department],
Assets.[Cal Status]   = AssetsSharePoint.[Cal Status],
Assets.[Return Date]  = AssetsSharePoint.[Return Date],
Assets.Comments       = AssetsSharePoint.[Comments],
Assets.[Cal Due]      = AssetsSharePoint.[Cal Due],
Assets.Active         = AssetsSharePoint.[Active]
WHERE Assets.[Asset ID] = AssetsSharePoint.[Asset ID];

Neater statement:

UPDATE Assets INNER JOIN AssetsSharePoint ON Assets.[Asset ID] = AssetsSharePoint.[Asset ID]
SET Assets.Department = AssetsSharePoint.[Department],
Assets.[Cal Status]   = AssetsSharePoint.[Cal Status],
Assets.[Return Date]  = AssetsSharePoint.[Return Date],
Assets.Comments       = AssetsSharePoint.[Comments],
Assets.[Cal Due]      = AssetsSharePoint.[Cal Due],
Assets.Active         = AssetsSharePoint.[Active]

Upvotes: 0

Related Questions