If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Prompting for a Parameter in an Access 2007 Report
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 |
#2
|
|||
|
|||
Prompting for a Parameter in an Access 2007 Report
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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#3
|
|||
|
|||
Prompting for a Parameter in an Access 2007 Report
On Jul 26, 5:03*pm, fredg wrote:
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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail 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 |
#4
|
|||
|
|||
Prompting for a Parameter in an Access 2007 Report
On Thu, 30 Jul 2009 16:19:30 -0700 (PDT), abortow wrote:
On Jul 26, 5:03*pm, fredg wrote: 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail 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 |
#5
|
|||
|
|||
Prompting for a Parameter in an Access 2007 Report
On Jul 30, 6:37*pm, fredg wrote:
On Thu, 30 Jul 2009 16:19:30 -0700 (PDT), abortow wrote: On Jul 26, 5:03*pm, fredg wrote: 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. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail 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 |
Thread Tools | |
Display Modes | |
|
|