Export to Excel: Runtime Error 3061 !!!HELP!!! (1 Viewer)

[businesshippieRH]

I am trying to export a query to excel. I have used this code minus the SQL portion (those queries did not require input from a form) for other queries to do the exact same thing. I have multiple queries that will require input from a form, and believe that if I can solve this first one, the rest should be pretty formulaic.

However, when I run this one, I get a "Run-time error 3061: too few parameters. Expected 3."

I have tried the code split into two sections run by separate buttons, where one creates the query and the other exports to excel. I have also tried running a query built using SQL (no VBA) and exporting it using the method that works for my other exports. The query itself populates using either method. It just will not export (crashes on red line).

My code is as follows:

Private Sub btn_EXPORTCCQry_Click()
'Open the query
'Export to Excel
        'Step 1: Declare your variables
        Dim MyDatabase As DAO.Database
        Dim MyQueryDef As DAO.QueryDef
        Dim MyRecordset As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
            'SQL from original "qry_REPORTINGChargeCodeDate" This query will be deleted if I can get this code to work.
            strSQL = "PARAMETERS [Forms].[frm_Reporting].[txt_CCStartDate] DateTime, [Forms].[frm_Reporting].[txt_CCEndDate] DateTime;" & _
                     " SELECT tbl_CompletedScans.ChargeCode, tbl_CompletedScans.CheckOutDate, tbl_EmployeeData.EmpName, tbl_CompletedScans.TrinityID, tbl_CompletedScans.EquipmentType, tbl_CompletedScans.Manufacturer, tbl_CompletedScans.ModelNo, tbl_CompletedScans.SerialNo" & _
                     " FROM tbl_EmployeeData INNER JOIN tbl_CompletedScans ON tbl_EmployeeData.EmpID = tbl_CompletedScans.CheckOutEmployee" & _
                     " WHERE (((tbl_CompletedScans.ChargeCode)=Forms!frm_Reporting!txt_CCChargeCode) And ((tbl_CompletedScans.CheckOutDate) Between (Forms!frm_Reporting!txt_CCStartDate) And (Forms!frm_Reporting!txt_CCEndDate)));"
        'Step 2: Identify the database and query
        Set MyDatabase = CurrentDb
            On Error Resume Next
            With MyDatabase
                .QueryDefs.Delete ("qry_ExportChargeCodeDate")
                Set MyQueryDef = .CreateQueryDef("qry_ExportChargeCodeDate", strSQL)
                .Close
            End With
        'Step 3: Open the query
        [COLOR="Red"]Set MyRecordset = MyQueryDef.OpenRecordset[/COLOR]
        'Step 4: Clear previous contents
        Dim xlApp As Object
        Set xlApp = CreateObject("Excel.Application")
        With xlApp
        .Visible = True
        .Workbooks.Add
        .Sheets("Sheet1").SELECT
        'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A1").CopyFromRecordset MyRecordset
        'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
        xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
        End With
'Open message box
MsgBox "Please use MS Excel to modify data further.", vbInformation, "Data Exported"

Thanks in advance for your help!!!

 

[vbaInet]

However, when I run this one, I get a "Run-time error 3061: too few parameters. Expected 3."

Opening a recordset without supplying the parameters will cause it to fail. A recordset won't go looking for the form to get the parameters.

In any case, here's a link showing several ways of exporting to Excel:

http://www.btabdevelopment.com/ts/exportformrecordset

 

[tehNellie]

To further vbaInet and your own comment.

Don't delete the query, modify it and the code to work properly:

