Set parameters for nested query

[Kelii]

Well, I've been searching around the groups for an answer to this one,
and none of the suggestions seems to be working for me. I'm almost
positive that this is a simple fix, but for the life of me I can't
figure out what I'm doing wrong.

I have a form that uses a query as its recordsource; I'll call the
query qryParent.

qryParent is based on 2 nested queries; which is to say qryParent
selects from qryChild2 and qryChild2 selects from qryChild1.

qryChild1 has two parameters: Company_Locatio n and Menu_Price_Date

I am trying to set the parameter for qryChild1 using DAO, then opening
the form which uses qryParent as its recordsource.

The problem is that my code doesn't do anything, Access still prompts
me for the parameters.

Now, before it is suggested, I want to preempt the following:
1. I want to keep the queries generic so that I can use them in future
forms; therefore I am not willing to set the parameters to field
values on the various forms (which could be done)
2. I could write the whole query in SQL (I think) and set the
parameters in the SQL string; however, I would prefer to use the
'parameter setting process' b/c it limits the lines of code in an
already complex procedure

Ok, so here is the code I'm using:
Private Sub Form_Load()
On Error GoTo Error_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("q ryChild1")
qdf.Parameters( "Menu_Price_Dat e") = Date
qdf.Parameters( "Company_Locati on") = Me.txtCompanyLo cation

Me.Recordsource = _
qryParent

' ....Code Continues....

Any thoughts or suggestions would be extremely well received.

Thanks,

Kelii

[Kelii]

Re: Set parameters for nested query

One other thing to mention, and I think this may be important,
qryParent is not an action query. This is fairly self evident from the
description above, but I wanted to be explicit about that.

Kelii

[Kelii]

Re: Set parameters for nested query

Hmmm, after doing some research, it may be useful for everyone to see
the actual SQL of qryChild1 ... since my query may be improperly
written (i.e., for passing parameters).

Please note that the query works as I expect ... which is to say if I
open qryParent, I get two prompts for Company_Locatio n and
Menu_Price_Date .

However, as I've continued looking for a solution, perhaps this is not
'enough.'

qryChild1 in SQL:
PARAMETERS [Company_Locatio n] Text ( 255 ), [Menu_Price_Date]
DateTime;
SELECT tblMenuPrice.Pr ice_ID, tblMenuPrice.Co mpany_Location,
tblMenuPrice.Me nu_Price_Date, tblMenuPrice.Me nu_Description_ ID,
tblMenuPrice.Me nu_Item_Price
FROM tblMenuPrice
WHERE (((tblMenuPrice .Company_Locati on)=[Company_Locatio n]) AND
((tblMenuPrice. Menu_Price_Date )<=[Menu_Price_Date]));

[Tom van Stiphout]

Re: Set parameters for nested query

On Fri, 18 Jul 2008 20:49:00 -0700 (PDT), Kelii <keliie@yahoo.c om>
wrote:

Try this:
Set qdf = db.QueryDefs("q ryParent")

-Tom.
Microsoft Access MVP

>Well, I've been searching around the groups for an answer to this one,
>and none of the suggestions seems to be working for me. I'm almost
>positive that this is a simple fix, but for the life of me I can't
>figure out what I'm doing wrong.
>
>I have a form that uses a query as its recordsource; I'll call the
>query qryParent.
>
>qryParent is based on 2 nested queries; which is to say qryParent
>selects from qryChild2 and qryChild2 selects from qryChild1.
>
>qryChild1 has two parameters: Company_Locatio n and Menu_Price_Date
>
>I am trying to set the parameter for qryChild1 using DAO, then opening
>the form which uses qryParent as its recordsource.
>
>The problem is that my code doesn't do anything, Access still prompts
>me for the parameters.
>
>Now, before it is suggested, I want to preempt the following:
>1. I want to keep the queries generic so that I can use them in future
>forms; therefore I am not willing to set the parameters to field
>values on the various forms (which could be done)
>2. I could write the whole query in SQL (I think) and set the
>parameters in the SQL string; however, I would prefer to use the
>'parameter setting process' b/c it limits the lines of code in an
>already complex procedure
>
>Ok, so here is the code I'm using:
>Private Sub Form_Load()
>On Error GoTo Error_Handler
>
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
>
Set db = CurrentDb
Set qdf = db.QueryDefs("q ryChild1")
qdf.Parameters( "Menu_Price_Dat e") = Date
qdf.Parameters( "Company_Locati on") = Me.txtCompanyLo cation
>
Me.Recordsource = _
qryParent
>
>' ....Code Continues....
>
>Any thoughts or suggestions would be extremely well received.
>
>Thanks,
>
>Kelii

