too few parameters, Expected 2 1

[Poduska]

I have a working query between 2 tables which utilizes two dates from 2 Active x calendar controls for the Start_date and End_date range which reside on a form. Seems easy enough and the query works. The query also works when I key in the actual date "1/25/2007" as a criteria instead of the Calendar criteria but not 1/25/2007.

However, when I add the query to my project, which worked before the caledars, I now get the error
Too Few Parameters, Expected 2

Here is the snippet of code which creates my recordset.

'open recordset on the Shipments table
    Set rst = New ADODB.Recordset
    With rst
        .Open "[qry_rpt_7_shipments_by_store_final]", strConn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
    End With

Here is the where clause from my query, it is pulling from Oracle so I need to only look at the date portion of the Date/Time field Oracle utilizes. Again, this works when I run the query from the query list but not from the VB.

WHERE (((DateValue([APPS_XXCOC_SHIPMENTS_MV]![SALES_DATE])) Between [Forms]![frm_date_selector]![start_date] And [Forms]![frm_date_selector]![end_date]))

Is it that when I run the saved query from VB it can't find the fields on the form or is it bringing back 1/25/2007 instead of "1/25/2007" with the double quotes?

I'm afraid that someone is going to say run it from SQL statement in the VB code, and I'll try if it is the only way.

Thanks for any ideas.

 

[Golom]

