Chaining parameter queries (1 Viewer)

Petr Danes

Registered User.
Local time
Today, 09:41
Joined
Aug 4, 2010
Messages
150
I call parameter queries from VBA fairly often, and they work fine - set a reference to the query, load the parameter and the query can then be used as a recordsource:

Code:
Set qdf = CurrentDb.QueryDefs("HypsoSouhrn")
With qdf
    .Parameters("ID Druhu") = 2669
End With
Set rst = qdf.OpenRecordset

I also use chains of queries - usually to do some sort of grouping or summing. Query A sets up a selected set of something, Query B groups all stuff from Query A, Query C summarizes stuff from Query B, and so on.

What I would like to do occasionally, and have never been able to figure out, is to chain a set of queries when one of the earlier ones is a parameter query. I have not been able to figure out how to feed a parameter to, say Query A, then open Query C as a recordsource. It keeps jumping up with a dialog box, asking for user input. I don't want that - I want to feed the parameter in by code.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:41
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you saying Query1 might have something like SELECT [Enter Value] As UserInput... and then Query2 might have something like SELECT UserInput * 2 As Result FROM Query1 and then try to assign Query2 to a recordset object?
 

Petr Danes

Registered User.
Local time
Today, 09:41
Joined
Aug 4, 2010
Messages
150
Yes, exactly that.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:41
Joined
Oct 29, 2018
Messages
21,358
Yes, exactly that.

Hi. Thanks for the clarification. I just gave it a try and didn't have any issues.

Here's what my code looks like:
Code:
Public Sub testparam()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryParam2")
With qdf
    .Parameters(0) = "two"
End With

Set rs = qdf.OpenRecordset

With rs
    Debug.Print rs.Fields("fname")
End With

Set qdf = Nothing
Set rs = Nothing
Set db = Nothing

End Sub
And here's the result in the Immediate Window.

Here's the SQL statement of qryParam2:
Code:
SELECT qryParam1.*
FROM qryParam1;
And here's the SQL statement of qryParam1:
Code:
SELECT Table1.ID, Table1.FName, Table1.LName, Table1.[email/com]
FROM Table1
 WHERE (((Table1.FName)=[enter fname]));
Is this anywhere close to what you're trying?
By the way, here's what my table looks like:
 

Attachments

  • param.PNG
    param.PNG
    12.3 KB · Views: 419
  • table.PNG
    table.PNG
    8.2 KB · Views: 132

Petr Danes

Registered User.
Local time
Today, 09:41
Joined
Aug 4, 2010
Messages
150
Oh, good grief - is that how to do it? Submit the parameter to the final query, even though it actually belongs to an earlier one? Seems like that should have occurred to me, somewhere along the way. Just tried it and it works great.

I also tried chaining several parameter queries, and I had to present all the parameters for all the queries to the last query. The order is earliest to latest, but I almost always use the actual name of the parameter, so that wouldn't be a problem.

Thanks for the tip - this has been niggling at me for years. I always wound up doing it some other way, because I couldn't figure this out. Glad to have it finally sussed out.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:41
Joined
Oct 29, 2018
Messages
21,358
Oh, good grief - is that how to do it? Submit the parameter to the final query, even though it actually belongs to an earlier one? Seems like that should have occurred to me, somewhere along the way. Just tried it and it works great.

I also tried chaining several parameter queries, and I had to present all the parameters for all the queries to the last query. The order is earliest to latest, but I almost always use the actual name of the parameter, so that wouldn't be a problem.

Thanks for the tip - this has been niggling at me for years. I always wound up doing it some other way, because I couldn't figure this out. Glad to have it finally sussed out.
Hi Petr. Glad to hear I wasn't steering you towards the wrong direction. Good luck with your project.
 

Users who are viewing this thread

Top Bottom