[Kelii]

Re: Set parameters for nested query

Tom,

Thanks for the reply. So I tried to get your suggestion to work last
night and this morning with no success.

A few things I did discover as I was working your suggestion:
1. I've located the query parameter setting code in the Form_Load
procedure of the form that used the query (i.e., qryParent) as its
recordsource

2. Relocating the code to Form_Open does not resolve the issue

3. Relocating the code to the event just prior to the Form_Open event
(e.g., cmdOpenForm) does not resolve the issue

4. The parameter names are properly set, or at least I'm pretty sure
they are, and the code will recognize that the parameters exist; for
example:

For each prm in qdf.Parameters
Debug.Print prm.Name
Next prm

Above code works and generates the following:
[Company_Name]
[Menu_Price_Date]

I would be grateful for any additional suggestions.

Thanks,

Kelii

[lyle fairfield]

Re: Set parameters for nested query

Kelii <keliie@yahoo.c omwrote in news:5c8560b2-88ad-475a-b330-
837b431525f2@2g 2000hsn.googleg roups.com:

Tom,
>
Thanks for the reply. So I tried to get your suggestion to work last
night and this morning with no success.
>
A few things I did discover as I was working your suggestion:
1. I've located the query parameter setting code in the Form_Load
procedure of the form that used the query (i.e., qryParent) as its
recordsource
>
2. Relocating the code to Form_Open does not resolve the issue
>
3. Relocating the code to the event just prior to the Form_Open event
(e.g., cmdOpenForm) does not resolve the issue
>
4. The parameter names are properly set, or at least I'm pretty sure
they are, and the code will recognize that the parameters exist; for
example:
>
For each prm in qdf.Parameters
Debug.Print prm.Name
Next prm
>
Above code works and generates the following:
[Company_Name]
[Menu_Price_Date]
>
I would be grateful for any additional suggestions.
>
Thanks,
>
Kelii

I don't know of any way to do what you want to do which is (I think ...
maybe):

Establish the parameter values of a query (Query1) and then call that
query by name from another query (Query2) which will "know" what those
parameter values are.

One can establish the parameter values of a query object and open a
recordset with that object's OpenRecordset method. But those parameter
values are not persistent. TTBOMK Query2 cannot easily use that query
object, which is not the same thing as the saved query.

My practice might be (if I used Saved Queries in JET or ACE, which I
seldom do) to load the query's SQL to a string, replace the parameter
names with the values I want and to use that string as a subquery.

eg

Public Sub Whim()
Dim SQL$
With DBEngine(0)(0). QueryDefs
..Refresh
SQL = .Item("Query1") .SQL
SQL = Replace(SQL, "[GetMinEmployeeI D]", 3)
SQL = Replace(SQL, ";", "")
Debug.Print DBEngine(0)(0). OpenRecordset(R eplace(.Item("Q uery2").SQL,
"Query1", "(" & SQL & ")"))(0)
End With
End Sub

Query1:SELECT *
FROM Employees
WHERE ID>[GetMinEmployeeI D];

Query2:SELECT *
FROM Query1;

The "Whim" sub prints 4 in the Immediate Window in Northwinds.

Newsclients/servers are likely to add line breaks to the code, which must
be removed.

[Kelii]

Re: Set parameters for nested query

Lyle,

Fair enough ... there are easy work arounds by saving the query or
writing SQL into the code. However, I am somewhat dissapointed as this
means more query tedium.

Question for you on follow up, why do you rarely use saved queries?

And, is your normal practice to simply write the SQL out in your code
and, for example, set the RecordSource property on Load or Open?

Thanks,

Kelii

[Kelii]

Re: Set parameters for nested query

In case future viewers are interested, I wanted to post my work
around.

I think this solution is almost as good, if not possibly better.

I created a module called kleQueryOutput.

Then I put a function in the module that compiles the SQL string based
on the two parameters (passed).

Then I set the recordsource equal to the function and passed the
appropriate parameters.

In the future, I can use the same function to produce an equivlaent
SQL string with different parameters (which makes me soooooo happy).

The SQL is tedious, so I avoid it at all possible costs. However, now
that its done, I'm off and rolling.

