0

I have predefined query stored in database "SampleQuery" which looks more or less like this:

SELECT Tree.NodeID, ASibling.NodeID, ASibling.Value FROM Tree LEFT JOIN Tree AS ASibling ON Tree.ParentNodeID = ASibling.ParentNodeID WHERE (Tree.NodeID=[TempVars]![SelectedNodeID]);

Before it's first time executed value for [TempVars]![SelectedNodeID] is set in OnClick event. When executed from navigation panel in Access it works correctly. But when I want to execute select from this query via vba like this:

.. CurrentDb.OpenRecordset("SELECT Sum(Value) FROM SampleQuery;", dbOpenSnapshot)

it doesn't work - it shows an error Too Few parameters: expected 1

After I checked it looks like it doesn't prepare/evaluate [TempVars]![SelectedNodeID] within "SampleQuery" even though it's already initiated as it's variable, not pure parameter. When I change [TempVars]![SelectedNodeID] to directly entered number "(Tree.NodeID=355)" then everything works.

I wanted to have simple predefined query to return selection of records and via VBA use different aggregate summaries, but it looks like not possible.

Kind regards, delix

delix
  • 1
  • 1
  • Check the TempVar and set it again if needed before opening the recordset. – Kostas K. Jun 30 '23 at 15:53
  • 1
    See https://stackoverflow.com/a/49509616/7296893. TempVars are not supported in DAO recorders unless you use some trickery. See https://stackoverflow.com/a/66414149/7296893 for the trickery (second answer may be valid for you too) – Erik A Jun 30 '23 at 16:07
  • I never use dynamic parameters in query object. I apply filter to form or report or build into SQL statement in VBA. I've never used TempVars at all. – June7 Jun 30 '23 at 16:10
  • I never use TempVars. I just add a code module to the database and either add a public variable or make a full property with public get and set functions. pretty much the next option here: https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access For saved queries with parameters I also use the DAO option but that explanation doesn't show the most natural way to insert parameter values into saved queries. – mazoula Jul 03 '23 at 04:00
  • Thanks for responses; Setting TempVar just before opening recordset doesn't change anything; I can see that you're not using TempVars, but if you use public variables how do you refer to them in saved queries? After your answers I have the impression that TempVars is somewhat inappropriate or not practical. – delix Jul 04 '23 at 09:17

0 Answers0