Runtime error 3061

[caniread]

I get this error when I run the code below. Runtime error 3061 Too few parameters. Expected 1. Anyone know why this is happening?

Update: I figured out it is because my query has a parameter. How do I pass that parameter on to the code? [Forms]![frmreports]![ID]



Sub exportQueryADODB()


Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset


Set dbs = CurrentDb


Set rsQuery = dbs.OpenRecordset("qryAll")


Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.workbooks.Open("C:\Users\igonzalez\Docume nts\access.xlsx")
targetWorkbook.Worksheets("Sheet2").Range("A5").Co pyFromRecordset rsQuery


End Sub

[pbaldy]

Yes, it's the parameter. Simplest solution is to wrap the form reference in the Eval() function.

[caniread]

Yes, it's the parameter. Simplest solution is to wrap the form reference in the Eval() function.

I ended up doing the following code and it worked.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter




Set db = CurrentDb
Set qdf = db.QueryDefs!qryControl
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm






Set rst = qdf.OpenRecordset(dbOpenDynaset)




Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.Workbooks.Open("S:\Technical Services\access.xlsx")
targetWorkbook.Worksheets("Sheet2").Range("B3").Co pyFromRecordset rst

[pbaldy]

Yes, that's the more involved method. Glad you got it working.

[caniread]

With the code below how would I then save and close the excel workbook?


Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter




Set db = CurrentDb
Set qdf = db.QueryDefs!qryControl
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm






Set rst = qdf.OpenRecordset(dbOpenDynaset)




Set excelApp = CreateObject("Excel.application", "")
excelApp.Visible = True
Set targetWorkbook = excelApp.Workbooks.Open("S:\Technical Services\access.xlsx")
targetWorkbook.Worksheets("Sheet2").Range("B3").Co pyFromRecordset rst

[pbaldy]

Untested, but try

excelApp.Save
excelApp.Quit

[caniread]

Thank you, below is the code that worked.

targetWorkbook.Save
excelApp.Quit

[pbaldy]

Happy to help.