DoCmd.RunSQL "INSERT INTO PartsChangeLog SELECT * FROM Parts "
This command works and drops in every record from the table "Parts" into the table "PartsChangeLog", but is not what I want. What I need, is for the command to make use of a "WHERE" condition too.
When the command is expanded to read:
DoCmd.RunSQL "INSERT INTO PartsChangeLog SELECT * FROM Parts " & _
"WHERE PartID=" & PartID
the code still runs, but I am presented with a message that tells me that Zero records will be updated. I concluded that the WHERE condition is not operating as I need it to.
The larger picture is that this code runs as an Event Procedure activated on the "Before Update" event of a form. The plan is that when a record on a form is changed, that the old values for the record are deposited in the PartsChangeLog table.
The form data is based on a query. The query makes use of my own Record Navigation control which include a Search box. Data entered in the Search Box is used in the Criteria of the query as
Like "*" & [forms]![Create / Edit Parts]![PartIDSearch] & "*" - Which is listed in the criteria of the PartID record and in the Or field against Item Description.
The problem occurs whether the Search box is used or not and I have tried with a new form without these additional Navigation controls too with the same result.
I am occasionally shown a message that reads "Run-time error '3075': Syntax Error (missing operator) in query expression "PartID=20-virt 8/25'.
In that example, "20-virt 8/25" is the PartID of the record being amended.
In table "Parts", PartID is a Primary Key, is Short Text, and Indexed=Yes (No duplicates)
In table PartsChangeLog, PartID is not a Primary Key, is Short Text and Indexed=Yes (Duplicates OK)
I think my WHERE condition is formatted incorrectly, but I can't work out how it should be.
Can one of you good folk shine some light on this please.