Parameter queries

[FireViking]

Hi all,
I have created a parameter query that asks for [start date] [End date]. Is it possible to capture each parameter for later use eg. Reports

cheers

 

[hsitz]

No, not if all they are is parameters in a query. But you may still be able to accomplish what you want by making a report that has that query as its recordsource. The user will then be prompted for the paramaters whenever the report is opened.

Alternatively, you build a form and have the user input the dates onto textboxes on the form, then you can have the query reference those textboxes. That way you could run the query and see the datasheet results, as well as run a report that has the query as its recordsource, both without reinputting the dates after you've typed them into the form.

The query can reference form values by referring to something like this as a parameter:

forms![NameOfForm]![NameOfTextBox]

-- Herb

 

[Frink]

Hallo,

I had a bit of a look at this problem, where I used a parameter query in a report as Herb suggests. I wanted to include the Parameter values on the report, but couldn't access them. They must be held somewhere, in some property of the report, but I couldn't find where.

- Frink

 

[hsitz]

Frink -- Oh, so you want to reference the paramaters IN the report, and not just in assembling the recordsource for the report.

There may be a way to do what you want directly by accessing the Parameters collection of the Querydef object for the query that is your report's recordsource. But I've never done that, and if you don't know what I'm talking about then go with the method in the next paragraph.

This requires an extra step or two, but is an easy way to do things:

(1) Make a form that has textboxes for you to input the values of the parameters you will use for your query. Label the textboxes appropriately so users know what they're entering.

(2) Put a command button on the form that calls the report you want.

(3) In the query, make sure the parameters are pointing the textboxes on the form (e.g., forms![formname]![textbox]).

(4) In the report, where you want to reference the values of the parameters, just make the same reference as you did in the query: forms![formname]![textbox]

(5) Once you've set it up this way, the report should be opened only by opening the form and clicking on the command button.

-- Herb

 

[Frink]

Hallo,

Thanks for your reply. I know that there are many ways of opening a report with different parameters, I was wondering if anyone had solved the specific problem of accessing (from code) the values of the parameters of a parameter query when used in a report.

My solution to the problem (which was a few months ago) was to open the report from a form where the parameters are entered in text boxes, as you have suggested.

- Frink

 

[hsitz]

Wish you would have said that more clearly beforehand; lots of different levels of users on this board.

Anyway, here's some code that's in the VBA help under an example for the Parameters collection of the QueryDef object. That should be enough to get you going. I'm curious why the method of using textboxes on a form won't work for you. -- Herb

----------------

Sub ParameterX()

Dim dbsNorthwind As Database
Dim qdfReport As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter

Set dbsNorthwind = OpenDatabase("Northwind.mdb&quot

' Create temporary QueryDef object with two
' parameters.
Set qdfReport = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
"FROM Orders WHERE ShippedDate BETWEEN " & _
"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
"ORDER BY EmployeeID&quot
Set prmBegin = qdfReport.Parameters!dteBegin
Set prmEnd = qdfReport.Parameters!dteEnd

' Print report using specified parameter values.
ParametersChange qdfReport, prmBegin, #1/1/95#, _
prmEnd, #6/30/95#
ParametersChange qdfReport, prmBegin, #7/1/95#, _
prmEnd, #12/31/95#

dbsNorthwind.Close

End Sub

Sub ParametersChange(qdfTemp As QueryDef, _
prmFirst As Parameter, dteFirst As Date, _
prmLast As Parameter, dteLast As Date)
' Report function for ParameterX.

Dim rstTemp As Recordset
Dim fldLoop As Field

' Set parameter values and open recordset from
' temporary QueryDef object.
prmFirst = dteFirst
prmLast = dteLast
Set rstTemp = _
qdfTemp.OpenRecordset(dbOpenForwardOnly)
Debug.Print "Period " & dteFirst & " to " & dteLast

' Enumerate recordset.
Do While Not rstTemp.EOF

' Enumerate Fields collection of recordset.
For Each fldLoop In rstTemp.Fields
Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
Next fldLoop

Debug.Print
rstTemp.MoveNext
Loop

rstTemp.Close

End Sub
---------------------------

 

[hsitz]

I assume you want to retrieve the values of the parameters by referencing

currentdb.Querydefs("[nameofquery&quot.parameters([paramindex]).value

I've tried this (on an open query) and can retrieve the name and other properties of each parameter, but for some reason get a blank value even when I think some number is assigned to the value. Maybe you'll have better luck.

 

[rose2]

Not sure if this is what you need. But, if you want to just display the parameters entered on the form, all you need to do is add a text box to your report.

For the Control Source of that text box, type the parameter (for example, [Enter beginning date:]). You can then use that text box to display the value of the parameter in the report. I usually set the text box to invisible, and then just refer to it in any other controls where I need the value.

 

[FireViking]

Hi all,
I can see I have started something!!
I have created a report where I use input boxes to obtain the start date an end date and save them in a string.
They are then used in the query which works fine

But for convenience I have a query designed outside of the code arena using the wizard. I will research a little with what has been suggested. It makes it hard if you have to write code for every query on a report just to capture the parameters.

cheers

 

[hsitz]

Fireking -- There's no reason to use code unless you are averse to calling your report from a form that has textboxes with the parameter values. Same as rose2 and I have said.

The only reason the code is here is because, for some reason, Frink doesn't want to use that method.

I agree that it's easier to include parameters in reports if you just show them on the report as textboxes that are referencing the paramater textboxes that were on the form. Forget about the code. -- Herb

 

[Frink]

Hallo again,

Herb - The Text box solution did work, I was just trying to do it in a (possibly) more sexy way. Then I got thinking about the code and that the parameters must be stored somewhere and why couldn't I get at them.

Your suggestion:
>I've tried this (on an open query) and can retrieve the
>name and other properties of each parameter, but for some
>reason get a blank value even when I think some number is
>assigned to the value

I tried the same thing at the time, with the same frustrating result.

Rose2 - I'm a little embarrassed. Your solution works fine, and would have been ideal for me a couple of months ago when I needed it. The trouble with me is that I like to delve into code too quickly

Still, it would be nice to be able to get the parameter values through the parameters collection, rather than from a text box...


FireViking - Sorry for hijacking your thread. I hope I haven't confused you. You're better off using the Text Boxes as Herb suggests and forgetting all the code stuff until you really need it.

- Frink

 

[FireViking]

Hi,
Frink,your not hijacking, this is what the forum is about. The only reason I dont want to code a report is the time factor. If the parameter collection can be recalled for later use on the report with minimal fuss then surely that is the way to go.
I still havnt had a chance to try the suggestions yet but will reply when I get the chance.

cheers

 

[FireViking]

Hi all,

Thank you all for your replies.
Roses reply using a text box with [start date] and [end date] worked fine.
This is an extremely simple solution for my original question.

Thank you Rose

Cheers.