I am trying to move the results of a parameter query to a formatted excel file. I use 2 command buttons on a form, the first to run the query (works) the second to export the data to excel (driving me insane)
I am having problems having the information go to different work pages based on some criteria in the query. (specifically there are 5 possible sites in the access file, each entry has one, I want each site to have its own tab in the excel file)
I assume it is a syntax error of some sorts that I am getting.
If anyone here has time to look over it and notice what I am doing wrong It is greatly appreciated.
If I have posted this in the wrong area please let me know, and I apologize for the inconvenience.
thanks
Sean
This is my code:
Private Sub exportcmd_Click()
On Error GoTo err_Handler
Const cTabTwo As Byte = 1
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rstOutput As DAO.Recordset
Dim sOutput As String
Dim sqlString As String
Dim shtArray As Variant
Dim siteArray As Variant
Dim I As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("paraquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rstOutput = qdf.OpenRecordset
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
sOutput = CurrentProject.Path & "\salary recovery template.xls"
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
appExcel.Visible = True
'Take the records in the current form and dump to Excel
siteArray = Array("PickA", "PickB", "Darl", "Bruce", "Other", "Overhead")
shtArray = Array("PickA549", "PickB349", "Darl348", "Bruce347", "Other", "Overhead")
For I = 0 To 4
sqlString = "SELECT * FROM paraquery WHERE jobsite='" & siteArray(I) & "'"
Set rstOutput = CurrentDb.OpenRecordset(sqlString)
Set wks = wbk.Worksheets.Add
wks.Name = shtArray(I)
wks.Range("A11").CopyFromRecordset rstOutput
Next
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
'You may want to save the workbook here
Set wbk = Nothing
Set appExcel = Nothing
rstOutput.Close
Set rstOutput = Nothing
DoCmd.Hourglass False
' Exit exportcmd_Click
err_Handler:
ExportRequest = Err.Description
' Me.lblMsg.Caption = Err.Description
'Resume exit_Here
End Sub
I am getting the error 3061- too few parameters, expected 6.
the debugger is highliteing this line
Set rstOutput = CurrentDb.OpenRecordset(sqlString)
my sqlsatement looks like this
SELECT [Employee_ID].[Recovery No], [Employee_ID].[Account Name], [Employee_ID].[Gen Ledg Acnt No], [Timesheettable1].[JobNumbers], [Employee_ID].[Type], [Employee_ID].[unknown], [Employee_ID].[Ref No], [Timesheettable1].[Employee], [Employee_ID].[Rate], [Timesheettable1].[Hours Worked], [Timesheettable1].[Hours Paid]
FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON [Employee_ID].[Employee]=[Timesheettable1].[Employee]) ON [Job Number].[Job Number]=[Timesheettable1].[JobNumbers]
WHERE (([Employee_ID].[Recovery No]) Like '*' & [Forms]![srchfrm]![recovnumber] & '*') And ([Timesheettable1].[JobNumbers]) Like '*' & [Forms]![srchfrm]![jobnumbersrchfrm] & '*' And ([Timesheettable1].[Employee]) Like '*' & [Forms]![srchfrm]![employee] & '*' And ([Timesheettable1].[PayPeriodEnd]) Like '*' & [Forms]![srchfrm]![payday] & '*' And ([Job Number].[Job Site]) Like '*' & [Forms]![srchfrm]![site] & '*';
I am having problems having the information go to different work pages based on some criteria in the query. (specifically there are 5 possible sites in the access file, each entry has one, I want each site to have its own tab in the excel file)
I assume it is a syntax error of some sorts that I am getting.
If anyone here has time to look over it and notice what I am doing wrong It is greatly appreciated.
If I have posted this in the wrong area please let me know, and I apologize for the inconvenience.
thanks
Sean
This is my code:
Private Sub exportcmd_Click()
On Error GoTo err_Handler
Const cTabTwo As Byte = 1
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rstOutput As DAO.Recordset
Dim sOutput As String
Dim sqlString As String
Dim shtArray As Variant
Dim siteArray As Variant
Dim I As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("paraquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rstOutput = qdf.OpenRecordset
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
sOutput = CurrentProject.Path & "\salary recovery template.xls"
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
appExcel.Visible = True
'Take the records in the current form and dump to Excel
siteArray = Array("PickA", "PickB", "Darl", "Bruce", "Other", "Overhead")
shtArray = Array("PickA549", "PickB349", "Darl348", "Bruce347", "Other", "Overhead")
For I = 0 To 4
sqlString = "SELECT * FROM paraquery WHERE jobsite='" & siteArray(I) & "'"
Set rstOutput = CurrentDb.OpenRecordset(sqlString)
Set wks = wbk.Worksheets.Add
wks.Name = shtArray(I)
wks.Range("A11").CopyFromRecordset rstOutput
Next
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
'You may want to save the workbook here
Set wbk = Nothing
Set appExcel = Nothing
rstOutput.Close
Set rstOutput = Nothing
DoCmd.Hourglass False
' Exit exportcmd_Click
err_Handler:
ExportRequest = Err.Description
' Me.lblMsg.Caption = Err.Description
'Resume exit_Here
End Sub
I am getting the error 3061- too few parameters, expected 6.
the debugger is highliteing this line
Set rstOutput = CurrentDb.OpenRecordset(sqlString)
my sqlsatement looks like this
SELECT [Employee_ID].[Recovery No], [Employee_ID].[Account Name], [Employee_ID].[Gen Ledg Acnt No], [Timesheettable1].[JobNumbers], [Employee_ID].[Type], [Employee_ID].[unknown], [Employee_ID].[Ref No], [Timesheettable1].[Employee], [Employee_ID].[Rate], [Timesheettable1].[Hours Worked], [Timesheettable1].[Hours Paid]
FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON [Employee_ID].[Employee]=[Timesheettable1].[Employee]) ON [Job Number].[Job Number]=[Timesheettable1].[JobNumbers]
WHERE (([Employee_ID].[Recovery No]) Like '*' & [Forms]![srchfrm]![recovnumber] & '*') And ([Timesheettable1].[JobNumbers]) Like '*' & [Forms]![srchfrm]![jobnumbersrchfrm] & '*' And ([Timesheettable1].[Employee]) Like '*' & [Forms]![srchfrm]![employee] & '*' And ([Timesheettable1].[PayPeriodEnd]) Like '*' & [Forms]![srchfrm]![payday] & '*' And ([Job Number].[Job Site]) Like '*' & [Forms]![srchfrm]![site] & '*';
