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
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