Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Getting Access 2010 Error 3016

    I have a VBA routine in my Access database that returns Error 3016:


    I've narrowed the problem down to the following VBA statement (by placing status messages boxes each side of the statement) in a Public Function that is called by a Public Sub that is in turn called by another Private Sub.

    ' Open recordset
    Set rsRecords = dbs.OpenRecordset(strQueryName)


    The strQueryName refers to a query that has the following SQL code:

    SELECT [Qry Trade Account].Transaction_Date, [Qry Trade Account].Transaction_ID, [Qry Trade Account].Transaction_Amount, [Qry Trade Account].Broker_Name, [Qry Trade Account].Trade_Type, [Forms]![Fm Account Report Selector].[Tbx_Opening_Balance]+DSum("[Transaction_Amount]","Qry Account Balance Chart","( (([Transaction_Date] >= #" & Format([Forms]![Fm Account Report Selector].[Tbx_Period_Start],"mm\/dd\/yyyy") & "#) AND ([Transaction_Date]< #" & Format([Transaction_Date],"mm\/dd\/yyyy") & "#)) or ( ([Transaction_Date] = #" & Format([Transaction_Date],"mm\/dd\/yyyy") & "#) and ([Transaction_ID] <= " & [Transaction_ID] & " ))) AND ([Broker_Name] = '" & [Forms]![Fm Account Report Selector].[Tbx_Broker_Name] & "') ") AS RunTot
    FROM [Qry Trade Account]
    GROUP BY [Qry Trade Account].Transaction_Date, [Qry Trade Account].Transaction_ID, [Qry Trade Account].Transaction_Amount, [Qry Trade Account].Broker_Name, [Qry Trade Account].Trade_Type
    HAVING ((([Qry Trade Account].Transaction_Date) Between [Forms]![Fm Account Report Selector].[Tbx_Period_Start] And [Forms]![Fm Account Report Selector].[Tbx_Period_End]) AND (([Qry Trade Account].Broker_Name) Like [Forms]![Fm Account Report Selector].[Tbx_Broker_Name] & "*") AND (([Qry Trade Account].Trade_Type) Like [Forms]![Fm Account Report Selector].[Tbx_Trade_Type] & "*"));


    and in Design View looks like this:


    It looks to me like the VBA statement is only recognizing the first 5 field (columns) of the Query and is overlooking the 6th field (RunTot:), despite the fact that the error message is suggesting "too few parameters". That 6th field is the one that I am particularly interested in transferring to Excel for charting purposes.

    Am I on the right track? How do I get around this error code?

    Any help or clues as to which way to proceed are appreciated.

    Cheers

    Trevor
    Last edited by BygAuldByrd; 2013-01-08 at 03:17.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    Does the query run successfully if done interactively? If so does it prompt for any parameters?
    Could you post the Criteria formulas in their entirety since they are cut off in the graphic?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2013-01-08)

  4. #3
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Yes, the query works perfecting if run interactively and when used in a tabular report.

    Yes, it is getting the required parameters from a form that is used to select the report for which this query provides data.

    Criteria and formulae as follows:

    Transaction Date > Between [Forms]![Fm Account Report Selector].[Tbx_Period_Start] And [Forms]![Fm Account Report Selector].[Tbx_Period_End]
    Broker_Name > Like [Forms]![Fm Account Report Selector].[Tbx_Broker_Name] & "*"
    Trade_Type > Like [Forms]![Fm Account Report Selector].[Tbx_Trade_Type] & "*"

    RunTot: [Forms]![Fm Account Report Selector].[Tbx_Opening_Balance]+DSum("[Transaction_Amount]","Qry Account Balance Chart","( (([Transaction_Date] >= #" & Format([Forms]![Fm Account Report Selector].[Tbx_Period_Start],"mm/dd/yyyy") & "#) AND ([Transaction_Date]< #" & Format([Transaction_Date],"mm/dd/yyyy") & "#)) or ( ([Transaction_Date] = #" & Format([Transaction_Date],"mm/dd/yyyy") & "#) and ([Transaction_ID] <= " & [Transaction_ID] & " ))) AND ([Broker_Name] = '" & [Forms]![Fm Account Report Selector].[Tbx_Broker_Name] & "') ")

    Looking forward to your helpful suggestions.

    Cheers

    Trevor

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Trevor,

    You may find this Article useful. Specifically changing your calling command to: Set rsRecords = dbs.OpenRecordset(strQueryName,dbOpenDynaset).

    Here's the MS Article on OpenRecordset which you may also find helpful.

    Sorry I can't be more specific but I don't really know what is the specific cause of your problem.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    BygAuldByrd (2013-01-08)

  7. #5
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts
    Hi RetiredGeek,

    Thanks for the very quick response. I tried your suggestion. Unfortunately it made no difference, so still looking for a solution.

    Seeing my query has 6 fields and the 3016 error is saying it's looking for 5 I decided to try deleting the RunTot: field from the query. That didn't help either, although the 3016 error is now reports: " Too few parameters. Expected 4."

    I also tried deleting the criteria - no change, adding in fixed criteria - still no change.

    Added: I've now tried this with a different Query, one with 8 fields from a single table - still the 3016 error stating "Too few parameters. Expected 4"

    I've tested the procedures in a simple sample database using a query with the same number of fields and they work just fine.

    This has really got me baffled.

    Cheers

    Trevor
    Last edited by BygAuldByrd; 2013-01-08 at 23:13.

  8. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by BygAuldByrd View Post
    Hi RetiredGeek,

    Thanks for the very quick response. I tried your suggestion. Unfortunately it made no difference, so still looking for a solution.

    Seeing my query has 6 fields and the 3016 error is saying it's looking for 5 I decided to try deleting the RunTot: field from the query. That didn't help either, although the 3016 error is now reports: " Too few parameters. Expected 4."

    I also tried deleting the criteria - no change, adding in fixed criteria - still no change.

    Added: I've now tried this with a different Query, one with 8 fields from a single table - still the 3016 error stating "Too few parameters. Expected 4"

    I've tested the procedures in a simple sample database using a query with the same number of fields and they work just fine.

    This has really got me baffled.

    Cheers

    Trevor
    VBA's expression processor CANNOT parse references to form controls when they are buried within a stored query.

    If your query is a simple one, then you can build the query dynamically like this:
    strSQL = "SELECT field FROM table WHERE dept = '" & Forms![frmName]![txtDept].Text & "'"
    set rs = dbs.OpenRecordset(strSQL)

    This will be a pain for your query because it is complex, and since you already have it saved you need a slightly different approach which involves setting the query parameters before running it.

    Use some code like this:
    Code:
    dim db as database
    dim qdf as querydef
    dim rst as recordset
    dim prm as parameter
    
    set db = currentdb
    set qdf = db.QueryDefs("yourquery")
    
    For Each prm in qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    
    set rst = qdf.OpenRecordset()
    
    ' put cleanup code here
    You can also set individual parameters before opening the query if you know which parameter is which - e.g. you can use syntax like
    qdf.Parameters(0).Value = "Sales"
    or
    qdf.Parameters(0) = Forms!frmInput![txtDept].Text

    but the For Each loop should evaluate all of them for you.

    (Note: I've assumed you're using references to the DAO library - Database, QueryDef, Parameter and Recordset are all DAO objects in my code!)
    Last edited by jeremybarker; 2013-01-09 at 05:23. Reason: missing quote

  9. The Following User Says Thank You to jeremybarker For This Useful Post:

    BygAuldByrd (2013-01-09)

  10. #7
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    105
    Thanks
    25
    Thanked 2 Times in 2 Posts

    Access Query Export to Excel for Access Report

    Hi Jeremy,

    Thank you for your suggestion. That looks like a pretty complicated solution so I haven't tried it, having found a simpler solution before I read you post.

    I used the "DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strQueryName, strFullFileName" procedure.

    For those interested in what I was trying to do, here is my public function code for preparing a chart to be inserted into an Access Report

    Code:
    Public Function OpenExcelWorkbook(strFullFileName As String, strWorkSheetName As String, _
    strChartSheetName As String, strQueryName As String, Optional blnClose As Boolean) As Boolean
    
    
    '   Opens an Excel spreadsheet, deletes existing data and adds new data from Access query which
    '   is to be displayed in an Access Report
    '   Parameters:
    '       strFullFileName:    this the fully defined path/filename to the Excel Workbook that is
    '                           to be used for producing a Chart to insert in an Access Report
    '       strWorkSheetName:   this is the name of the Worksheet within the Workbook, the first time
    '                           the Workbook is used this process will create the Worksheet
    '                           and give it the name of the Query
    '       strChartSheetName:  this is a Chart sheet witnin the Workbook. This chart is to be
    '                           displayed in the Access Report
    '       strQueryName:       the Access Query containing the data to be exported to Excel
    '       blnClose:           an optional parameter that, if set to "True", will quit (close) the
    '                           Excel instance created by this function. Default is "False"
    '                           so Excel will remain open.
    
    
    On Error GoTo Err_Proc
    
    
    '   Set flag to True for verbose debugging messages
    Dim strDebuggingMessageFlag As Boolean
    strDebuggingMessageFlag = False
    
    
    '   Check function parameters
    
    
    If strDebuggingMessageFlag Then MsgBox "Parameters:" & vbCrLf & "1:  " & strFullFileName & vbCrLf & "2:  " & _
    strWorkSheetName & vbCrLf & "3:  " & strChartSheetName & vbCrLf & "4:  " & _
    strQueryName & vbCrLf & "5:  " & blnClose, vbExclamation, "0.0 Public Function OpenExcelWorkbook..."
    
    
    If Len(strFullFileName) = 0 Then
        MsgBox "Missing filename.", vbCritical + vbOKOnly, "Error"
        Exit Function
    End If
    
    
    If Len(strWorkSheetName) = 0 Then
        MsgBox "Missing sheet name.", vbCritical + vbOKOnly, "Error"
        Exit Function
    End If
    
    
    If Len(strChartSheetName) = 0 Then
        MsgBox "Missing Chart name.", vbCritical + vbOKOnly, "Error"
        Exit Function
    End If
    If Len(strQueryName) = 0 Then
        MsgBox "Missing query name or SQL string.", vbCritical + vbOKOnly, "Error"
        Exit Function
    End If
    
    
    Dim wbk As Object
    Dim appExcel As Object
    Dim blnWorkbookExists As Boolean
    Dim rstDAO As DAO.Recordset
    
    
    On Error Resume Next
    
    
    '   Create instance of Excel
    Set appExcel = CreateObject("Excel.Application")
    appExcel.UserControl = True
    appExcel.DisplayAlerts = False
    appExcel.Visible = False
    
    
        If strDebuggingMessageFlag Then MsgBox "Excel started", vbExclamation, "0.1 Public Function OpenExcelWorkbook..."
        
    '   Check to see if the path/filenmae for workbook exists
    blnWorkbookExists = MyFileExists(strFullFileName)
    
    
    If blnWorkbookExists Then
        '   If workbook exists open it
        appExcel.Workbooks.Open strFullFileName
        appExcel.ActiveWorkbook.Activate
        If strDebuggingMessageFlag Then MsgBox "Workbook: " & vbCrLf & strFullFileName & vbCrLf & vbCrLf & "exists, opened and activated.", _
        vbExclamation, "0.2 Public Function OpenExcelWorkbook..."
    Else
        '   If workbook does not exist exit function and advise user to create workbook
        MsgBox "Workbook not found." & vbCrLf & vbCrLf & "Using Excel, create a workbook with path/filename: " & vbCrLf & vbCrLf & strFullFileName _
        & vbCrLf & vbCrLf & " added new sheet named :" & vbCrLf & vbCrLf & strWorkSheetName _
        & vbCrLf & vbCrLf & "and create any required charts, then Quit Excel", vbExclamation, "Public Function OpenExcelWorkbook..."
        GoTo Exit_Proc
    End If
    
    
        If strDebuggingMessageFlag Then MsgBox "Excel workbook " & vbCrLf & wbk & vbCrLf & vbCrLf & "now activated", _
        vbExclamation, "1.0 Public Function OpenExcelWorkbook..."
    
    
    On Error GoTo Err_Proc
    
    
    '   Actviate worksheet that is to contain imported data
    appExcel.ActiveWorkbook.Sheets(strWorkSheetName).Activate
    
    
    '   Clear old data, if any
    appExcel.ActiveWorkbook.Worksheets(strWorkSheetName).Cells.ClearContents
        
        If strDebuggingMessageFlag Then MsgBox "Data deleted from " & strWorkSheetName, _
        vbExclamation, "2.0 Public Function OpenExcelWorkbook..."
    
    
    ' Save excel spreadsheet
    appExcel.ActiveWorkbook.Save
        
        If strDebuggingMessageFlag Then MsgBox "Excel and  '" & strWorkSheetName & "' ready to be closed.", _
        vbExclamation, "2.1 Public Function OpenExcelWorkbook..."
    
    
    '   Save and close Excel before transfering data from Access
    appExcel.Quit
    
    
    '   Wait a couple of seonds for the Excel workbook to close for attemting to write data to it
    TWait = Time
    TWait = DateAdd("s", 2, TWait)
    Do Until TNow >= TWait
         TNow = Time
    Loop
    
    
        If strDebuggingMessageFlag Then MsgBox "Excel closed." & vbCrLf & vbCrLf & "Ready to transfer data from Access to " _
        & vbCrLf & vbCrLf & strWorkSheetName, vbExclamation, "3.0 Public Function OpenExcelWorkbook..."
    
    
    '   Try transfer using DoCmd.TransferSpreadsheet procedure
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strQueryName, strFullFileName
    
    
        If strDebuggingMessageFlag Then MsgBox "DoCmd.TransferSpreadsheet procedure completed", _
        vbExclamation, "3.1 Public Function OpenExcelWorkbook..."
    
    
    '   Open and Activate the DataChart sheet in Excel
    appExcel.Workbooks.Open strFullFileName
    appExcel.ActiveWorkbook.Charts(strChartSheetName).Activate
        If strDebuggingMessageFlag Then MsgBox "Workbook '" & strChartSheetName & "' activated and ready viewing", _
        vbExclamation, "4.0 Public Function OpenExcelWorkbook..."
    
    
    ' Save excel spreadsheet
    If blnWorkbookExists Then
        appExcel.ActiveWorkbook.Save
    Else
        appExcel.ActiveWorkbook.SaveAs strFullFileName
    End If
    
    
    Exit_Proc:
    
    
        ' Reset alerts prompts
        appExcel.DisplayAlerts = True
        Set dbs = Nothing
        '   Close Excel if flag blnClose = True
        If blnClose Then
            appExcel.Quit
        End If
        
        OpenExcelWorkbook = True
        
        If strDebuggingMessageFlag Then MsgBox "OpenExcelWorkbook subroutine completed", vbExclamation, "5.0 OpenExcelWorkbook..."
    
    
    Exit_Proc_1:
        Exit Function
    
    
    Err_Proc:
    
    
        If Err.Number = 9 Then
            MsgBox Err.Number & "-" & Err.Description, vbCritical, "Error 0: Public Function OpenExcelWorkbook()"
            blnWorksheetExists = False
            Resume Next
        Else
            MsgBox Err.Number & "-" & Err.Description, vbCritical, "Error 1: Public Function OpenExcelWorkbook()"
            Resume Exit_Proc_1
        End If
    
    
    End Function
    Cheers

    Trevor
    Last edited by BygAuldByrd; 2013-01-09 at 15:14.

  11. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    222
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by BygAuldByrd View Post
    Hi Jeremy,

    Thank you for your suggestion. That looks like a pretty complicated solution so I haven't tried it, having found a simpler solution before I read you post.

    I used the "DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, strQueryName, strFullFileName" procedure.

    Cheers

    Trevor
    Hi Trevor - glad you found a solution that worked for you! Since we didn't have much context in the original post I assumed you needed to open the query as a recordset. If you ever need to do that, then the code I posted will allow you to resolve parameters that depend on values stored in Access forms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •