How to do an append query to a table from 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

 

[rocco]

try this

currentdb.execute("INSERT INTO LetterHistory ( PatientID, LetterName,
LetterDate ) values(" & rstDayFile.fields(0) & "," & rstDayFile.fields(1), &
",#" & rstDayFile.fields(2) & "#)")

this will add to your table just the first record of your rstDayFile
recordset. If you need to add more you have to go through a loop.

 

[magicdds-]

Thanks for your help.
Mark

try this

currentdb.execute("INSERT INTO LetterHistory ( PatientID, LetterName,
LetterDate ) values(" & rstDayFile.fields(0) & "," & rstDayFile.fields(1), &
",#" & rstDayFile.fields(2) & "#)")

this will add to your table just the first record of your rstDayFile
recordset. If you need to add more you have to go through a loop.