Access 2010 SQL error in macro

Question

As a class project, I'm creating an Access 2010 database that tracks inventory. For the purpose of this question, there are two relevant tables, Assets and Actions, and one relevant form, Update Asset. My goal is to have a new record inserted into the Actions table every time someone updates a record in the Assets table. The new Actions record would contain a snapshot of the fields contained in the current record at the time of the save.

The Update Asset form has a macro called AuditLog attached to the After Update event. The macro uses the RunSQL command with the following parameters:

SQL Statement:

INSERT INTO Actions (AssetTag, LocationID,Slot,Status,Timestamp,TechID) ; 
SELECT(AssetTag,LocationID,Slot,Status,LastUpdated,TechID) FROM Assets WHERE 
   Assets!AssetTag = Me!AssetTag ;

Use Transaction: Yes

However, when I go to save record, I get the following error:

Syntax Error in INSERT INTO statement

Macro Name: AuditLog

Action Name: RunSQL

Arguments: INSERT INTO Actions (AssetTag, LocationID,Slot,Status,Timestamp,TechID) ; SELECT (AssetTag,LocationID,Slot,Status,LastUpdated,TechID) FROM Assets WHERE Assets!AssetTag = Me!AssetTag ;, Yes

Error Number: 2950

I have added the folder with the database to Access' list of trusted locations. Access is definitely not my strong point, so any help would be greatly appreciated.

Answer 1

It's not my strong point, either, but I bet that a semi colon is an end of expression/statement and that like SQL Server, that should be a compound statement (select...into)

Therefore, remove that first semi-colon.

INSERT INTO Actions (AssetTag, LocationID,Slot,Status,Timestamp,TechID) SELECT(AssetTag,LocationID,Slot,Status,LastUpdated,TechID) FROM Assets WHERE Assets!AssetTag = Me!AssetTag ;

Answer 2

Semicolons "complete" the statement. essentially what you're doing there is cutting off your statement at INSERT INTO Actions (AssetTag, LocationID,Slot,Status,Timestamp,TechID) ; <--

for future reference, you almost never need to use a semicolon in access. it may add it in for you, but to avoid stuff like this, just dont put it in.

Answer 3

As others have suggested, the semicolon is the culprit. If \ when you have future issues with your Access SQL statements you can paste the statement into the Access Query Designer. Doing so will let the Query Designer locate the issue for you (by highlighting the code with the syntax problem).