Send parameters from form to query

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I would like to create a form that asks for start date and end date (with
defaults) that will then send these values to a report that starts with a
query. I know how to create the form, I just don't know how to call the
report(with query front end) with the inputted values. Thank you.
 
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The article offers two approaches:

1. Using parameters in the query, such as:
Between [Forms].[Form1].[Text0] And [Forms].[Form1].[Text2]

2. Using the WhereCondition of OpenReport.
 
Thank you for your response Allen. I did as instructed in your link but it
still isn't working. This is what I did:

created the input form, start and end date fields and command button -
naming them all as you directed
I cut and paste your code into the OnClick event of the command button and
made the following changes:
strReport = "[Hunter Ratios]" 'Put your report name in these quotes.
strDateField = "[Hunt Date]" 'Put your field name in the square brackets
in these quotes.

When I run the form, the dates appear (I set defaults) but when I press the
command button, nothing happens. I took the debug quote off in the event
procedure and there is not display.

Any idea what I'm doing wrong? Thanks.

Allen Browne said:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The article offers two approaches:

1. Using parameters in the query, such as:
Between [Forms].[Form1].[Text0] And [Forms].[Form1].[Text2]

2. Using the WhereCondition of OpenReport.

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

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

Lori said:
I would like to create a form that asks for start date and end date (with
defaults) that will then send these values to a report that starts with a
query. I know how to create the form, I just don't know how to call the
report(with query front end) with the inputted values. Thank you.
Click to expand...
Click to expand...
 
Okay, so we are talking about method 2 at:
http://allenbrowne.com/casu-08.html

Temporarily add tthis line at the top of the procedure:
Stop

Now test the code. It should stop on this line. Press F8 repeatedly to trace
exactly what's going on.

You can also add a line such as:
Debug.Print strWhere
just before the OpenReport line.
After running it, open the Immediate Window (Ctrl+G) to see what came out.

Hopefully this will help you do debug your code.

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

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

Lori said:
Thank you for your response Allen. I did as instructed in your link but
it
still isn't working. This is what I did:

created the input form, start and end date fields and command button -
naming them all as you directed
I cut and paste your code into the OnClick event of the command button and
made the following changes:
strReport = "[Hunter Ratios]" 'Put your report name in these quotes.
strDateField = "[Hunt Date]" 'Put your field name in the square
brackets
in these quotes.

When I run the form, the dates appear (I set defaults) but when I press
the
command button, nothing happens. I took the debug quote off in the event
procedure and there is not display.

Any idea what I'm doing wrong? Thanks.

Allen Browne said:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The article offers two approaches:

1. Using parameters in the query, such as:
Between [Forms].[Form1].[Text0] And [Forms].[Form1].[Text2]

2. Using the WhereCondition of OpenReport.

Lori said:
I would like to create a form that asks for start date and end date
(with
defaults) that will then send these values to a report that starts with
a
query. I know how to create the form, I just don't know how to call
the
report(with query front end) with the inputted values. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
I played around with it for awhile and now it works except for two questions:
1) When the parameter form calls the report preview, the form stays on the
screen. How do I remove this?
2) Do I add another command button on the form to print the report? What if
my client likes to see it in Report Layout mode? Is there a way to specify
this?

Thanks so much for your help. I've been pulling my hair out trying to
figure out this code.

Allen Browne said:
Okay, so we are talking about method 2 at:
http://allenbrowne.com/casu-08.html

Temporarily add tthis line at the top of the procedure:
Stop

Now test the code. It should stop on this line. Press F8 repeatedly to trace
exactly what's going on.

You can also add a line such as:
Debug.Print strWhere
just before the OpenReport line.
After running it, open the Immediate Window (Ctrl+G) to see what came out.

Hopefully this will help you do debug your code.

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

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

Lori said:
Thank you for your response Allen. I did as instructed in your link but
it
still isn't working. This is what I did:

created the input form, start and end date fields and command button -
naming them all as you directed
I cut and paste your code into the OnClick event of the command button and
made the following changes:
strReport = "[Hunter Ratios]" 'Put your report name in these quotes.
strDateField = "[Hunt Date]" 'Put your field name in the square
brackets
in these quotes.

When I run the form, the dates appear (I set defaults) but when I press
the
command button, nothing happens. I took the debug quote off in the event
procedure and there is not display.

Any idea what I'm doing wrong? Thanks.

Allen Browne said:
See:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

The article offers two approaches:

1. Using parameters in the query, such as:
Between [Forms].[Form1].[Text0] And [Forms].[Form1].[Text2]

2. Using the WhereCondition of OpenReport.

I would like to create a form that asks for start date and end date
(with
defaults) that will then send these values to a report that starts with
a
query. I know how to create the form, I just don't know how to call
the
report(with query front end) with the inputted values. Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Re 1: Set the form's Popup and Modal properties to No.
If you open the form in code or macro, don't use dialog mode.

Re 2: The command button can open the report.
If you have A2007, the OpenReport action lets you specify Report view or
Layout view rather than Print Preview or Normal.
 
Back
Top