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_Location 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("qryChild1")
qdf.Parameters("Menu_Price_Date") = Date
qdf.Parameters("Company_Location") = Me.txtCompanyLocation

Me.Recordsource = _
qryParent

' ....Code Continues....

Any thoughts or suggestions would be extremely well received.

Thanks,

Kelii

[Kelii]

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]

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_Location and
Menu_Price_Date.

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

qryChild1 in SQL:
PARAMETERS [Company_Location] Text ( 255 ), [Menu_Price_Date]
DateTime;
SELECT tblMenuPrice.Price_ID, tblMenuPrice.Company_Location,
tblMenuPrice.Menu_Price_Date, tblMenuPrice.Menu_Description_ID,
tblMenuPrice.Menu_Item_Price
FROM tblMenuPrice
WHERE (((tblMenuPrice.Company_Location)=[Company_Location]) AND
((tblMenuPrice.Menu_Price_Date)<=[Menu_Price_Date]));

[Tom van Stiphout]

On Fri, 18 Jul 2008 20:49:00 -0700 (PDT), Kelii <ke****@yahoo.com>
wrote:

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

-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_Location 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("qryChild1")
qdf.Parameters("Menu_Price_Date") = Date
qdf.Parameters("Company_Location") = Me.txtCompanyLocation

Me.Recordsource = _
qryParent

' ....Code Continues....

Any thoughts or suggestions would be extremely well received.

Thanks,

Kelii

[Kelii]

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]

Kelii <ke****@yahoo.comwrote in news:5c8560b2-88ad-475a-b330-
83**********@2g2000hsn.googlegroups.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, "[GetMinEmployeeID]", 3)
SQL = Replace(SQL, ";", "")
Debug.Print DBEngine(0)(0).OpenRecordset(Replace(.Item("Query2 ").SQL,
"Query1", "(" & SQL & ")"))(0)
End With
End Sub

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

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]

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]

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 = _
fMenuItemWithLastPrice(Me.txtCompanyLocation, Date)

....code continues...
End Sub

Function fMenuItemWithLastPrice _
(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: fMenuItemWithLastPrice (Me.Company_Location, _
'Me.Sale_Date)
'Note: this SQL string build on fLastMenuPrice

Dim strSQL As String

'This compiles the query string
strSQL = _
"SELECT " & _
"tblItemDetails.Item_Description_Number,
tblItemDetails.Class, " & _
"tblItemDetails.Item_Description_ID,
tblItemDetails.Item_Unit_of_Measure, " & _
"tblItemDetails.Item_Location, tblItemDetails.Item_Type, "
& _
"tblItemDetails.Item_Category,
tblItemDetails.Active_Status, " & _
"tblItemDetails.Item_Par, tblItemDetails.Item_EOQ, " & _
"tblItemDetails.Sub_Assembly_Total_Yield,
tblItemDetails.Memo, " & _
"tblMenuPrice.Company_Location, rsPrice3.Menu_Price_Date,
" & _
"tblMenuPrice.Menu_Item_Price " & _
"FROM " & _
"(tblItemDetails INNER JOIN tblMenuPrice ON
tblItemDetails.Item_Description_ID = " & _
"tblMenuPrice.Menu_Description_ID) INNER JOIN " & _
"(" & fLastMenuPrice(strCompanyName, datLastDate) & ")
rsPrice3 " & _
"ON tblMenuPrice.Price_ID = rsPrice3.Price_ID " & _
"WHERE " & _
"(((tblMenuPrice.Company_Location)='" & _
FindFirstFixup(strCompanyName) & "'))"

fMenuItemWithLastPrice = 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: fMenuItemWithLastPrice (Me.Company_Location, _
'Me.Sale_Date)

Dim strSQL As String

'This compiles the query string
strSQL = _
"SELECT " & _
"tblMenuPrice.Price_ID, tblMenuPrice.Company_Location, " &
_
"tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Description_ID, " & _
"tblMenuPrice.Menu_Item_Price " & _
"FROM " & _
"(SELECT " & _
"Max(rsPrice1.Menu_Price_Date) AS
MaxOfMenu_Price_Date, " & _
"rsPrice1.Menu_Description_ID " & _
"FROM " & _
"(SELECT " & _
"tblMenuPrice.Price_ID,
tblMenuPrice.Company_Location, " & _
"tblMenuPrice.Menu_Price_Date,
tblMenuPrice.Menu_Description_ID, " & _
"tblMenuPrice.Menu_Item_Price " & _
"FROM " & _
"tblMenuPrice " & _
"WHERE " & _
"(((tblMenuPrice.Company_Location)='" & _
FindFirstFixup(strCompanyName) & "') AND " & _
"((tblMenuPrice.Menu_Price_Date)<=# "
strSQL = strSQL & _
datLastDate & "#))) rsPrice1 " & _
"GROUP BY rsPrice1.Menu_Description_ID) rsPrice2 " & _
"INNER JOIN " & _
"tblMenuPrice ON (rsPrice2.MaxOfMenu_Price_Date = " & _
"tblMenuPrice.Menu_Price_Date) AND " & _
"(rsPrice2.Menu_Description_ID =
tblMenuPrice.Menu_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