I have some VBA code that sits behind a button on a form. The code runs a query to insert results into a temporary table, export the data from the table to an Excel file with a defined worksheet name, and then delete the temporary table. The query itself uses parameters that are provided by the user from the form prior to them clicking on the button. When I click the button, I receive the following error: "Too few parameters. Expected 1."
The VBA code I am using is as follows:
Private Sub Command34_Click()
On Error GoTo Err_Command34_Click
Dim strSQL As String
Dim strTable As String
Dim strElementTable As String
Dim strRemLifeFrom As String
Dim strRemLifeTo As String
Dim strContractor As String
Dim strExcelFile As String
Dim strWorkSheet As String
strTable = "tblTemp"
strElementTable = [Forms]![PROGFORM]![ElementTable]
strRemLifeFrom = [Forms]![PROGFORM]![RemLifeFrom]
strRemLifeTo = [Forms]![PROGFORM]![RemLifeTo]
strContractor = [Forms]![PROGFORM]![Contractor]
strExcelFile = "C:\Temp\ProgrammeExport\"
strExcelFile = strExcelFile & strContractor & "_"
strWorkSheet = strElementTable
strSQL = "SELECT WorkPlanner.UPRN, WorkPlanner.[Block Ref], WorkPlanner.[Flat Number], WorkPlanner.[Block/House name], WorkPlanner.[House Number], WorkPlanner.[Street 1], WorkPlanner.[Street 2], WorkPlanner.Locality, WorkPlanner.Town, WorkPlanner.Postcode, "
strSQL = strSQL & strElementTable & ".[Rem Life], " & strElementTable & ".[Rem Life Year], " & strElementTable & ".[Year Programmed] "
strSQL = strSQL & "INTO " & strTable
strSQL = strSQL & " FROM " & strElementTable & " INNER JOIN WorkPlanner ON " & strElementTable & ".UPRN = WorkPlanner.UPRN "
strSQL = strSQL & "WHERE WorkPlanner.UPRN IN (SELECT UPRN FROM " & strElementTable & " WHERE (Contractor = " & strContractor & " AND ([Rem Life] BETWEEN " & strRemLifeFrom & " AND " & strRemLifeTo & ")))"
CurrentDb.Execute strSQL
DoCmd.TransferSpreadsheet acExport, 8, strTable, strExcelFile, True, strWorkSheet
DoCmd.DeleteObject acTable, strTable
Exit_Command34_Click:
Exit Sub
Err_Command34_Click:
MsgBox Err.Description
Resume Exit_Command34_Click
End Sub
I'm not sure what this error means or what part of my code is failing.