Consolidate

hyp527

Registered User.
Local time
Yesterday, 22:00
Joined
Jan 17, 2009
Messages
27
Hi,

I have this module and I'm trying to tweak it so it can add more columns, but everytime I run it, it's giving me error: Run-time error '2147217900: Invalid SQL statement; expected Delete, Insert, Procedure, Select or Update. Did I miss something? Thanks!

Public Sub s_runMe()
Dim cn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs1 = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset

rs1.Open "query1", cn, adOpenDynamic, adLockOptimistic
rs2.Open "tempQuery1", cn, adOpenDynamic, adLockOptimistic 'has 3 columns: Vendor Num, Vendor Name, Abbr'

rs1.MoveFirst
rs2.AddNew
rs2![Vendor Num] = rs1![Vendor Num]
rs2![Vendor Name] =rs2![Vendor Name]
rs2![Abbr] = rs1![Abbr]
rs1.MoveNext


Do While Not rs1.EOF
If rs1![Vendor Num] = rs2![Vendor Num] Then
rs2![Abbr] = rs2![Abbr] & " , " & rs1![Abbr]
Else
rs2.AddNew
rs2![Vendor Num] = rs1![Vendor Num]
rs2![Abbr] = rs1![Abbr]
End If

rs1.MoveNext

Loop

rs1.Close

MsgBox "Done!"

End Sub
 
The compiler is telling you it doesn't like either query1 or tempQuery1, depending on which line raises the error...so the place to start would be to look at those queries.

Regards,
Tim
 

Users who are viewing this thread

Back
Top Bottom