Does anyone have an example of a do.cmd.runsql insert using recordset fields? 
I have a table that I am populating with a few fields from a recordset and 
cannot seem to figure out how to do the insert.
Thanks.

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

  strSQL = "SELECT Field1, Field2, Field3 FROM MyTable"
  Set dbCurr = CurrentDb()
  Set rsCurr = dbCurr.OpenRecordset(strSQL)

  Do Until rsCurr.EOF
    strSQL = "INSERT INTO Table2 " & _
      "(NumericField, TextField, DateField) " & _
      "VALUES (" & rsCurr.Field1 & ", '" & rsCurr.Field2 & "', " & _
      Format(rsCurr.Field3, "\#yyyy\-mm\-dd hh\:nn\:ss\#") & ")"
    dbCurr.Execute strSQL, dbFailOnError
    rsCurr.MoveNext
  Loop

  rsCurr.Close
  Set rsCurr = Nothing
  Set dbCurr = Nothing


Note that I'm using the Execute method of the database object rather than 
DoCmd.RunSQL, for two reasons. First, it eliminates the annoying "You are 
about to insert ..." message, and second, by using the dbFailOnError 
parameter, a trappable error will be raised if something goes wrong 
executing the SQL statement. Note, too, that because I'm assuming that the 
second field is a Text field, there are quotes around its value: exagerated 
for clarity, that line of code is

      "VALUES (" & rsCurr.Field1 & ", ' " & rsCurr.Field2 & " ', " & _

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"AJ" <AJ@discussions.microsoft.com> wrote in message 
news:6DE5ECB7-A7C8-4CF8-AF56-915542624418@microsoft.com...
> Does anyone have an example of a do.cmd.runsql insert using recordset 
> fields?
> I have a table that I am populating with a few fields from a recordset and
> cannot seem to figure out how to do the insert.
> Thanks.