Set sub (embedded?) query parameters using VBA, call sub query in primary query with parameters comp

[Kelii]

I want to pass parameters to a sub (embedded?) query using VBA. Here
are the intricacies:
1. The "primary" query is constructed in VBA (using variable strSQL)
and utilized in a "db.OpenRecordset (strSQL, dbOpenDynaset)" procedure
2. The "primary" query that builds the dataset is simple and has no
form references:
e.g., Select qrySQ4.Field1, tblTable1.Field1, etc...
3. The "sub" query that is referenced in the primary query has several
form references:
e.g., SELECT tblTable2.Field1
FROM tblTable2
WHERE tblTable2.Field1 < Forms!ParentForm!Object

When executed, the db.OpenRecordset (strSQL, dbOpenDynaset) command
fails and requests additional parameters. I've identified the source
of the issue: query parameters cannot be resolved using DAO in the
db.OpenRecordset command. My first attempt at a work around is to set
the "sub" query parameters using DAO; e.g.,

Set qdQueryDefName = db.QueryDefs (qrySourceSubQuery)
qdQueryDefName.Parameters("Parameter1").Value = Forms!ParentForm!
Object
'More code
Set recROPFrom = db.OpenRecordset(strSQL, dbOpenDynaset)
'More code
End Sub

This has had no impact. The VBA OpenRecordset still fails and requests
parameters.

QUESTION: Does anyone know if the sub query parameters can be set
using VBA. If so, when those parameters are set, do they then carry
over to the "Primary" query, when the primary query calls the "sub"
queries recordset. If all of this is possible, is there sample
syntax / website / helpfile that I've missed in my research? If the
parameters cannot be effectively passed, what is a typical work around
for this type of issue.

Thanks in advance.

Best,

Kelii

Note: this a new branch of a discussion from comp.databases.ms-access
group. If you've followed that discussion and see this post, I
apologize.

 

[Ken Sheridan]

Kelii:

You are assigning a value to the parameter of the querydef object, but your
recordset is using a string expression (strSQL), not the querydef object. As
the SQL statement is built in code you can concatenate the value of the
control on the form into the string expression rather than referencing it as
a parameter, e.g.

strtSQL = "SELECT Field1, Field2 " _
"FROM SomeTable " & _
"WHERE EXISTS(" & _
"SELECT * " & _
"FROM SomeOtherTable " & _
"WHERE SomeOtherTable.ID = SomeTable.ID "& _
"AND Somefield = " & _
Forms!ParentForm!Object & ")"

This assumes object is a number. If its text:

"AND Somefield = """ & _
Forms!ParentForm!Object & """)"

Or if it’s a date:

"AND Somefield = #" & _
Format(Forms!ParentForm!Object,"mm/dd/yyyy") & "#)"

BTW in situations where you do assign values to a querydef object's
parameters you can loop through its Parameters collection and evaluate each.
That way the code doesn't need to know what the parameters are or how many
there are. Here's a snippet from some code which does this; the query name
is passed into the procedure as a strQuery argument:

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQuery)

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

Set rst = qdf.OpenRecordset

Ken Sheridan
Stafford, England

 

[Kelii]

Ken,

Thanks for the reply.

I've attempted to build the query string in the manner you suggest
above. I haven't had any luck. I believe the problem is that VBA
cannot resolve references to a form when the SQL command is used in an
OpenRecordset command.

So for example, this has worked for me in the past

Dim strSQL as String
strSQL = "SELECT * FROM tblTable1 WHERE tblTable1.Field1 = Forms!
ParentForm!Object"
Forms!ParentForm.Recordsource = strSQL

However, the following returns Error 91, 1 parameters expected
Dim db as Database
Dim recRecordset as DAO.Recordset
Dim strSQL as String

Set db = CurrentDb
strSQL = "SELECT * FROM tblTable1 WHERE tblTable1.Field1 = Forms!
ParentForm!Object"
recRecordset = db.OpenRecordset (strSQL, dbOpenDynaset)

To further complicate the issue, the form references reside in sub
queries, they do not reside in the parent query. So for example,
qryParent = SELECT * FROM qryChild
qryChild = SELECT * FROM tblTable1 WHERE tblTable1.Field1 = Forms!
ParentForm!Object

Anyhow, further thoughts would be appreciated.

Kelii

 

[Ken Sheridan]

Kelii:

Its more a question of DAO not evaluating the reference to the control
rather than VBA per se. In the first situation you are setting the
RecordSource property of a form. In that context there is no problem with it
referencing the control on the form as a parameter. In the second, however,
you are establishing a recordset object based on an SQL statement in which
the value of a control on a form. One way you could handle that would be to
establish a temporary querydef object and then evaluate each parameter in its
parameters collection, and then open the recordset as in the code snippet I
sent you, but that's a long winded way of doing it. Much simpler is to
concatenate the *value* of the control on the form into the string
expression, so that this evaluates to the literal value of the control not to
a reference to it, i.e. if Field1 is a text data type:

strSQL = "SELECT * FROM tblTable1 " & _
"WHERE Field1 = """ & Forms!ParentForm!Object & """"

Note the use of pairs of quotes characters to indicate literal quotes
characters within the literal strings in the above expression. If Field1 is
a number data type then you'd omit the delimiting quotes characters:

= " & Forms!ParentForm!Object

if it’s a date then use the # date delimiter character, as I explained in
my last post:

= #" & Forms!ParentForm!Object & "#"

As regards referencing a form in a subquery you are misunderstanding the
nature of a subquery, I'm afraid. Subqueries are not separate queries
themselves, rather SQL statements within an outer query which return values
which in some operate on the outer query; this could be to restrict the rows
returned by the outer query (the most common use as in the example in my last
post), it could be to return a result set from which the outer query returns
columns or it could be to return a value in a column in the outer query's
result set.

From the examples you give it seems that you are using the term 'subquery'
incorrectly to denote one query using another query in its FROM clause (your
qryParent). In this case you could either build the complete SQL statement
from scratch in code using only base tables in the statement and
concatenating the parameter into the string expression as with the above
simple example, or you'd evaluate the parameters of qryParent, which is a
saved query of that name drawing on qryChild, which is also a saved query of
that name and includes the reference to the form as a parameter. The fact
that the parameter is in qryChild is immaterial; its still a parameter of
qryParent by virtue of the former's use in the latter's FROM clause, so the
code would be like this:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryParent")

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

Set rst = qdf.OpenRecordset

However you do it the form must be open when the code is executed of course.

Ken Sheridan
Stafford, England

 

[Kelii]

Ken,

Should you still be watching this discussion, I apologize for the lack
of response. Your post gave me a bunch of different things to try.

First, I tend to use the term subquery to refer to just about any
query that is used to build another query. I'm a bit sloppy with my
language, apologies for that. I believe the terms are subquery and
perhaps embedded query? Anyhow, nevermind that, I tried your
suggestions and I ultimately got the correct results.

I tried for a long while to get the parameters to work as you show in
your final code snipet above. I couldn't get it to work. I don't know
why. I kept getting the same error.

So, I broke down and built the query as one long SQL string. This was
no small feat for me to accomplish, since the query references 4
separate querries, in which 2 of those queries reference another
query. Anyhow, after getting the SQL to work, I concatenated the
"parameters" to the SQL string and we're now off and running.

You've been super helpful, thanks again.

Kelii