Here's some generic code.

   Dim cmd As New ADODB.Command
   Dim rs  As New ADODB.Recordset
    
   ' Create the command
   Set cmd.ActiveConnection = strConn
   cmd.CommandText = "[qry_rpt_7_shipments_by_store_final]"
   
   ' Execute the Command, passing in the
   ' values for the parameters
   Set rs = cmd.Execute(, Array( _
      #[Forms]![frm_date_selector]![start_date]#, _
      #[Forms]![frm_date_selector]![end_date]#), _
      adCmdStoredProc)

The problem is that opening a saved query with ADO requires that you explicitly set the parameters. The query will not pick up the references to the controls on the form because it isn't Access that's opening the query ... it's ADO which knows nothing about forms or controls.

 

[Poduska]

I have tried to get this new idea to work in Northwind as my actual query takes a while to work. I cannot get it to compile as it still has two errors

Option Compare Database
Option Explicit
Sub find_orders()

Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs  As New ADODB.Recordset
Dim strConn As String
Dim rst As ADODB.Recordset
 
    
strConn = "Provider=Microsoft.jet.oledb.4.0;" & _
          "Data Source=" & CurrentProject.path & _
          "\northwind.mdb"
    Set conn = New ADODB.Connection
  
    Set rst = New ADODB.Recordset
  
   ' Create the command
   Set cmd.ActiveConnection = strConn
   cmd.CommandText = "[qry_rpt_7_shipments_by_store_final]"
   
   ' Execute the Command, passing in the
   ' values for the parameters
   Set rs = cmd.Execute(,Array(#[Forms]![frm_date_selector]![start_date]#,
      #[Forms]![frm_date_selector]![end_date]#), _
      adCmdStoredProc)
End Sub

I get the error "Compile Error, Object required" when I attempt to run and the var strConn which is my connection string is highlighted.

   ' Create the command
   Set cmd.ActiveConnection = [b]strConn[/b]

Also the cmd code at the end which creates the rst,

   Set rs = cmd.Execute(,Array([b]#[/b][Forms]![frm_date_selector]![start_date]#,
      #[Forms]![frm_date_selector]![end_date]#), _
      adCmdStoredProc)

is highlighted RED (error) and when I attempt to edit it it always highlights the first # with the message Compile error: Expected expression, it appears that I may be missing something. I have never utilized the cmd.execute before and am somewhat mystified. I don't have a clue concerning the operation of the array clause as arrays basically just scare me!
Time to learn.
I do understand Golom's statement that ADO cannot understand forms etc. used within a saved query and the Array and cmd. seems to be a good solution.

Any more hints I can use to get this to work?

Thanks
Tek-Tips rules!

 

[Golom]

Sorry ... screwed up the syntax

Set rs = cmd.Execute(,Array(CDate([Forms]![frm_date_selector]![start_date]),
      CDate([Forms]![frm_date_selector]![end_date])), _
      adCmdStoredProc)

 

[Poduska]

I finally have it working!!!!

OK, Ok, Golom has empowered me by sharing his wisdom and providing me with bits of his knowledge that has permitted me to get this query working. (sound fancy enough?)

I had to remove the set from the Set cmd.ActiveConnection = strConn to get rid of the last error.

Many, Many thanks for your assistance.

Here is the code I have.

Sub find_orders()

Dim conn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim cmd As New ADODB.Command
Dim rs  As New ADODB.Recordset
Dim strConn As String
Dim rst As ADODB.Recordset
 
    
strConn = "Provider=Microsoft.jet.oledb.4.0;" & _
          "Data Source=" & CurrentProject.Path & _
          "\tesco_reporting.mdb"
    
   cmd.ActiveConnection = strConn
   cmd.CommandText = "[qry_rpt_7_shipments_by_store_final]"
   
   ' Execute the Command, passing in the
   ' values for the parameters
    Set rs = cmd.Execute(, Array(CDate([Forms]![frm_date_selector]![start_date]), _
        CDate([Forms]![frm_date_selector]![end_date])), _
        adCmdStoredProc)

'Print out the results from the recordset
If Not rs.EOF Then
    Debug.Print rs.GetString
Else
    MsgBox ("No Records Found" & Chr(13) & _
    "Try another date")
End If

End Sub

Thanks again, many STARS!

 

[Poduska]

Golom,

I have it working (thanks), and even added another parameter from another form to the Array but I want to understand the array for future use. Us there a specific order that the parameters need to be within the array to make it function or is the vb just using the Array to find the field within the form?

This works great this time but I just want to make sure I know how to do it and not just your code works!

 

[Golom]

The parameters must be in the same order as they appear in the query. There is an alternate method in which you can set each parameter separately but this one requires the left-to-right ordering.

 

[Poduska]

So the way they appear, when you edit the SQL generated by Access from the QBE grid, is the order for the Array. So I just got LUCKY that it worked for me as my new criteria showed up as a "Having" clause at the end.

I guess as long as the SQL does not get rearanged (somehow) I am safe. However, I think I should learn how to code the SQL direct and take the QBE grid out of the equation.

Any good sources or threads of how to do this? Everyone seems to do it different, a bit confusing.

Thanks again for your help.

SQL

SELECT APPS_XXCOC_SHIPMENTS_MV.PRODUCT_LINE, APPS_XXCOC_SHIPMENTS_MV.EAN, APPS_XXCOC_SHIPMENTS_MV.TPND, APPS_XXCOC_SHIPMENTS_MV.ARTIST AS Artist, APPS_XXCOC_SHIPMENTS_MV.TITLE AS Title, APPS_XXCOC_SHIPMENTS_MV.SUPPLIER AS Supplier, Sum(APPS_XXCOC_SHIPMENTS_MV.DC_STK_QTY) AS [DC Stock Qty], Sum(APPS_XXCOC_SHIPMENTS_MV.SHIPMENT) AS Shipments, Sum(APPS_XXCOC_SHIPMENTS_MV.IN_STORE_STK) AS [Instore Stock], Sum((APPS_XXCOC_SHIPMENTS_MV!IN_STORE_STK+APPS_XXCOC_SHIPMENTS_MV!DC_STK_QTY)) AS [Total Stock]
FROM APPS_XXCOC_SHIPMENTS_MV INNER JOIN tbl_Chart_Item ON APPS_XXCOC_SHIPMENTS_MV.EAN = tbl_Chart_Item.EAN
WHERE (((DateValue([APPS_XXCOC_SHIPMENTS_MV]![SALES_DATE])) Between [Forms]![frm_date_selector]![start_date] And [Forms]![frm_date_selector]![end_date]) AND ((APPS_XXCOC_SHIPMENTS_MV.SHIPMENT)>0))
GROUP BY APPS_XXCOC_SHIPMENTS_MV.PRODUCT_LINE, APPS_XXCOC_SHIPMENTS_MV.EAN, APPS_XXCOC_SHIPMENTS_MV.TPND, APPS_XXCOC_SHIPMENTS_MV.ARTIST, APPS_XXCOC_SHIPMENTS_MV.TITLE, APPS_XXCOC_SHIPMENTS_MV.SUPPLIER
[b]HAVING (((APPS_XXCOC_SHIPMENTS_MV.PRODUCT_LINE)=[Forms]![frm_product_line]![lb_product_line]))[/b]
ORDER BY APPS_XXCOC_SHIPMENTS_MV.PRODUCT_LINE, APPS_XXCOC_SHIPMENTS_MV.EAN;

VB

Option Compare Database
Option Explicit
Sub find_orders()

Dim conn As ADODB.Connection
'Dim cat As ADOX.Catalog
Dim cmd As New ADODB.Command
Dim rs  As New ADODB.Recordset
Dim strConn As String
Dim pl As String

    
strConn = "Provider=Microsoft.jet.oledb.4.0;" & _
          "Data Source=" & CurrentProject.Path & _
          "\tesco_reporting.mdb"
    'Set conn = New ADODB.Connection
  
    'Set rst = New ADODB.Recordset
  
   ' Create the command
   'Set cmd = New ADODB.Command
   
   cmd.ActiveConnection = strConn
   cmd.CommandText = "[Copy of qry_rpt_7_shipments_by_line_product_line form test1]"
   
   ' Execute the Command, passing in the
   ' values for the parameters
    Set rs = cmd.Execute(, Array(CDate([Forms]![frm_date_selector]![start_date]), _
        CDate([Forms]![frm_date_selector]![end_date]), [b][Forms]![frm_product_line]![lb_product_line].Column(0))[/b], _
        adCmdStoredProc)
        
If Not rs.EOF Then
    Debug.Print rs.GetString
Else
    MsgBox ("No Records Found" & Chr(13) & _
    "Try another date")
End If

Set conn = Nothing
Set rs = Nothing
Set cmd = Nothing
End Sub