MS Access sql - Update query syntax

Question

Seems a small issue with an sql update query. But I can not get my head around this issue. Not very much acquainted with sql.

Based on a selection, selected rows from a table (7DARphases) are copied and inserted to another table (7tblDAR). And the Project ID (PRJID column) for all the inserted rows should be updated with a fixed value (PID) taken from the form. Issue is: I am facing syntax issue in the part where the fixed value needs to be inserted in the PRJID column of added rows.

Code is:

Set dbs = CurrentDb

PID = Me.PRJID.Value

sqlstr = "INSERT INTO 7tblDAR (Phase, Deliverable, Link_PIM_temp, Link_PIM_WB, Description, Accept_criteria, Deliv_type, TollgateID, Workstream, Accountable, ClientRACI) SELECT [7DARphases].Phase, [7DARphases].Deliverable, [7DARphases].Link_PIM_temp, [7DARphases].Link_PIM_WB, [7DARphases].Description, [7DARphases].Accept_criteria, [7DARphases].Deliv_type, [7DARphases].TollgateID, [7DARphases].Workstream, [7DARphases].Accountable, [7DARphases].ClientRACI FROM 7DARphases WHERE ((([7DARphases].SolType) Like '*2PP*')) ORDER BY [7DARphases].Phase, [7DARphases].Deliverable;"

sqlUpdt = "update 7tblDAR set PRJID =  '" & Me.PRJID.Value & "' from 7tblDAR where tblDAR.PRJID = """""


dbs.Execute sqlstr, dbFailOnError

dbs.Execute sqlUpdt, dbFailOnError

The 'sqlstr' works fine and inserts rows. But 'sqlUpdt' gives error:
"Run-time error 3075: Syntax error (missing operator) in query expression ..."

Can you please help me out with this.

Plus, if possible, can you suggest to perform this action in one query itself.

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-02-25T12:39:23

Why not put the value in when you insert the values?

sqlstr = "INSERT INTO 7tblDAR (Phase, Deliverable, Link_PIM_temp, Link_PIM_WB, Description, Accept_criteria, Deliv_type, TollgateID, Workstream, Accountable, ClientRACI, PRJID)
    SELECT . . .,
           '" & Me.PRJID.Value & "' 
    WHERE [7DARphases].SolType Like '*2PP*')
    ORDER BY [7DARphases].Phase, [7DARphases].Deliverable;"

This saves the trouble of having to update it later.

MS Access sql - Update query syntax

1 Answers