Executing query via Stored Procedure.

I created the following query:

SELECT Null AS Title, tblCustomers.FirstName AS [First Name], tblCustomers.MiddleInitial AS MI, tblCustomers.LastName AS [Last Name], tblCustomers.SecondNameFirst AS [Second Name First], tblCustomers.SecondNameMid AS [Second Name Mid], tblCustomers.SecondNameLast AS [Second Name Last], tblCustomers.Relation AS Relation, tblCustomers.Address1 AS [Address 1], tblCustomers.Address2 AS [Address 2], tblCustomers.Address3 AS [Address 3], tblCustomers.Address4 AS [Address 4], tblCustomers.City, tblCustomers.State, tblCustomers.Zip, tblCustomers.SSN, Null AS [DDA Number], Null AS [Check/Cert Number], Format(Nz([CashBalance],Nz([SharesAmount],0)),"#.000") AS [Check/Cert Amount], Right(tblCustomers.OfficeNumber,3) & " " & tblCustomers.CustomerNumber AS [Account Number], tblProducts.PropertyType AS [Property Type], tblCustomers.PropertyStatus AS [Property Status], tblProducts.CUSIP, tblProducts.SecurityName AS [Security Name], Null AS [Sub-Issue], tblProducts.IRACode, tblCustomers.DateOfBirth
FROM tblStatesAll INNER JOIN (tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber=tblProducts.CustomerNumber) ON tblStatesAll.StateFS=tblCustomers.State
WHERE (tblCustomers.DateOfBirth<=DateSerial(IIf(Forms!frmCustomRpt!lstYears.Value="ALL",Year(Now()),Forms!frmCustomRpt!lstYears.Value)-(tblStatesAll.IRAFS+71),12,31) And tblStatesAll.FallCycle=Yes And LEN(tblProducts.IRACode)>0) Or (IsNull(tblCustomers.DateOfBirth) And tblStatesAll.FallCycle=Yes);

This query is passed through to an Excel format via the following sub procedure:

Private Sub IRAQueryFall()
 
    Dim rstQueryFS As ADODB.Recordset
    Dim objXL As Excel.Application
    Dim objWS As Excel.Worksheet
    Dim fld As ADODB.Field
    Dim intCol As Integer
    Dim intRow As Integer
   
           
    Set rstQueryFS = New ADODB.Recordset
      Set rstQueryFS = CurrentProject.Connection.Execute("qryIRAFall", , adCmdStoredProc)
      Set objXL = New Excel.Application
      objXL.Workbooks.Add
      strNextFile = GetNextFileName("C:\IRA1.XLS")
      objXL.ActiveWorkbook.SaveAs strNextFile
      Set objWS = objXL.ActiveSheet
      objXL.ActiveSheet.Protect UserInterfaceOnly:=True
                 
      For intCol = 0 To rstQueryFS.Fields.Count - 1
        Set fld = rstQueryFS.Fields(intCol)
        objWS.Cells(1, intCol + 1) = fld.Name
      Next intCol
     
      intRow = 2
      Do Until rstQueryFS.EOF
        For intCol = 0 To rstQueryFS.Fields.Count - 1
           
           objWS.Cells(intRow, intCol + 1) = _
              rstQueryFS.Fields(intCol).Value
           objWS.Cells.EntireColumn.AutoFit

        Next intCol
        rstQueryFS.MoveNext
        intRow = intRow + 1
      Loop
      DoCmd.Hourglass False
       
      objXL.Visible = True
           
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------------------

Has it been your experience that when you reference a control within a query, that you have to build the SQL statements in VBA if you want to execute the query as a Stored Procedure. In other words, you can't create the query in a query object and execute the query via the Stored Procedure if it has controls that are referenced in the query. I have a control which I reference in the query as Forms!frmCustomRpt!lstYears.Value.
When I try and run the form which executes the following statement:

Set rstQueryFS = CurrentProject.Connection.Execute("qryIRAFall", , adCmdStoredProc)

I get a message: Too few parameters. Expected 1.

When I run the query via the Database Window, I double click on the query object and the system response is

Enter Parameter Value

Forms!frmCustomRpt!lstYears.Value

This query executes from a form named frmCustomRpt which contains a list box named lstYears that the user makes a choice from
which in turn is generating the dialog box with the title bar Enter Parameter Value, Forms!frmCustomRpt!lstYears.Value,
when I click on the query object to execute the query.



 Start Free Trial