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, ""
DoCmd.SetWarnings True
mcr_ExportTeacherToExcel_Exit:
Exit Sub
mcr_ExportTeacherToExcel_Err:
DoCmd.SetWarnings True
MsgBox Error$
Resume mcr_ExportTeacherToExcel_Exit
End Sub
A word about your code:
- remove everything that is not required; most parameters have a default so if you leave them out the default will occur; the less writing the better, the code will look cleaner and easier to quickly follow
- NEVER leave controls on forms without a proper name, change them always to a word(s) in English that is clearly understandable, especially if you are going to use them in VBA
- NEVER EVER set warnings off without switching them back on again. I cannot tell you the problems this can cause in your database. Believe me - been there, done that, got the scars to prove it!