Run Time Error 3061

[LB79]

Hello,

Im having some trouble with “Run Time Error 3061 – Too Few Parameters. Expected 1.”
I've tried jiggling the code about but cant seem to solve it.l
Can anyone see what my problem it?

Thanks

VBA
Private Sub lst1_Click()
Dim Dte As String
Dim Agent As String
Dim NewFile As String
Dim Path As String
Dte = Format(Now, "YYYYMMDD")
Agent = Me.PMA_lst1
Filename = "MyFileName (" & Agent & " " & Dte & ").xls"
Path = "C:\"
FileCopy "C:\MyFile.xls", Path & Filename
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(Path & Filename)
Dim Rs As DAO.Recordset
Set xlSheet = xlBook.Worksheets(1)
Set Rs = CurrentDb.OpenRecordset("MyQuery", dbOpenSnapshot) ‘’’’’’’’’’’’’’’’’’Debugs Here‘’’’’’’’’’’’’’’’’’
xlSheet.Range("A2").CopyFromRecordset Rs
Rs.Close
Set Rs = Nothing
xlBook.Save
xlApp.Quit

MyQuery SQL

SELECT IIf([PO_REPORT_MONTH] Is Null,"-",[PO_REPORT_MONTH]) AS Exp, tbl_PMA_LEGS_PMA.PO_ACTIVITY_MONTH AS [Cost Month], tbl_PMA_LEGS_PMA.AGENT_CD AS Agent, Left([AC],4) AS Item, tbl_PMA_LEGS_PMA.CUR, tbl_PMA_LEGS_PMA.PMA_AMT AS [PMA (CUR)], tbl_PMA_LEGS_PMA.PMA_USD AS [PMA (USD)], "" AS Blank1, "" AS Blank2, tbl_PMA_LEGS_PMA.[PO#] AS PO
FROM tbl_PMA_LEGS_PMA
WHERE (((tbl_PMA_LEGS_PMA.AGENT_CD) Like [Forms]![frm_MNU1_Main]![MNU_subfrm].[Form]![PMA_Sub].[Form]![PMA_lst1]));

 

[namliam]

[Forms]![frm_MNU1_Main]![MNU_subfrm].[Form]![PMA_Sub].[Form]![PMA_lst1]));

Your not allowed to do this in a coded query...

You cannot call items/controls on a form, you have to do this anotherway...

It works fine if you run the query manually, but from code it will fail...
To prove it... substitute in the real value of the PMA_List1 and try it, it should work then.

 

[namliam]

Also....

[B]Set xlBook = xlApp.Workbooks.Open[/B](Path & Filename)
Dim Rs As DAO.Recordset
Set [B]xlSheet[/B] = xlBook.Worksheets(1)
Set Rs = CurrentDb.OpenRecordset("MyQuery", [B]dbOpenSnapshot[/B]) ‘’’’’’’’’’’’’’’’’’Debugs Here‘’’’’’’’’’’’’’’’’’
[B]xlSheet[/B]

Are you executing this from Excel or access?? dbOpenSnapshot may not be a defined variable if your executing this in Excel.

 

[LB79]

Hi and thanks for that... I wonder what the reason is for not being able to do these certain things in code. Perhaps its allowed in 2007+?

Do you know of a way around this? The query filters based on a list box.
This is being executed in Access.

Thanks

 

[namliam]

You need to fill in the parameter when you run the query...
i.e. Give query
WHERE (((tbl_PMA_LEGS_PMA.AGENT_CD) Like [GiveParameter]

Then in code assign the value to the parameter before executing it.