how do you open a recordset with underlying parameters?

[billelev]

Hi There,

I am trying to execute a query using openRecordset on a query called 'AccountValues'. The 'AccountValues' query relies on another query, 'Prices', which in turn relies on a form for two parameters.

The information flow is:

2 parameters -> Prices -> AccountBalances -> {my query}

Ideally, I would be able to perform the following:

strSQL = "SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd FROM AccountValues GROUP BY Account, SubAccount, Sector, Symbol;"
Set CustData = qdf.OpenRecordset(strSQL)

However, when I run this I get the following error message:

"Too few parameters. Expected 2."

I understand that this is because the 'Prices' query requires these parameters. Now, I have been able to open 'AccountValues' with the correct parameters using the following code.

    Dim CustDB As dataBase, CustData, CustTotals As Recordset
    Dim qdf As QueryDef, prm As Parameter
 
    Set CustDB = DBEngine.Workspaces(0).Databases(0)
    Set qdf = CustDB.QueryDefs("AccountValues")
 
    For Each prm In qdf.Parameters
        prm.value = Eval(prm.Name)
    Next prm
 
    Set CustData = qdf.OpenRecordset(dbOpenDynaset)

However, this is only opens the 'AccountValues' query with the correct parameters, not the QUERY ON AccountValues.

Does anyone know how I might be able to go one step further and execute a query on the AccountValues query?

Thanks in advance for your help.

[ADezii]

Hi There,

I am trying to execute a query using openRecordset on a query called 'AccountValues'. The 'AccountValues' query relies on another query, 'Prices', which in turn relies on a form for two parameters.

The information flow is:

2 parameters -> Prices -> AccountBalances -> {my query}

Ideally, I would be able to perform the following:

strSQL = "SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd FROM AccountValues GROUP BY Account, SubAccount, Sector, Symbol;"
Set CustData = qdf.OpenRecordset(strSQL)

However, when I run this I get the following error message:

"Too few parameters. Expected 2."

I understand that this is because the 'Prices' query requires these parameters. Now, I have been able to open 'AccountValues' with the correct parameters using the following code.

    Dim CustDB As dataBase, CustData, CustTotals As Recordset
    Dim qdf As QueryDef, prm As Parameter
 
    Set CustDB = DBEngine.Workspaces(0).Databases(0)
    Set qdf = CustDB.QueryDefs("AccountValues")
 
    For Each prm In qdf.Parameters
        prm.value = Eval(prm.Name)
    Next prm
 
    Set CustData = qdf.OpenRecordset(dbOpenDynaset)

However, this is only opens the 'AccountValues' query with the correct parameters, not the QUERY ON AccountValues.

Does anyone know how I might be able to go one step further and execute a query on the AccountValues query?

Thanks in advance for your help.

How about applying a Filter to your Recordset based on a Parameterized Query?

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, qdf As DAO.QueryDef
 
Set MyDB = CurrentDb()
Set qdf = MyDB.QueryDefs("qryEmployee")
 
'Evaluate Parameters
For Each prm In qdf.Parameters
  prm.Value = Eval(prm.Name)
Next prm
 
'Open Recordset on Paramter Query
Set MyRS = qdf.OpenRecordset(dbOpenDynaset)
MyRS.MoveLast: MyRS.MoveFirst
  MsgBox "Recordset Count before Filtering = " & MyRS.RecordCount
 
'Let's apply a Filter to the Original Recordset
MyRS.Filter = "[Platoon]='A'"
 
'Create a New 'Filtered' Recordset based on the Original
Dim rstFiltered As DAO.Recordset
Set rstFiltered = MyRS.OpenRecordset()
rstFiltered.MoveLast: rstFiltered.MoveFirst
  MsgBox "Recordset Count after Filtering = " & rstFiltered.RecordCount
 
MyRS.Close
rstFiltered.Close

Just a couple of things to remember when Filtering a Recordset:

1. The new Filtering doesn't take effect until you create a new Recordset based on the old one.
2. The Filter property doesn't apply to Table-Type Recordsets.
3. The new Filtering will never retrieve additional Rows from the original Source Tables. It will Filter only Rows that are in the base Recordset you are filtering.

NOTE: Let me know how you make out.

[maxamis4]

strSQL = "SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd FROM AccountValues GROUP BY Account, SubAccount, Sector, Symbol"
Set CustData = qdf.OpenRecordset(strSQL)

you don't need the semicoln

[billelev]

ADezii,

Thanks for your suggestion. It's not so much that I need to filter the initial recordset, but rather group and sum the data (as per the Select statement at the top of the post).

Any ideas how I could achieve that?

[maxamis4]

My best suggestion is to create your query in Access, under the queries tab. Once you get the query you want working, then look at the query in sql view and copy and paste that into your recordset. This should help you trouble shoot it better. Sorry I can't help you more but I would need to see the results displayed to understand where the syntax is wrong. But like I said create your sql view under quieres in access and when you get it to display how you like copy it to your recordset.


good luck

[billelev]

This is the query I would like to execute:

SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd
FROM AccountValues
GROUP BY Account, SubAccount, Sector, Symbol;

However, AccountValues is a query that takes inputs from a form. I cannot, therefore, open a recordSet with the above query without being asked to enter parameters.

I can, however, open a recordSet of 'AccountValues' using CustDB.QueryDefs("AccountValues"), but this will not allow me to GROUP the data, which is the whole point of me trying to do this.

It is not a question of how to write an SQL select statement. I know exactly which statement to use. I just don't know how to use the above statement AND feed in the correct parameters using VBA.