Where in Update Statement Not Working--Updating All Records Instead

[rousseaud]

Hello,

I could use a little help trying to figure this one out--I would greatly appreciate it.

I'm using an UPDATE statement in VBA to update a table. Typically, this statement should only affect one record. For some reason, it's updating all the records in the table. I'm assuming my WHERE statement is wrong. Here's a short version of the code (all variables in the SQL string are integers:

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim recint As Integer
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbLocation

cn.Open strConnection

strSQL = "UPDATE tblTest SET Permission ='" & Permission & "', Order1 ='" & Order1 & "' WHERE CustID = RecID"

cn.Execute strSQL
cn.Close
Set cn = Nothing
Set rs = Nothing

[NeoPa]

If [Permission] and [Order1] are both integers then the quotes (') you have around the values are superfluous. Only string literal values require quotes. I doubt that would explain all records being updated though.

That would pertain to the WHERE clause specifically. I assume that [CustID] and [RecID] are both fields in the table and are both numeric. I think we need to see some data though and an explanation of exactly what happens against that data - clearly explained.

[rousseaud]

Thank youfor the reply, NeoPa. I went back to the table and I did add a numeric field called [RecID] for debugging purposes. I removed this field and everything appears to work.

Thanks again.

[NeoPa]

I'm glad it's now working, but confused how it can be if there is no longer a [RecID] field to match the SQL code in your WHERE clause. Probably no issue that I don't understand, but weird nevertheless.