update sql error: too few parms

[gammaman]

my udpate sql is throwing too few parms error. Not sure why.

Public Sub doCompare()
Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("fred_backup")
Set rs2 = CurrentDb.OpenRecordset("fred_backup_old")
columnCount = CurrentDb.TableDefs("fred_backup").Fields.count - 41
strSQL = ""
strSQL2 = ""
For I = 0 To columnCount - 1
If I = 0 Then
strSQL = "fred_backup." & "[" & rs.Fields(I).Name & "]" & "=" & "fred_backup_old." & "[" & rs2.Fields(I).Name & "]"
Else
strSQL2 = strSQL2 & " AND " & "fred_backup." & "[" & rs.Fields(I).Name & "]" & "=" & "fred_backup_old." & "[" & rs2.Fields(I).Name & "]" & vbCrLf
End If
Next I
db.Execute "UPDATE fred_backup SET Previous ='Y' where " & strSQL & strSQL2 & ""
End Sub

If I do a MsgBox on the SQL it looks like this

 

[pr2-eugin]

Why are you not JOINING the two tables? The problem is because, you are using two tables, but have not actually SELECTed them

 

[gammaman]

OH s**t I cannot believe I missed that. LOL.

 

[pr2-eugin]

Good luck !

 

[gammaman]

Ok, so no more error but there are not any rows being updated. See attached msgbox.

"UPDATE fred_backup SET Previous ='Y'  where exists(select 'X' from fred_backup_old where " & strSQL & strSQL2 & ")"

 

[pr2-eugin]

Scratch everything. Now, explain what is that you want to do.

 

[gammaman]

Let f = field.

I am trying to update column "Previous" to 'Y' in tb1 where

tb1.f1 = tb2.f1 and
tb1.f2 = tb2.f2 and

.....

 

[pr2-eugin]

Why is there no unique identified between tbl1 and tbl2? Why are you using = comparisons? Should you not be using JOIN so that you will get a set of data, on which you can apply a WHERE?

You have not explained anything BTW.

 

[vbaInet]

It would be wise to Debug.Print that SQL string and paste it into a query to see if you get any results!! And show us the SQL string too.