Results 1 to 6 of 6

Export to excel query error

  1. #1
    swenger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    133

    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,450
    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.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

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

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,450
    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
    133
    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 online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,450
    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
  •  
Tech Forums: Microsoft Office Forums