I am currently making a case tracker within MS Access and cannot get the below UPDATE code that is located in a subform to work. I've tried Googling this but to no avail.

All the data is stored in a main table called tblCaseTrackerLoad. This table has 98 columns in it. I also have a subform called tblBulkAssign subform which reads from 5 columns from within tblCaseTrackerLoad with the following query (which is placed in the Record Source fields of the Data tab):

SELECT tblCaseTrackerLoad.[Select], tblCaseTrackerLoad.[Case Status], tblCaseTrackerLoad. 
[Customer Name], tblCaseTrackerLoad.Account_Key, tblCaseTrackerLoad.[Case_Owner] FROM 
tblCaseTrackerLoad WHERE (((tblCaseTrackerLoad.[Case Status])="To be assigned"));

Here are the field types:

tblCaseTrackerLoad.[Select] is a Yes/No field
tblCaseTrackerLoad.[Case Status] is a Short Text field
tblCaseTrackerLoad.[Customer Name] is a Short Text field
tblCaseTrackerLoad.Account_Key is a Large Number field
tblCaseTrackerLoad.[Case_Owner] is a Short Text field

The above code works perfectly and when the subform is loaded, I can see the rows of data that where the "Case Status = To Be Assigned". This is shown in a table format.

What I am trying to make happen in when the subform is loaded, the user ticks boxes in the Select field (as many or as few as they need to select), then they click a button which loads a different form containing all the users that the case could be assigned to. A user is selected and then the code executes and is supposed to assign the cases to the users by way of an update statement to update the table "tblCaseTrackerLoad".

Here is my code for the execution of the UPDATE statement:

strSQL = "UPDATE tblCaseTrackerLoad SET [Case_Owner] = " & "'" & 
txtAssignToFromAssignToUserForm & "'" & ", [Case Status] = ""Assigned"", tblCaseTrackerLoad. 
[Date Assigned] = " & Assigned Timestamp & ", WHERE [Select] = Yes AND [Case_Owner] IS NULL;"

Debug. Print strSQL

' Execute the SQL Command
DoCmd.RunSQL strSQL

When ran, the error I get says "Run-Time error '3144': Syntax error in UPDATE statement."

Here is the output of Debug. Print

UPDATE tblCaseTrackerLoad SET [Case_Owner] = 'Craig Hogan', [Case Status] = "Assigned", tblCaseTrackerLoad.[Date Assigned] = 10/02/2022 07:36:13, WHERE [Select] = Yes AND [Case_Owner] IS NULL;

For reference:

1. txtAssignToFromAssignToUserForm is the value that is passed from the form to select who to assign the cases to, back into this subform. This box is successfully receiving the users name.

2. AssignedTimeStamp is a bit of code just above the UPDATE query which says Dim AssignedTimeStamp As Date AssignedTimeStamp = Now

Although the tblCaseTrackerLoad.[Date Assigned] field is not referenced within the subform, it is still a valid field in the main table tblCaseTrackerLoad and is of type "Date/Time".

It's bound to be some silly little thing that I haven't done but I have spent approx. 4 hours trying to sort this out now and cannot seem to find the solution.

Please can someone educate me on where I have gone wrong?

CodePudding user response：

You have a comma before the "WHERE" clause. Remove that comma.

UPDATE tblCaseTrackerLoad 
SET [Case_Owner] = 'Craig Hogan', [Case Status] = "Assigned", tblCaseTrackerLoad.[Date Assigned] = 10/02/2022 07:36:13
WHERE [Select] = Yes AND [Case_Owner] IS NULL;