Run-time Error '3061' Too Few Parameters. Expected 4. (1 Viewer)

ccondran08 · Jan 13, 2016

Hi, looking for some help here please. I have a form that has a textbox in what I've called the "settings" tab of a control tab. The user can click on a button and edit the text box and type in an Activity Code that is then saved and used in a query to retrieve records that the user has selected (eg. [Forms]![frm_Main_Menu]![txtActivity1]).

The code below is used to export the query and paste it into a spreadsheet that is located in the directory of "TRACpath". During the execution of the code I get an Run-time error "3061" which appears and then the VBA editor appears and is highlighted on the following line ;

Set rs_Milestones = qdf_Milestones.OpenRecordset()

Can anyone please shed some light on this so I can get this finished...:banghead:

Dim XL As Excel.Application
Dim xlWB As Workbook
Dim wbTarget As Workbook
Dim TRACpath As String

TRACpath = [Forms]![frm_Main_Menu].[txtTRAC_Path]

Dim qdf_Milestones As QueryDef
Dim rs_Milestones As Recordset

Set qdf_Milestones = CurrentDb.QueryDefs("qry_P3_Milestone_Gaps")
Set rs_Milestones = qdf_Milestones.OpenRecordset()

Set XL = CreateObject("Excel.Application")

Set wbTarget = XL.Workbooks.Open(TRACpath)

wbTarget.Worksheets("Data").Cells(2, 186).CopyFromRecordset rs_Milestones

wbTarget.Save
wbTarget.Close

Set wbTarget = Nothing
Set XL = Nothing
Set qdf_Milestones = Nothing

Minty · Jan 13, 2016

Is your query a crosstab query by any chance? If it is you have to define the parameters in the query design.

ccondran08 · Jan 13, 2016

No, this isn't a cross tab query.

Minty · Jan 13, 2016

Could you post up the query - without it we are guessing.

ccondran08 · Jan 14, 2016

This is a very simple query, it works fine when I open the query. The problem is when the code tries to grab the query and send it to the spreadsheet I am missing something that tells the query that there is a textbox on the form that is used to filter the query. I believe the answer is in the below link but for some reason I am missing something.

https://support.microsoft.com/en-us/kb/209203

SELECT tbl_P6_Data.[Activity Name], tbl_P6_Data.Finish, tbl_P6_Data.[Activity ID]
FROM tbl_P6_Data
WHERE ((tbl_P6_Data.[Activity ID])=Forms!frm_Main_Menu!txtActivity1);

sneuberg · Jan 14, 2016

I suggest reading https://www.safaribooksonline.com/library/view/access-cookbook/0596000847/ch01s17.html

I am fairly certain the answer you need is there.

Added: Ok that is about the same info and in your link, still I do not see anything in your code like

qdfSample![Forms!FormName!ControlName] = _
Forms![FormName]![ControlName]

which is shown in your link.

ccondran08 · Jan 14, 2016

Thanks sneuberg, the link you provided had my answer and you've saved my day. The full code is ;

Dim XL As Excel.Application
Dim xlApp As Excel.Application
Dim xlWB As Workbook
Dim wbTarget As Workbook
Dim TRACpath As String

Dim qdf_Milestones
Dim rs_Milestones

TRACpath = [Forms]![frm_Main_Menu].[txtTRAC_Path]

'Punch ETC Transfer
Set qdf_Milestones = CurrentDb.QueryDefs("qry_P3_Milestone_Gaps")

qdf_Milestones("Forms!frm_Main_Menu!txtActivity1") = Forms!frm_Main_Menu!txtActivity1
qdf_Milestones("Forms!frm_Main_Menu!txtActivity2") = Forms!frm_Main_Menu!txtActivity2
qdf_Milestones("Forms!frm_Main_Menu!txtActivity3") = Forms!frm_Main_Menu!txtActivity3
qdf_Milestones("Forms!frm_Main_Menu!txtActivity4") = Forms!frm_Main_Menu!txtActivity4

Set rs_Milestones = qdf_Milestones.OpenRecordset()

Set XL = CreateObject("Excel.Application")

Set wbTarget = XL.Workbooks.Open(TRACpath)

wbTarget.Worksheets("Data").Cells(2, 186).CopyFromRecordset rs_Milestones

wbTarget.Save
wbTarget.Close

Set wbTarget = Nothing
Set XL = Nothing
Set qdf_Milestones = Nothing

Run-time Error '3061' Too Few Parameters. Expected 4. (1 Viewer)

ccondran08

Registered User.

Minty

AWF VIP

ccondran08

Registered User.

Minty

AWF VIP

ccondran08

Registered User.

sneuberg

AWF VIP

ccondran08

Registered User.

Similar threads

Users who are viewing this thread