PARAMETERS [@startdate] DateTime, [@Enddate] DateTime [@chargecode] datatype;                     
SELECT 
tbl_CompletedScans.ChargeCode
, tbl_CompletedScans.CheckOutDate
, tbl_EmployeeData.EmpName
, tbl_CompletedScans.TrinityID
, tbl_CompletedScans.EquipmentType
, tbl_CompletedScans.Manufacturer
, tbl_CompletedScans.ModelNo
, tbl_CompletedScans.SerialNo
FROM tbl_EmployeeData 
INNER JOIN tbl_CompletedScans ON tbl_EmployeeData.EmpID = tbl_CompletedScans.CheckOutEmployee
WHERE (((tbl_CompletedScans.ChargeCode)=[@chargecode] 
And ((tbl_CompletedScans.CheckOutDate) Between ([@startdate]) And ([@enddate])));”

I don't know what the datatype of Chargecode is and I dare say that I've omitted a bracket somewhere, but this the basis of your query. Save it with a meaningful Name.

your code now is hence:

Dim qdf as querydef

set qdf as currentdb.querydef("QuerywithMeaningfulName")
qdf.parameters("[@startdate]").value = Forms!frm_Reporting!txt_CCStartDate
qdf.parameters("[@enddate]").value = Forms!frm_Reporting!txt_CCEndDate
qdf.parameters("[@chargecode]").value = Forms!frm_Reporting!txt_CCChargeCode

set rs = qdf.openrecordset

lets get the query working and then we'll worry about the excel bit, at first glance it looks ok.

 

[businesshippieRH]

Sorry for the slow reply, but work has been keeping me busy with other stuff (the access program still has a few weeks before "launch", and I tend to get pulled in a thousand different directions at all times...). Tehnellie: I have followed your instructions and changed the query ("qry_REPORTINGChargeCodeDate") to read as follows:

PARAMETERS [Forms]![frm_Reporting]![txt_CCStartDate] DateTime, [Forms]![frm_Reporting]![txt_CCEndDate] DateTime, [Forms]![frm_Reporting]![txt_CCChargeCode] Text ( 255 );
SELECT tbl_CompletedScans.ChargeCode, tbl_CompletedScans.CheckOutDate, tbl_EmployeeData.EmpName, tbl_CompletedScans.TrinityID, tbl_CompletedScans.EquipmentType, tbl_CompletedScans.Manufacturer, tbl_CompletedScans.ModelNo, tbl_CompletedScans.SerialNo
FROM tbl_EmployeeData INNER JOIN tbl_CompletedScans ON tbl_EmployeeData.EmpID = tbl_CompletedScans.CheckOutEmployee
WHERE (((tbl_CompletedScans.ChargeCode)= ([Forms]![frm_Reporting]![txt_CCChargeCode])) And 
((tbl_CompletedScans.CheckOutDate) Between [Forms]![frm_Reporting]![txt_CCStartDate] And [Forms]![frm_Reporting]![txt_CCEndDate]));

If I run it after filling in the proper boxes on the form, the query runs perfectly. I tried replacing the parameters portions with [@chargecode], [@startdate], and [@enddate]. However, it then produces a pop-up asking for these to be input. The code on my form is as follows:

'Export to Excel
        'Step 1: Declare your variables
        Dim MyDatabase As DAO.Database
        Dim MyQueryDef As DAO.QueryDef
        Dim MyRecordset As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
            'Step 2: Identify the database and query
            Set MyDatabase = CurrentDb
            Set MyQueryDef = MyDatabase.QueryDefs("qry_REPORTINGChargeCodeDate") 'Query name in the database
                qdf.Parameters([Forms]![frm_Reporting]![txt_CCStartDate]).Value = Forms.frm_Reporting.txt_CCStartDate
                qdf.Parameters([Forms]![frm_Reporting]![txt_CCEndDate]).Value = Forms.frm_Reporting.txt_CCEndDate
                qdf.Parameters([Forms]![frm_Reporting]![txt_CCChargeCode]).Value = Forms.frm_Reporting.txt_CCChargeCode
        'Step 3: Open the query
        Set MyRecordset = MyQueryDef.OpenRecordset
            'Step 4: Clear previous contents
            Dim xlApp As Object
            Set xlApp = CreateObject("Excel.Application")
            With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").SELECT
        'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A1").CopyFromRecordset MyRecordset
        'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
        xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
        End With

I have tried using this code with the qdf.Parameters set to [@startdate]=Form control (obviously while the query was modified in the same way), as well as as it is written above. Both ways return a Run-time error '424': Object required.

Thanks again for your help!

 

[vbaInet]

May I bring your attention to tehNellie's post which shows the proper way to reference a parameter in a querydef.

qdf.parameters([B][COLOR="Blue"]"[@startdate]"[/COLOR][/B]).value = Forms!frm_Reporting!txt_CCStartDate

 

[businesshippieRH]

Thanks. That should help moving forward. I'm still new to VBA and kinda "learning as I go". I should have mentioned previously that I was unsure of syntax for that portion of the code. I have tried it both ways (but will only do so with the quotes in the future) and continue to receive the Run-time error '424' (with the query set up for those parameters- I finally made 2 versions so that I could stop rewriting the query each time).

 

[vbaInet]

I was under the impression that you wrote the code. Are you still experiencing problems?

 

[businesshippieRH]

I did write the code using the input from tehNellie above. I have fixed it so that it reads as follows:

'Export to Excel
        'Step 1: Declare your variables
        Dim MyDatabase As DAO.Database
        Dim MyQueryDef As DAO.QueryDef
        Dim MyRecordset As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
            'Step 2: Identify the database and query
            Set MyDatabase = CurrentDb
            Set MyQueryDef = MyDatabase.QueryDefs("qry_REPORTINGChargeCodeDate2") 'Query name in the database
                qdf.Parameters("[@startdate]").Value = Forms.frm_Reporting.txt_CCStartDate
                qdf.Parameters("[@enddate]").Value = Forms.frm_Reporting.txt_CCEndDate
                qdf.Parameters("[@chargecode]").Value = Forms.frm_Reporting.txt_CCChargeCode
        'Step 3: Open the query
        Set MyRecordset = MyQueryDef.OpenRecordset
            'Step 4: Clear previous contents
            Dim xlApp As Object
            Set xlApp = CreateObject("Excel.Application")
            With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").SELECT
        'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A1").CopyFromRecordset MyRecordset
        'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
        xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
        End With

I continue to get the same error.

 

[vbaInet]

When I said I thought you wrote the code, it was in reference to your original code and your statement about learning VBA

I'm going to reference tehNellie's post again.

qdf.parameters("[@startdate]").value = Forms[B][B][COLOR="Red"]![/COLOR][/B][/B]frm_Reporting[COLOR="Red"][B]![/B][/COLOR]txt_CCStartDate

When code is posted please follow every little detail.

 

[businesshippieRH]

Gotcha! Tone + internet= bad combination sometimes. Thanks again.

I'm learning the SQL as I go too (we use a lot of excel and a little access to tabulate huge datasets, but we're trying to use forms and such for the first time to allow for more than myself and one other guy to do the work)... and I get them mixed up sometimes. It's updated and still not working (with the "[@startdate]" or "[Forms]![frm_Reporting]![txt_CCStartDate]").

 

