How to write an append query 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

 

[Graham Mandeno]

Hi Mark

You declare a string variable in your VBA code and then construct the SQL
string in your variable. Then assign CurrentDb to a database object
variable and use the Execute method to run the SQL command. For example:

Dim sSQL as String, db as DAO.Database
sSQL = "INSERT INTO LetterHistory ( PatientID, LetterName, LetterDate ) " _
& "SELECT PatientID, LetterName, Date() AS LetterDate " _
& "FROM rstDayFile;"
Set db = CurrentDb
db.Execute sSQL, dbFailOnError
MsgBox db.RecordsAffected & " letters have been recorded."

 

[Mr B]

In your event proceedure, define a string type variable:

Dim strSql as String

Then assign the sql statement (your query) to the string type variable:

strsql = "INSERT INTO LetterHistory ( PatientID, LetterName, LetterDate ) " _
& "SELECT PatientID, LetterName, Date() AS LetterDate " _
& "FROM rstDayFile;"

(please note that I have used string contatinating characters in the sql
statement so that hopefully it would not word warp.)

Then you cand use the following statement to run the query:

CurrentDb.Execute strSql

That should do it.