SQL WHERE - No results when using WHERE

[GAJITCS]

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.

[davegri]

try

"Where PartsID='" & PartID & "'"

[Minty]

Debugging 101 - inspect the values after you have created them: Your problem is that your partID appears to be a string
Use a variable to hold your SQL string.

Dim strSQl as String

strSQL = "INSERT INTO PartsChangeLog SELECT * FROM Parts WHERE PartID = '" & Me.PartID & "'"

Debug.Print strSQL 

CurrentDb.Execute strSQL, DbSeeChanges

[GAJITCS]

Debugging 101 - inspect the values after you have created them: Your problem is that your partID appears to be a string
Use a variable to hold your SQL string.

Dim strSQl as String

strSQL = "INSERT INTO PartsChangeLog SELECT * FROM Parts WHERE PartID = '" & Me.PartID & "'"

Debug.Print strSQL 

CurrentDb.Execute strSQL, DbSeeChanges

Thank you Minty. I am new to this at the moment and haven't developed any sort of debugging stuff. Enclosing as you have '" & Me.PartID & "'" seems to be close to what Davegri went on to suggest too. I added your code to the Event procedure and nothing seemed to happen, but I fully expect I have put it in the wrong place.

[GAJITCS]

try

"Where PartsID='" & PartID & "'"

Thank you Davegri. Your code was inserted and worked.

But I don't understand what made the difference or why?

If you have time to explain, I would appreciate it.

[Welshgasman]

Thank you Davegri. Your code was inserted and worked.

But I don't understand what made the difference or why?

If you have time to explain, I would appreciate it.

Because your PartID is text and not numeric.

[Minty]

To compare or insert strings, a string value need to be escaped by a delimiter. That delimiter is normally a double quote " e.g.

"Fred"

However you are already in the middle of creating a string so you can't easily use another " mark so we use another acceptable delimiter the single quote ' e.g.

'Fred'

So your concatenated string becomes

"INSERT INTO PartsChangeLog SELECT * FROM Parts WHERE PartID = 'Fred' "

Dates need the # delimiter around them e.g. "Where MyDate = #2023-05-14#"

Numbers don't need a delimiter.

Does that help?