Problem with my update statement --getting error 3144

Question

I am getting Runtime error 3144 Syntax error in update statement when I am running this code and not sure what I am doing wrong. I have a list that I am wanting to write to another table (my main table) in my database. Below is the code:

Set lst = Me.lstTermEmpl

For Each item In lst.ItemsSelected
    CurrentDb.Execute "UPDATE tbl_Staffing SET Term_Heat_Ticket_Num = me.TxtTerm_Heat_Ticket_Num, Inactive_Status_Dt =lst.column(7), Termination_Reason_FK =lst.column(9), WHERE ID = " & _
        lst.Column(8) & ";", dbFailOnError

It is retruning values for the me.TxtTerm_Heat_Ticket_num and the Inactive_Status_dt but Not the Termination_Reason_FK or the ID.

Am I missing something in the code maybe?

This is my query for my list:

SELECT Tbl_Term_Employees.Term_ID, Tbl_Term_Employees.Date_of_TermEmplRequest,
Tbl_Term_Employees.NatGen_ID, Tbl_Term_Employees.FirstName, 
Tbl_Term_Employees.LastName, IIf([Term_Approval_Status]=0,"Pending SVM Approval","SVM Approved") AS TermApprovalStatus, 
Tbl_Termination_Reasons.Termination_Reason, 
Tbl_Term_Employees.Inactive_Status_Dt, Tbl_Term_Employees.ID_FK, 
Tbl_Term_Employees.Termination_Reason_FK, Tbl_Term_Employees.Team_Fk, 
Tbl_Term_Employees.Site_FK
FROM ((Tbl_Term_Employees INNER JOIN Tbl_Termination_Reasons ON Tbl_Term_Employees.Termination_Reason_FK = Tbl_Termination_Reasons.Termination_Reason_ID) INNER JOIN Tbl_Teams ON Tbl_Term_Employees.Team_Fk = Tbl_Teams.Team_ID) INNER JOIN Tbl_Site ON Tbl_Term_Employees.Site_FK = Tbl_Site.Site_ID
WHERE (((Tbl_Term_Employees.Team_Fk)=[forms]![FrmTermEmplList].[cboTeam]) AND ((Tbl_Term_Employees.Site_FK)=[forms]![FrmTermEmplList].[cboSite]) AND ((Tbl_Term_Employees.Term_Approval_Status)=[forms]![FrmTermEmplList].[txtTerm_Approval_Status]));

--Picture of my query in design view

Really don't see how can return any of the non-concatenated variable values. — June7
– June7, Commented May 5, 2020 at 21:54

June7 · Accepted Answer · 2020-05-06 15:45:05Z

1

Concatenate variable input. Reference to form control is a variable.
Semi-colon ";" is not needed.
Remove comma in front of WHERE.

Parameter for date/time type field needs # delimiters, text field parameter needs apostrophes.

    CurrentDb.Execute "UPDATE tbl_Staffing SET Term_Heat_Ticket_Num = '" & _
        Me.TxtTerm_Heat_Ticket_Num & "', Inactive_Status_Dt = #" & lst.column(7, item) & _
        "#, Termination_Reason_FK = " & lst.column(9, item) & " WHERE ID = " & lst.Column(8, item), dbFailOnError

edited May 6, 2020 at 15:45

answered May 5, 2020 at 21:41

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Sunglow08 Over a year ago

The code above is still showing null for my termination_reason_FK and 0 on my ID. Could it be the query for the list that is not returning the values of the columns?

Sunglow08 Over a year ago

The sql for my query is this: However I am not sure if I am pulling in the primary key from the tbl_Staffing which is ID.

Sunglow08 Over a year ago

SQL is too long for this box

Sunglow08 Over a year ago

The list box keeps giving me a null value for the Termination_Reason_FK even though the table the query for the list box runs from has a value in that field. What am I doing wrong here?

Sunglow08 Over a year ago

I added my query that produces my list.

|

Collectives™ on Stack Overflow

Problem with my update statement --getting error 3144

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related