Hi I'm getting errors on the INS

[supermaestro]

Hi

I'm getting errors on the INSERT part of this query in the WHILE loop (data type error) Would appreciate any help...

Private Sub Update_Click()
Dim rs As New ADODB.Recordset

Set rs = CurrentProject.Connection.Execute("SELECT contacts.[Contact ID], transactions.[Transaction ID], transactions.[Last Transaction], transactions.[Transaction Date], transactions.[Transaction Note], transactions.[Next Action Date], transactions.[Next Transaction] FROM contacts INNER JOIN transactions ON contacts.[Contact ID] = transactions.[Contact ID] WHERE transactions.[Next Action Date]=Date() AND transactions.[Next Transaction]='Mailshot';&quot

Do While Not rs.EOF
CurrentProject.Connection.Execute ("INSERT into transactions ([Contact ID],[Last Transaction],[Transaction Date],[Transaction Note],[Next Action Date],[Next Transaction]) values ('" & rs("Contact ID&quot & "','Mailshot',Now(),'" & Transaction_Notes & "','" & Next_Action_Date & "','Call');&quot
rs.MoveNext
Loop
rs.Close


Cheers

 

[Quehay]

Personally I hate looking at embedded quotes and use CHR(39) / CHR(34) to wrap string literals in SQL statements.
Another gratuitous comment is avoid using spaces in table & field names (you may have inherited these)--it makes life much easier.

If you put your SQL into a variable before executing you can debug more easily. Lift it from the debug window and try to run it from query window--it should be more obvious what the hitch is.

Do While Not rs.EOF

strSQL = "insert into transactions ([Contact ID],[Last Transaction],[Transaction Date],[Transaction Note],[Next Action Date],[Next Transaction]) values (" & _
chr(34) & rs(field) & chr(34) & "," etc. (you do the typing).

Debug.print strSQL
CurrentProject.Connection.Execute ("INSERT into transactions ([Contact ID],[Last Transaction],[Transaction Date],[Transaction Note],[Next Action Date],[Next Transaction]) values ('" & rs("Contact ID&quot & "','Mailshot',Now(),'" & Transaction_Notes & "','" & Next_Action_Date & "','Call');&quot
rs.MoveNext
Loop
rs.Close