resources for parameter forms

[punking315]

Are there any good tutorials or resources describing how to connect a
parameter form to a report and underlying query?
I have a report that works great with parameters in the underlying query.
However , when I trigger the parameter input form for thr report (using open
& close event) it is not passing the data from the form to the underlying
query.

I used the example in the help files of access2007.

BTW, here is my SQL of the query:

SELECT Query1.jobsite, Query1.scope, Query1.taskdate, Query1.taskname,
Sum(Query1.empTotalCost) AS SumOfempTotalCost, Sum(Query1.equipTotalCost) AS
SumOfequipTotalCost, Sum(Query1.matTotalCost) AS SumOfmatTotalCost,
[SumOfempTotalCost]+[SumOfequipTotalCost]+[SumOfmatTotalCost] AS TotalCost
FROM Query1
GROUP BY Query1.jobsite, Query1.scope, Query1.taskdate, Query1.taskname
HAVING (((Query1.jobsite)=[Forms]![parameters]![jobsite]) AND
((Query1.scope)=[Forms]![parameters]![scope]) AND ((Query1.taskdate) Between
[Forms]![parameters]![startdate] And [Forms]![parameters]![enddate])) OR
(((Query1.scope)=[Forms]![parameters]![scope]) AND ((Query1.taskdate) Between
[Forms]![parameters]![startdate] And [Forms]![parameters]![enddate]) AND
(([Forms]![parameters]![jobsite]) Is Null)) OR
(((Query1.jobsite)=[Forms]![parameters]![jobsite]) AND ((Query1.taskdate)
Between [Forms]![parameters]![startdate] And [Forms]![parameters]![enddate])
AND (([Forms]![parameters]![scope]) Is Null)) OR (((Query1.taskdate) Between
[Forms]![parameters]![startdate] And [Forms]![parameters]![enddate]) AND
(([Forms]![parameters]![jobsite]) Is Null) AND
(([Forms]![parameters]![scope]) Is Null));



thanks for any help

 

[Allen Browne]

There are a couple of ways to do this.

One is to create a parameter query (like the one in your example), so that
the query reads the parameter values from the controls on the form. The
report is then based on this query, so it shows only the records that meet
the condition. You do not need to do anything in the report's Open or Close
event: just open the form first. Typically you have a command button on the
form that opens the report from the form.

The other way is to omit the criteria from the query. You still use the form
to enter the limiting parameters, and the command button to open the report.
In the Click event of the command button, you build the string to filter the
report based on the controls where the user entered something. You then use
this string as the WhereCondition for OpenReport.

For an example of building such a string, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
Download the example database, and see how you build the string. The example
filters the search form itself, but you build exactly the same string to
filter a report.

There are several advantages of using this second approach rather than the
parameter query:
a) The report and its query can work without the form, or even with other
forms.

b) It is much easier and more efficient to offer optional criteria, i.e. the
filter string only contains the values where the user actually entered
something.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Are there any good tutorials or resources describing how to connect a
parameter form to a report and underlying query?
I have a report that works great with parameters in the underlying query.
However , when I trigger the parameter input form for thr report (using
open
& close event) it is not passing the data from the form to the underlying
query.

I used the example in the help files of access2007.

BTW, here is my SQL of the query:

SELECT Query1.jobsite, Query1.scope, Query1.taskdate, Query1.taskname,
Sum(Query1.empTotalCost) AS SumOfempTotalCost, Sum(Query1.equipTotalCost)
AS
SumOfequipTotalCost, Sum(Query1.matTotalCost) AS SumOfmatTotalCost,
[SumOfempTotalCost]+[SumOfequipTotalCost]+[SumOfmatTotalCost] AS TotalCost
FROM Query1
GROUP BY Query1.jobsite, Query1.scope, Query1.taskdate, Query1.taskname
HAVING (((Query1.jobsite)=[Forms]![parameters]![jobsite]) AND
((Query1.scope)=[Forms]![parameters]![scope]) AND ((Query1.taskdate)
Between
[Forms]![parameters]![startdate] And [Forms]![parameters]![enddate])) OR
(((Query1.scope)=[Forms]![parameters]![scope]) AND ((Query1.taskdate)
Between
[Forms]![parameters]![startdate] And [Forms]![parameters]![enddate]) AND
(([Forms]![parameters]![jobsite]) Is Null)) OR
(((Query1.jobsite)=[Forms]![parameters]![jobsite]) AND ((Query1.taskdate)
Between [Forms]![parameters]![startdate] And
[Forms]![parameters]![enddate])
AND (([Forms]![parameters]![scope]) Is Null)) OR (((Query1.taskdate)
Between
[Forms]![parameters]![startdate] And [Forms]![parameters]![enddate]) AND
(([Forms]![parameters]![jobsite]) Is Null) AND
(([Forms]![parameters]![scope]) Is Null));



thanks for any help

 

[punking315]

thank you for your help. I did find my mistake. I will share the fix.

When I made the form I used a combobox for two of the parameters. each of
the comboboxes had a lookup the went by the ID field and I displayed the
"common name" field (2 columns with 0",1" ) it was bound on the first column
which was the ID #. That ID # was what was being passed to the query as a
parameter, not the common name. thus, the combobox value made no sense to the
query's parameter criteria. I eliminated the iD column in the combobox
lookup, had only the "common name" column showing, and that value is what is
passed to the query's paramter criteria. Thanks for getting me thinking.

 

[Albert D. Kallal]

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = " & cboSalesRep & ""

end if

select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = " & cobCity & ""
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.