[vbaInet]

I need to know the line it errors. When it throws an error click the Debug button and tell me the line it highlights.

 

[businesshippieRH]

Error is on line:

qdf.Parameters("[@startdate]").Value = Forms.frm_Reporting.txt_CCStartDate

 

[vbaInet]

Same thing as I highlighted in post #9.

 

[businesshippieRH]

Sorry. I was being lazy and copied it from my above post. Error is on line:

qdf.Parameters("[@startdate]").Value = Forms!frm_Reporting!txt_CCStartDate

 

[vbaInet]

You called your querydef (qdf) is called MyQuerydef. If you followed tehNellie's example to the letter you won't be having issues with the querydef parts.

 

[businesshippieRH]

Ok... following his instructions (Changed lines have 'CHANGE next to them)

    Dim MyDatabase As DAO.Database
        Dim qdf As DAO.QueryDef [COLOR="red"]'CHANGE[/COLOR]
        Dim MyRecordset As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
            'Step 2: Identify the database and query
            Set MyDatabase = CurrentDb
            Set qdf = CurrentDb.QueryDef("qry_REPORTINGChargeCodeDate2") [COLOR="red"]'CHANGE[/COLOR]
                qdf.Parameters("[@startdate]").Value = Forms!frm_Reporting!txt_CCStartDate
                qdf.Parameters("[@enddate]").Value = Forms!frm_Reporting!txt_CCEndDate
                qdf.Parameters("[@chargecode]").Value = Forms!frm_Reporting!txt_CCChargeCode
        'Step 3: Open the query
        Set MyRecordset = QueryDef.OpenRecordset [COLOR="red"]'CHANGE[/COLOR]
            'Step 4: Clear previous contents
            Dim xlApp As Object
            Set xlApp = CreateObject("Excel.Application")
            With xlApp
            .Visible = True
            .Workbooks.Add
            .Sheets("Sheet1").SELECT
        'Step 5: Copy the recordset to Excel
        .ActiveSheet.Range("A1").CopyFromRecordset MyRecordset
        'Step 6: Add column heading names to the spreadsheet
        For i = 1 To MyRecordset.Fields.Count
        xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        Next i
        xlApp.Cells.EntireColumn.AutoFit
        End With

Now: "Compile error: Method or data member not found" on line with red highlighted:
Set qdf = CurrentDb.QueryDef("qry_REPORTINGChargeCodeDate2")

Sorry... I'm still trying to wrap my head around the whole variables thing...

 

[vbaInet]

I like the "CHANGE" labels

        Dim MyDatabase As DAO.Database
        Dim qdf As [COLOR="blue"]DAO.[/COLOR]QueryDef
        Dim MyRecordset As DAO.Recordset
        Dim strSQL As String
        Dim i As Integer
            'Step 2: Identify the database and query
            Set MyDatabase = CurrentDb
            Set qdf = [COLOR="Blue"]MyDatabase[/COLOR].QueryDef[COLOR="blue"]s[/COLOR]("qry_REPORTINGChargeCodeDate2")
                qdf.Parameters("[@startdate]").Value = Forms!frm_Reporting!txt_CCStartDate
                qdf.Parameters("[@enddate]").Value = Forms!frm_Reporting!txt_CCEndDate
                qdf.Parameters("[@chargecode]").Value = Forms!frm_Reporting!txt_CCChargeCode
        'Step 3: Open the query
        Set MyRecordset = [COLOR="blue"]qdf[/COLOR].OpenRecordset

 

[businesshippieRH]

Thank you!!! It works perfectly. Looks like I may actually be able to wrap up this little project tomorrow...

 

[vbaInet]

Good luck with that