Here is the actual code should anyone be interested:
Private Sub Form_Load()
On Error GoTo Error_Handler

Me.RecordSource = _
fMenuItemWithLa stPrice(Me.txtC ompanyLocation, Date)

....code continues...
End Sub

Function fMenuItemWithLa stPrice _
(strCompanyName As String, datLastDate As Date)
On Error GoTo Error_Handler
'This function returns the SQL string for the query showing
'all the various details of a menu item as well as the last
'menu price given a specified Company and Date
'Inputs: company location, last maximum date (i.e., <= Date)
'Example: fMenuItemWithLa stPrice (Me.Company_Loc ation, _
'Me.Sale_Date)
'Note: this SQL string build on fLastMenuPrice

Dim strSQL As String

'This compiles the query string
strSQL = _
"SELECT " & _
"tblItemDetails .Item_Descripti on_Number,
tblItemDetails. Class, " & _
"tblItemDetails .Item_Descripti on_ID,
tblItemDetails. Item_Unit_of_Me asure, " & _
"tblItemDetails .Item_Location, tblItemDetails. Item_Type, "
& _
"tblItemDetails .Item_Category,
tblItemDetails. Active_Status, " & _
"tblItemDetails .Item_Par, tblItemDetails. Item_EOQ, " & _
"tblItemDetails .Sub_Assembly_T otal_Yield,
tblItemDetails. Memo, " & _
"tblMenuPrice.C ompany_Location , rsPrice3.Menu_P rice_Date,
" & _
"tblMenuPrice.M enu_Item_Price " & _
"FROM " & _
"(tblItemDetail s INNER JOIN tblMenuPrice ON
tblItemDetails. Item_Descriptio n_ID = " & _
"tblMenuPrice.M enu_Description _ID) INNER JOIN " & _
"(" & fLastMenuPrice( strCompanyName, datLastDate) & ")
rsPrice3 " & _
"ON tblMenuPrice.Pr ice_ID = rsPrice3.Price_ ID " & _
"WHERE " & _
"(((tblMenuPric e.Company_Locat ion)='" & _
FindFirstFixup( strCompanyName) & "'))"

fMenuItemWithLa stPrice = strSQL

Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Function

Function fLastMenuPrice _
(strCompanyName As String, datLastDate As Date)
On Error GoTo Error_Handler
'This function returns the SQL string for the query showing
'each menu item along with the last
'menu price given a specified Company and Date
'Inputs: company location, max date of price (i.e., <= date)
'Example: fMenuItemWithLa stPrice (Me.Company_Loc ation, _
'Me.Sale_Date)

Dim strSQL As String

'This compiles the query string
strSQL = _
"SELECT " & _
"tblMenuPrice.P rice_ID, tblMenuPrice.Co mpany_Location, " &
_
"tblMenuPrice.M enu_Price_Date,
tblMenuPrice.Me nu_Description_ ID, " & _
"tblMenuPrice.M enu_Item_Price " & _
"FROM " & _
"(SELECT " & _
"Max(rsPrice1.M enu_Price_Date) AS
MaxOfMenu_Price _Date, " & _
"rsPrice1.Menu_ Description_ID " & _
"FROM " & _
"(SELECT " & _
"tblMenuPrice.P rice_ID,
tblMenuPrice.Co mpany_Location, " & _
"tblMenuPrice.M enu_Price_Date,
tblMenuPrice.Me nu_Description_ ID, " & _
"tblMenuPrice.M enu_Item_Price " & _
"FROM " & _
"tblMenuPri ce " & _
"WHERE " & _
"(((tblMenuPric e.Company_Locat ion)='" & _
FindFirstFixup( strCompanyName) & "') AND " & _
"((tblMenuPrice .Menu_Price_Dat e)<=# "
strSQL = strSQL & _
datLastDate & "#))) rsPrice1 " & _
"GROUP BY rsPrice1.Menu_D escription_ID) rsPrice2 " & _
"INNER JOIN " & _
"tblMenuPri ce ON (rsPrice2.MaxOf Menu_Price_Date = " & _
"tblMenuPrice.M enu_Price_Date) AND " & _
"(rsPrice2.Menu _Description_ID =
tblMenuPrice.Me nu_Description_ ID)"

fLastMenuPrice = strSQL

Exit_Procedure:
On Error Resume Next
Exit Function
Error_Handler:
Select Case Err
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
Resume Exit_Procedure
End Select
End Function

Thanks to Lyle and Tom for their help.

Kelii