How to run an append Query in VBA on a recordset

[magicdds-]

I have some code running on an event procedure. At one point in the code I
need to run an append query. If the data to be appended to the table
LETTERHISTORY was in another table, I would just have an append query and
DoCmd.OpenQuery "QueryName".

However, my data is in a recordset that was created in the code of the event
procedure.

The name of the recordset is "rstDayFile"

The Query, if made by the QUERY BUILDER would look like this:

INSERT INTO LetterHistory ( PatientID, LetterName, LetterDate )
SELECT PatientID, LetterName, Date() AS LetterDate
FROM rstDayFile;

I don't know how to type this query into the VBA code of the event procedure
or how to get it to run to append the single record to the LETTERHISTORY
table.
There are 3 fields in the LETTERHISTORY table: PatientID, LetterName,
LetterDate.

Thanks for your help.
Mark

 

[Ken Sheridan]

Mark:

Build a value list of the values in the recordset's single row, plus the
current date, and include this in the SQL statement to be executed:

Dim dbs as DAO.Database
Dim rstDayFile As DAO.Recordset
Dim strSQL AS String

Set dbs = CurrentDb

' code to establish recordset

With rstDayFile
strSQL = " INSERT INTO LetterHistory " & _
"(PatientID, LetterName, LetterDate) " & _
"VALUES(" & .Fields("PatientID") & ",""" & _
.Fields("LetterName") & """,#" & _
Format(VBA.Date,"mm/dd/yyyy") & "#)"
End With

dbs.Execute strSQL, dbFailOnError

Ken Sheridan
Stafford, England

 

[magicdds-]

I modified it to this:

strSQL = " INSERT INTO LetterHistory " & _

"(PatientID, LetterName, LetterDate) " & _
"VALUES(" & rstDayFile.Fields("PatientID") & ",""" & _
rstDayFile.Fields("LetterName") & """,#" & _
Format(VBA.Date,"mm/dd/yyyy") & "#)"

and it worked great.
Thank you very much for your help
Mark