Prompting for a Parameter in an Access 2007 Report

[Mark Schaffel]

I have created a query in a database which will be used for
invoicing. I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company]. For the company prompt, I'd like the
pop-up to have a combo box with only the companies in another table so
they don't have to remember the spelling/sytax of each company. Can
anyone help me do this?

Mark in Chicago

 

[fredg]

I have created a query in a database which will be used for
invoicing. I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company]. For the company prompt, I'd like the
pop-up to have a combo box with only the companies in another table so
they don't have to remember the spelling/sytax of each company. Can
anyone help me do this?

Mark in Chicago

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a Customer you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

 

[abortow]

I have created a query in a database which will be used for
invoicing.  I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company].  For the company prompt, I'd like the
pop-up to have a combo box with only the companies in another table so
they don't have to remember the spelling/sytax of each company.  Can
anyone help me do this?

Mark in Chicago

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a Customer you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

I am stuck. Everything works except the command button doesn't
recognize the value in the field to put in the query. I do not have a
date range, just one field. What am I missing? I am new to this, so
I would appreciate any help you can give. Thanks.

Anne

 

[fredg]

I have created a query in a database which will be used for
invoicing.  I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company].  For the company prompt, I'd like the
pop-up to have a combo box with only the companies in another table so
they don't have to remember the spelling/sytax of each company.  Can
anyone help me do this?

Mark in Chicago

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.

Let's assume it is a Customer you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.

I am stuck. Everything works except the command button doesn't
recognize the value in the field to put in the query. I do not have a
date range, just one field. What am I missing? I am new to this, so
I would appreciate any help you can give. Thanks.

Anne

I have no idea what you mean by "the command button doesn't
recognize the value in the field to put in the query".
All the command button does is after you select the Customer from the
combo box and enter the Start and End dates it makes the form not
visible and allows the Report to resume processing. You did place code
in the Report's Open event to open the form, didn't you?

I also have no idea what you mean by "I do not have a
date range, just one field." Your original question said:
"I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company]" which is what I gave you in my reply.

If you did exactly what I previously wrote, the query will read the
combo box value from the form and the report data will be filtered by
Customer and Date.

 

[abortow]

On Sun, 26 Jul 2009 16:48:20 -0700 (PDT), Mark Schaffel wrote:
I have created a query in a database which will be used for
invoicing.  I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company].  For the company prompt, I'd like the
pop-up to have a combo box with only the companies in another table so
they don't have to remember the spelling/sytax of each company.  Can
anyone help me do this?
Mark in Chicago
You'll need to use a form to do this.
First, create a query that will display the fields you wish to show in
the report.
Second, create a report, using the query as it's record source, that
shows the data you wish to display for ALL records.
Let's assume it is a Customer you need as criteria, as well
as a starting and ending date range.
Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".
Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".
Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"
Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName
As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate
Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog
Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"
Run the Report.
The report will open the form.
Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.
The Report will display just those records selected.
When the Report closes it will close the form.

I am stuck.  Everything works except the command button doesn't
recognize the value in the field to put in the query.  I do not have a
date range, just one field.  What am I missing?  I am new to this, so
I would appreciate any help you can give.  Thanks.

Anne

I have no idea what you mean by "the command button doesn't
recognize the value in the field to put in the query".
All the command button does is after you select the Customer from the
combo box and enter the Start and End dates it makes the form not
visible and allows the Report to resume processing. You did place code
in the Report's Open event to open the form, didn't you?

I also have no idea what you mean by "I do not have a
date range, just one field." Your original question said:
"I want to have the user answer 3 prompts: [Start Date],
 [End Date] and [Which Company]" which is what I gave you in my reply.

If you did exactly what I previously wrote, the query will read the
combo box value from the form and the report data will be filtered by
Customer and Date.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

Sorry to confuse you, but I did not ask the original question. I
happened upon this post in my search for help. I am only using one
parameter, so that I can generate a report by project name. The query
is not recognizing what is in the combo box. I followed all your
instructions to a "t" and applied it my situation with no luck. So I
am wondering if I missed something. Thanks.

Anne

 

[Sheryl]

Hi and thank you for the help.

I too am trying to use your directions, but for only a single prompt.

I have the query, report and form set up. The query and report work fine. I have an combo box for the customer number, named as you said. I have the button named as you said, with the event procedure.

I have the event procedures set up on the open and close of the report. The issue, I believe is that the event procedure on open is not being read, or it is going to the query first. When I try to open the report, I'm receiving the prompt "forms!ParamForm!cboFindName" which was automatically put in brackets on my query criteria section.

Can you help me? What am I missing?

Thank you!

 

[Assyura Muharam]

Hai,

sorry to interupt you, I have read your tutorial how to prompting for a paramater, but I have different value to prompt and can't used what you have told - maybe I missing something.

Here is the detail :
I want the user have to answer this 3 :
1. Date or Invoice ID - using combo box
2. Company Name (1st) - using combo box
3. Company Name (2nd) - using combo box

or in word I want to show an Invoice between 2 Company ID (If there is 4 company between those 2 id, then all 4 will open) on the date user fill (ex: 21/08/10), so all the invoice from those 4 company on that day will open.

Please help me if you can, and thank you very much for the time to answer

You'll need to use a form to do this.

First, create a query that will display the fields you wish to show in
the report.

Second, create a report, using the query as it is record source, that
shows the data you wish to display for ALL records.

Let's assume it is a Customer you need as criteria, as well
as a starting and ending date range.

Next, make a new unbound form.
Add a combo box that will show the CustomerID field as well as the
Customer Name field (you can use the Combo Box wizard to do so).
Set the Combo box's Column Count property to 2.
Hide the CustomerID field by setting the Combo box's ColumnWidth
property to 0";1"
Make sure the Combo Box Bound Column is the
CustomerID field.
Name this Combo Box "cboFindName".

Add 2 unbound text controls to the form.
Set their Format property to any valid date format.
Name one "StartDate".
Name the other "EndDate".

Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Go back to the query. As criteria, on the Query's CustomerID field
criteria line write:
forms!ParamForm!cboFindName

As Criteria on the DateField, write:
Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerName in the combo box.
Enter the starting and ending dates.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Sunday, July 26, 2009 11:21 PM Mark Schaffel wrote:
I have created a query in a database which will be used for
invoicing. I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company]. For the company prompt, I'd like the
pop-up to have a combo box with only the companies in another table so
they do not have to remember the spelling/sytax of each company. Can
anyone help me do this?

Mark in Chicago

On Thursday, July 30, 2009 9:37 PM fredg wrote:
I have no idea what you mean by "the command button does not
recognize the value in the field to put in the query".
All the command button does is after you select the Customer from the
combo box and enter the Start and End dates it makes the form not
visible and allows the Report to resume processing. You did place code
in the Report's Open event to open the form, did not you?

I also have no idea what you mean by "I do not have a
date range, just one field." Your original question said:
"I want to have the user answer 3 prompts: [Start Date],
[End Date] and [Which Company]" which is what I gave you in my reply.

If you did exactly what I previously wrote, the query will read the
combo box value from the form and the report data will be filtered by
Customer and Date.