JackieEVSC
Registered User.
- Local time
- Today, 10:03
- Joined
- Feb 24, 2012
- Messages
- 26
I converted a macro to code. The macro had two queries ... the first one (Delete_tbl_Temp_Items), which deletes all entries in the tbl_Temp table. The second query appends the results of "CurrentTeachersSvcTag" to the tbl_Temp table. (I chose to delete the data and append the query results because I was having trouble with the date field transferring properly).
"CurrentTeachersSvcTag" pulls the necessary items for a particular school into the tbl_Temp table. Users can change the school at will, and often do.
The queries work perfectly, and after running them, the tbl_Temp ALWAYS has the correct data. After the export to excel, I open that spreadsheet and it's a crap shoot as to whether I get the old results or the results currently in the tbl_Temp file.
It appears that the existing "1-1_Teachers.xlsx" workbook is not always overwritten. Sometimes it is and I get the new results, sometimes it's not and I get the results from the last successful export. It's kind of a crap-shoot if the file actually exports, even though the message box pops up and tells me that the export was successful.
Any help identifying where I'm going wrong would be greatly appreciated!!
Here is the code:
Private Sub Command23_Click()
On Error GoTo mcr_ExportTeacherToExcel_Err
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete_tbl_Temp_Items", acViewNormal, acEdit
DoCmd.Close acQuery, "Delete_tbl_Temp_Items"
DoCmd.OpenQuery "CurrentTeacherSvcTag", acViewNormal, acEdit
DoCmd.Close acQuery, "CurrentTeacherSvcTag"
DoCmd.TransferSpreadsheet acExport, 10, "tbl_Temp", "1-1_Teachers.xlsx", True, ""
Beep
MsgBox "Results have been exported to your My Documents folder as a file named 1-1_Teachers.", vbOKOnly, ""
mcr_ExportTeacherToExcel_Exit:
Exit Sub
mcr_ExportTeacherToExcel_Err:
MsgBox Error$
Resume mcr_ExportTeacherToExcel_Exit
End Sub
"CurrentTeachersSvcTag" pulls the necessary items for a particular school into the tbl_Temp table. Users can change the school at will, and often do.
The queries work perfectly, and after running them, the tbl_Temp ALWAYS has the correct data. After the export to excel, I open that spreadsheet and it's a crap shoot as to whether I get the old results or the results currently in the tbl_Temp file.
It appears that the existing "1-1_Teachers.xlsx" workbook is not always overwritten. Sometimes it is and I get the new results, sometimes it's not and I get the results from the last successful export. It's kind of a crap-shoot if the file actually exports, even though the message box pops up and tells me that the export was successful.
Any help identifying where I'm going wrong would be greatly appreciated!!
Here is the code:
Private Sub Command23_Click()
On Error GoTo mcr_ExportTeacherToExcel_Err
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete_tbl_Temp_Items", acViewNormal, acEdit
DoCmd.Close acQuery, "Delete_tbl_Temp_Items"
DoCmd.OpenQuery "CurrentTeacherSvcTag", acViewNormal, acEdit
DoCmd.Close acQuery, "CurrentTeacherSvcTag"
DoCmd.TransferSpreadsheet acExport, 10, "tbl_Temp", "1-1_Teachers.xlsx", True, ""
Beep
MsgBox "Results have been exported to your My Documents folder as a file named 1-1_Teachers.", vbOKOnly, ""
mcr_ExportTeacherToExcel_Exit:
Exit Sub
mcr_ExportTeacherToExcel_Err:
MsgBox Error$
Resume mcr_ExportTeacherToExcel_Exit
End Sub