Parameter query into recordset in VBA

[Atomic Shrimp]

I had problems getting a query to go into a recordset in VBA, because the criteria in the query referenced a value in one of my form controls - apparently, because the query is passed to Jet for execution, and because Jet doesn't know anything about forms, it generated an error 'too few parameters - expected 1'

I sorted that problem by means of the instructions in this MS article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

The solution is to create a querydef object, set the querydef object as the saved query, assign the parameter, then set the recordset as the opened result of the querydef. No problem

BUT a problem does occur when the query is based on *another* query that also needs parameters based on control values on a form.

I could try to rewrite the second query, but I'm reluctant to do so, because of the way it needs to work - Query1 selects a bunch of records based on quite complex criteria, plus a parameter from the form, query2 needs to select the precisely complementary set of records - essentially, it's a 'find unmatched' query based on the original table and the first query.

So... is there any way around this - how do I pass a parameter to a query that is underlying the one I want in my recordset?

 

[DCrake]

Mike

Here is a sample I posted a while ago that does exactly what you want by way of functions and public variables.


http://www.access-programmers.co.uk/forums/showthread.php?t=167718


Alls you need to do is to enter the function call in the source query.

David

 

[WIS]

I had problems getting a query to go into a recordset in VBA, because the criteria in the query referenced a value in one of my form controls - apparently, because the query is passed to Jet for execution, and because Jet doesn't know anything about forms, it generated an error 'too few parameters - expected 1'

I sorted that problem by means of the instructions in this MS article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

The solution is to create a querydef object, set the querydef object as the saved query, assign the parameter, then set the recordset as the opened result of the querydef. No problem

BUT a problem does occur when the query is based on *another* query that also needs parameters based on control values on a form.

I could try to rewrite the second query, but I'm reluctant to do so, because of the way it needs to work - Query1 selects a bunch of records based on quite complex criteria, plus a parameter from the form, query2 needs to select the precisely complementary set of records - essentially, it's a 'find unmatched' query based on the original table and the first query.

So... is there any way around this - how do I pass a parameter to a query that is underlying the one I want in my recordset?

Have you tried this: This solves the "Too few parameters - expected nn".

Courtesy of Litwin, Getz et al

Dim db As Database, rst As Recordset, qdf As QueryDef, prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySomethingWithParameter")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)

I put this on a prev post about 2 mths ago.

 

[Atomic Shrimp]

Have you tried this: This solves the "Too few parameters - expected nn".

Courtesy of Litwin, Getz et al

Dim db As Database, rst As Recordset, qdf As QueryDef, prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySomethingWithParameter")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)

I put this on a prev post about 2 mths ago.

I came across that post in my searches and I did try it, but it doesn't work in this case because the parameter is not in the query I'm assigning to the recordset, but instead is in a query that the main query merely uses - it doesn't seem to be possible to pass parameters to a child query in this way, or by explicitly naming everything.
I do have explicitly assigned parameters elsewhere that I'm going to convert to this method, just because I think it's more robust in dealing with minor changes such as renaming of elements, etc., so thanks for bringing it up.

DCrake's method worked a treat in the query-on-a-parameter-query scenario though. Thanks