Passing query parameter from form

[Jerry Anderson]

I haven't used Access very much, but I've rolled up my sleeves and dug in!
I have a form for Special Events by Department, and two subforms: one Salary
and Other Miscellaneous Costs (Master/Child fields Special Events and
Department).
I have built a parameter query that totals the records from two tables:
Salaries and Other Costs (again using Special Event and Department as search
criteria).
The form has two text boxes: one for the Special Event, the other for the
Department.
I want the query to use the values from these text boxes as parameters, and
then display the query results as Total Salary and Total Other Costs for the
respective Special Event and Department on the same form in two other text
boxes.
How do I do this? Code snippets are GREATLY appreciated!

 

[Allen Browne]

You can have the query read the values from the text boxes on the form. Just
type something like this in the Criteria row under the various fields:
[Forms].[Form1].[Text0]

This becomes quite messy where you have lots of text boxes, and some may be
left blank. to handle that kind of situation, it may be better to omit the
criteria from the query, and build a filter string instead.

The 2nd method in this article gives a simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive example that includes a sample database to
download and pull apart to see how it works:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

 

[Jerry Anderson]

Thanks a ton! You've been such a help, let me try for one more.
I used your "more comprehensive example" and everything works just fine. Now
I want to display the total of the records returned (the individual records
are good).
The SQL that I ended up with in the query is as follows:
PARAMETERS [Forms]![frmClientSearch]![txtFilterCostCenter] Text ( 255 ),
[Forms]![frmClientSearch]![txtFilterEventName] Text ( 255 );

SELECT Sum(ProjectedSalaries.TotalCost) AS SumOfTotalCost
FROM ProjectedSalaries
HAVING (((ProjectedSalaries.BudgetUnit) Like "*" &
[Forms]![frmClientSearch]![txtFilterCostCenter] & "*") AND
((ProjectedSalaries.Event)=[Forms]![frmClientSearch]![txtFilterEventName]));

I built the code that specifies the "WHERE" criteria for the form filter,
and everything works just fine! Now I need to display the value of
SumOfTotalCost on the form. I added an unbound text box and added the
following code:

txtSumOfTotalCost=SumOfTotalCost

It doesn't work. How do I accomplish this last piece?

You can have the query read the values from the text boxes on the form. Just
type something like this in the Criteria row under the various fields:
[Forms].[Form1].[Text0]

This becomes quite messy where you have lots of text boxes, and some may be
left blank. to handle that kind of situation, it may be better to omit the
criteria from the query, and build a filter string instead.

The 2nd method in this article gives a simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive example that includes a sample database to
download and pull apart to see how it works:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

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

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

I haven't used Access very much, but I've rolled up my sleeves and dug in!
I have a form for Special Events by Department, and two subforms: one
Salary
and Other Miscellaneous Costs (Master/Child fields Special Events and
Department).
I have built a parameter query that totals the records from two tables:
Salaries and Other Costs (again using Special Event and Department as
search
criteria).
The form has two text boxes: one for the Special Event, the other for the
Department.
I want the query to use the values from these text boxes as parameters,
and
then display the query results as Total Salary and Total Other Costs for
the
respective Special Event and Department on the same form in two other text
boxes.
How do I do this? Code snippets are GREATLY appreciated!

 

[Allen Browne]

Is TotalCost actually in this form?

If it is in the query that feeds the form, you can just add a text box to
the Form Footer section, with Control Source of:
=Sum([TotalCost])

As you filter the form, the total will reflect that, because it's summing
the actual records you have in the form.

Post back if I have not understood what you are doing.

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

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

Thanks a ton! You've been such a help, let me try for one more.
I used your "more comprehensive example" and everything works just fine.
Now
I want to display the total of the records returned (the individual
records
are good).
The SQL that I ended up with in the query is as follows:
PARAMETERS [Forms]![frmClientSearch]![txtFilterCostCenter] Text ( 255 ),
[Forms]![frmClientSearch]![txtFilterEventName] Text ( 255 );

SELECT Sum(ProjectedSalaries.TotalCost) AS SumOfTotalCost
FROM ProjectedSalaries
HAVING (((ProjectedSalaries.BudgetUnit) Like "*" &
[Forms]![frmClientSearch]![txtFilterCostCenter] & "*") AND
((ProjectedSalaries.Event)=[Forms]![frmClientSearch]![txtFilterEventName]));

I built the code that specifies the "WHERE" criteria for the form filter,
and everything works just fine! Now I need to display the value of
SumOfTotalCost on the form. I added an unbound text box and added the
following code:

txtSumOfTotalCost=SumOfTotalCost

It doesn't work. How do I accomplish this last piece?

You can have the query read the values from the text boxes on the form.
Just
type something like this in the Criteria row under the various fields:
[Forms].[Form1].[Text0]

This becomes quite messy where you have lots of text boxes, and some may
be
left blank. to handle that kind of situation, it may be better to omit
the
criteria from the query, and build a filter string instead.

The 2nd method in this article gives a simple example:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive example that includes a sample database to
download and pull apart to see how it works:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

message

I haven't used Access very much, but I've rolled up my sleeves and dug
in!
I have a form for Special Events by Department, and two subforms: one
Salary
and Other Miscellaneous Costs (Master/Child fields Special Events and
Department).
I have built a parameter query that totals the records from two tables:
Salaries and Other Costs (again using Special Event and Department as
search
criteria).
The form has two text boxes: one for the Special Event, the other for
the
Department.
I want the query to use the values from these text boxes as parameters,
and
then display the query results as Total Salary and Total Other Costs
for
the
respective Special Event and Department on the same form in two other
text
boxes.
How do I do this? Code snippets are GREATLY appreciated!

 

[Jerry Anderson]

Allen-
You are now my best friend in Perth, Western Australia! (Never mind that you
are also the only individual that I know in Australia!)