Results 1 to 6 of 6
  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151

    Export to excel query error

    I have this code in my database which is working fine. However when I copy it to another database and change the query name to the correct one I get the following error message.



    Run-time error '3061':

    Too few parameters. Expected 1.

    If I debug it highlights this line of the code

    Set MyRecordset = MyQueryDef.OpenRecordset

    Any idea why??

    Code:
    Private Sub ExcelRec_Click()
    
    'Step 1: Declare your variables
    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset
    Dim i As Integer
    'Step 2: Identify the database and query
    Set MyDatabase = CurrentDb
    Set MyQueryDef = MyDatabase.QueryDefs("QueryName") 'Query name in the database
    '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("A2").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
    MsgBox "Export has been successful", vbInformation, "Sample"
    
    
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    did you try opening query directly? Likely it refers to a table, field or form that you're missing in db and you will get a parameter prompt. If it's an action query, try opening to datasheet view from design view.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Yes the query works when I run the query

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    then i'm stumped for the present if when running the query directly there's no prompt. I should have asked that too I guess.
    If there is a prompt (report what that's all about) then that's a clue. From vba side, Access cannot resolve a parameter so it's telling you that in the message.
    You may have to define the parameters in the query (property sheet) or in code.

  5. #5
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    151
    Quote Originally Posted by swenger View Post
    Yes the query works when I run the query
    I resolved it with this work around.

    Created a Function which called this input field from the form

    Then I modified the Query to call this function instead and it worked.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    glad you solved it. Good luck with your project.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export To Excel Error
    By RayMilhon in forum Programming
    Replies: 10
    Last Post: 03-14-2019, 12:06 PM
  2. Replies: 3
    Last Post: 11-11-2013, 04:50 PM
  3. Export to Excel Error Handling
    By TimMoffy in forum Programming
    Replies: 1
    Last Post: 06-06-2012, 05:40 AM
  4. Error when trying to export from table to Excel
    By tobinjames in forum Import/Export Data
    Replies: 3
    Last Post: 12-15-2011, 02:55 PM
  5. Macro export to excel error
    By Andy_d in forum Import/Export Data
    Replies: 7
    Last Post: 04-15-2011, 09:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums