Error 3061 Too Few Parameters Expecting 1

[ironman]

Hello - I've been searching for a solution to this error. I think there is something I need to do with 'resolving' the parameter in my query, but I'm not sure how to apply the changes. I have what I think is 3 parts, a Function Module, a Query, a Button on a form, with VBA calling the function.



The query works fine when run via access queries. The problem starts with the use the criteria of adding a [enter parameter here] option to the Query Grid. When I run the query via the button on the form I get the message. (if there is no parameter in the query it works, it's just when I have the parameter it fails.). Here is the SQL view of the query - I'm using [Enter Region Here], for the Region ID:

 
SELECT ("2010") AS [Year], Network_by_ZIP.DISTRICT, Network_by_ZIP.REGION, INSTITUTIONS_2010.RSSDHCR_OR_CERT_2010_NEW, First(INSTITUTIONS_2010.NAME_INSTITUTION_NEW) AS FirstOfNAME_INSTITUTION_NEW, Count(([BKBR])) AS OFFICES10, Sum(([DEPSUMBR])) AS DEPSUMBR10
FROM (([FDIC_10_STATE_DATA_2010_3-18-2011] LEFT JOIN Network_by_ZIP ON [FDIC_10_STATE_DATA_2010_3-18-2011].ZIPBR = Network_by_ZIP.ZIP_CD_5) LEFT JOIN MAIN_OFFICES ON [FDIC_10_STATE_DATA_2010_3-18-2011].UNINUMBR = MAIN_OFFICES.UNINUMBER_MO) INNER JOIN INSTITUTIONS_2010 ON [FDIC_10_STATE_DATA_2010_3-18-2011].RSSDHCR_OR_CERT = INSTITUTIONS_2010.RSSDHCR_OR_CERT_2010
WHERE (((MAIN_OFFICES.MO_FLAG) Is Null) AND ((Network_by_ZIP.REGION_ID)=[Enter Region Here]))
GROUP BY Network_by_ZIP.DISTRICT, Network_by_ZIP.REGION, INSTITUTIONS_2010.RSSDHCR_OR_CERT_2010_NEW
ORDER BY Network_by_ZIP.REGION, Sum(([DEPSUMBR])) DESC;

The function Code is used to run a query, then copy the results to a specific XLS file and Worksheet. I found this online, wish i could take credit for it, cuz I think it's pretty slick!

Here's the Function:

 
Public Function SendTQ2XLWbSheetR(strTQName As String, strSheetName As String, strFilePath As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
' strFilePath is the name and path of the file you want to send this data into.
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWBkC As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
    On Error GoTo err_handler
    strPath = strFilePath
    Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPath)
    ApXL.Visible = True
    Set xlWSh = xlWBk.worksheets(strSheetName)
    
    xlWSh.select
    xlWSh.Range("A1").select
    xlWSh.columns("b:h").clearcontents
    xlWSh.Range("A1").select
    For Each fld In rst.Fields
        ApXL.ActiveCell = fld.Name
        ApXL.ActiveCell.Offset(0, 1).select
    Next
    rst.MoveFirst
    xlWSh.Range("b2").CopyFromRecordset rst
    xlWSh.Range("1:1").select
    ' This is included to show some of what you can do about formatting.  You can comment out or delete
    ' any of this that you don't want to use in your own export.
    With ApXL.Selection.Font
        .Name = "Arial"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
    End With
    ApXL.Selection.Font.Bold = True
    With ApXL.Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.select
    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit
    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").select
    
    rst.Close
    xlWBk.Close 'True   ' true means close without prompt - - blank means close with prompt. True
    Set rst = Nothing
Exit_SendTQ2XLWbSheetR:
    Exit Function
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_SendTQ2XLWbSheetR
End Function

I'm finding from my search that I think I need to modify the SQL view, but I'm not sure how. Suggestions would be great...and if your a mother...Happy Mother's Day to you...

[June7]

This is why I don't use parameter prompts in queries. Problematic and too hard to control validation. I prefer to have users enter value into a control on form. Then have query refer to the control to get the value. This way I can make sure the entered value is good before running the query (or opening a form or report). I can't see anything wrong with the query.

Did you have a question about the function?

[ironman]

Hi June7 - - Yes, so right now the button on a form calls the function I have shown, Would I use an imput box in the command button before the call, that would identify the value. In my case it would be a region: IL01 would be a parameter.

Would I then need to modify the function to use that region code of IL01? By chance would you be able to provide me an example? Thanks for your quick reply!

[June7]

If the query were in VBA, an input box could work. Would need code to validate entry. Example:

strResponse = 0
While strResponse < 1 Or strResponse > 9
      strResponse = InputBox("How many specimens? Select quantity of 1 to 9.", "Multiple Specimens", 1)
Wend

Then the strResponse variable would be concatenated into the SQL statement.

But you are calling an Access saved query object? Then need a textbox (or combobox or listbox) where user enters/selects value. Use the Validation property of textbox as one way to control the entry. Cannot proceed until it is correct. Then the query would refer to the control: Network_by_ZIP.REGION_ID=Forms!formname!controlnam e

Should not impact the function. You can continue to call the function by button click or by the AfterUpdate event of the textbox.

[ironman]

Thanks again for your quick reply. I'm going to test this tomorrow! I'll provide an update then. Thanks again.