By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

how do you open a recordset with underlying parameters?

100+
P: 119
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd FROM AccountValues GROUP BY Account, SubAccount, Sector, Symbol;"
  2. 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.

Expand|Select|Wrap|Line Numbers
  1.     Dim CustDB As dataBase, CustData, CustTotals As Recordset
  2.     Dim qdf As QueryDef, prm As Parameter
  3.  
  4.     Set CustDB = DBEngine.Workspaces(0).Databases(0)
  5.     Set qdf = CustDB.QueryDefs("AccountValues")
  6.  
  7.     For Each prm In qdf.Parameters
  8.         prm.value = Eval(prm.Name)
  9.     Next prm
  10.  
  11.     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.
Jun 7 '07 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,599
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd FROM AccountValues GROUP BY Account, SubAccount, Sector, Symbol;"
  2. 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.

Expand|Select|Wrap|Line Numbers
  1.     Dim CustDB As dataBase, CustData, CustTotals As Recordset
  2.     Dim qdf As QueryDef, prm As Parameter
  3.  
  4.     Set CustDB = DBEngine.Workspaces(0).Databases(0)
  5.     Set qdf = CustDB.QueryDefs("AccountValues")
  6.  
  7.     For Each prm In qdf.Parameters
  8.         prm.value = Eval(prm.Name)
  9.     Next prm
  10.  
  11.     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?
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, qdf As DAO.QueryDef
  2.  
  3. Set MyDB = CurrentDb()
  4. Set qdf = MyDB.QueryDefs("qryEmployee")
  5.  
  6. 'Evaluate Parameters
  7. For Each prm In qdf.Parameters
  8.   prm.Value = Eval(prm.Name)
  9. Next prm
  10.  
  11. 'Open Recordset on Paramter Query
  12. Set MyRS = qdf.OpenRecordset(dbOpenDynaset)
  13. MyRS.MoveLast: MyRS.MoveFirst
  14.   MsgBox "Recordset Count before Filtering = " & MyRS.RecordCount
  15.  
  16. 'Let's apply a Filter to the Original Recordset
  17. MyRS.Filter = "[Platoon]='A'"
  18.  
  19. 'Create a New 'Filtered' Recordset based on the Original
  20. Dim rstFiltered As DAO.Recordset
  21. Set rstFiltered = MyRS.OpenRecordset()
  22. rstFiltered.MoveLast: rstFiltered.MoveFirst
  23.   MsgBox "Recordset Count after Filtering = " & rstFiltered.RecordCount
  24.  
  25. MyRS.Close
  26. 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.
Jun 8 '07 #2

maxamis4
Expert 100+
P: 295
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd FROM AccountValues GROUP BY Account, SubAccount, Sector, Symbol"
  2. Set CustData = qdf.OpenRecordset(strSQL)
you don't need the semicoln
Jun 8 '07 #3

100+
P: 119
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?
Jun 8 '07 #4

maxamis4
Expert 100+
P: 295
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
Jun 9 '07 #5

100+
P: 119
This is the query I would like to execute:

Expand|Select|Wrap|Line Numbers
  1. SELECT Account, SubAccount, Sector, Symbol, Sum(MarketValueStart) AS SumOfMarketValueStart, Sum(MarketValueEnd) AS SumOfMarketValueEnd
  2. FROM AccountValues
  3. 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.
Jun 11 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.