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
|
|||
|
|||
Report parameters
I am tentatively dipping my toe in the water. I would like to add parameters
to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#2
|
|||
|
|||
Report parameters
Saylindara,
Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#3
|
|||
|
|||
Report parameters
I've been having trouble posting a reply and getting 'Service temporarily
unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report’s OpenEvent: DoCmd.OpenForm “ParamForm”,,,,,acDialog and the CloseEvent: DoCmd.Close acForm, “ParamForm”. I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#4
|
|||
|
|||
Report parameters
Saylindara,
You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#5
|
|||
|
|||
Report parameters
I'm having a lot of trouble posting replies. I don't know if there is
something wrong with my PC or it's a more general problem. I'm sorry to be so dense but I'm not sure where I put Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub or what I have to delete. Here's hoping this post goes through. To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP "Al Campagna" wrote: Saylindara, You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#6
|
|||
|
|||
Report parameters
Saylindara,
I'm using my own object names... please use your own real names in any code you use. In form design mode, select your RunReportButton's OnClick property. Put your cursor in the text field next to the OnClick event. Usng the arrow on the right, select [Event Procedure] Click on the 3 dots to the right (...) You'll now be in the form's module where you'll see... Private Sub RunReport_OnClick() End Sub Put the code between the lines... Private Sub RunReport_OnClick() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub Test by setting a cboEmpID value, or deleting any cboEmpID value... -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I'm having a lot of trouble posting replies. I don't know if there is something wrong with my PC or it's a more general problem. I'm sorry to be so dense but I'm not sure where I put Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub or what I have to delete. Here's hoping this post goes through. To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP "Al Campagna" wrote: Saylindara, You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#7
|
|||
|
|||
Report parameters
I added this to the command button OnClick event:
Private Sub Command2_Click() If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.EmployeeID) End If End Sub This opened the form when the combobox was blank but when I tried it with employee names the form opens but is blank. I had already checked as you recommended that I had the correct bound column (such a good idea as it showed I hadn't and I was able to correct it). I then thought I had a brainwave (I should have known better) that it was because I hadn't got employeeID on the form, so I added that in the details section but it didn't make any difference. Should I be putting something in the query? I tried what I had originally 'forms!PramForm!cboFindName' but a form popped up asking me for parameters. I know you must be getting fed up with me now but any further help would be greatly appreciated. "Al Campagna" wrote: Saylindara, I'm using my own object names... please use your own real names in any code you use. In form design mode, select your RunReportButton's OnClick property. Put your cursor in the text field next to the OnClick event. Usng the arrow on the right, select [Event Procedure] Click on the 3 dots to the right (...) You'll now be in the form's module where you'll see... Private Sub RunReport_OnClick() End Sub Put the code between the lines... Private Sub RunReport_OnClick() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub Test by setting a cboEmpID value, or deleting any cboEmpID value... -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I'm having a lot of trouble posting replies. I don't know if there is something wrong with my PC or it's a more general problem. I'm sorry to be so dense but I'm not sure where I put Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub or what I have to delete. Here's hoping this post goes through. To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP "Al Campagna" wrote: Saylindara, You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#8
|
|||
|
|||
Report parameters
Saylindara,
I may have confused you with my previous code... As we discussed, you shouldn't be filtering the report by EmployeeName. We should use the unique EmpID value. Your combo (cboEmpID) on the ParamForm should return a legitimate EmpID, and make sure you use cboEmpID value in your Open Report Where argument. Notice in this code that the EmployeeID field of your query is filtered by the cboEmpID on form ParamForm. The form must be open when the report is run. Remove any criteria you have hard wired in the query itself. Notice the Refresh I've added. Each DoCmd should all be on one line. Private Sub Command2_Click() Refresh If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.cboEmpID) End If End Sub Doing it this way, we don't hard-wire the query to any specific filter. Another button on your form could filter the same query and report by some City value, or some State value, or whatever we want. Have patience... we will get this resolved -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I added this to the command button OnClick event: Private Sub Command2_Click() If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.EmployeeID) End If End Sub This opened the form when the combobox was blank but when I tried it with employee names the form opens but is blank. I had already checked as you recommended that I had the correct bound column (such a good idea as it showed I hadn't and I was able to correct it). I then thought I had a brainwave (I should have known better) that it was because I hadn't got employeeID on the form, so I added that in the details section but it didn't make any difference. Should I be putting something in the query? I tried what I had originally 'forms!PramForm!cboFindName' but a form popped up asking me for parameters. I know you must be getting fed up with me now but any further help would be greatly appreciated. "Al Campagna" wrote: Saylindara, I'm using my own object names... please use your own real names in any code you use. In form design mode, select your RunReportButton's OnClick property. Put your cursor in the text field next to the OnClick event. Usng the arrow on the right, select [Event Procedure] Click on the 3 dots to the right (...) You'll now be in the form's module where you'll see... Private Sub RunReport_OnClick() End Sub Put the code between the lines... Private Sub RunReport_OnClick() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub Test by setting a cboEmpID value, or deleting any cboEmpID value... -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I'm having a lot of trouble posting replies. I don't know if there is something wrong with my PC or it's a more general problem. I'm sorry to be so dense but I'm not sure where I put Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub or what I have to delete. Here's hoping this post goes through. To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP "Al Campagna" wrote: Saylindara, You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#9
|
|||
|
|||
Report parameters
For some reason my posts have not been accepted for several days - I don't
know if this one will be accepted either but I'm giving it another try. Because of this I had to go to another forum to fine tune what you started. I just wanted you to know you really helped me with my problem and to thank you for taking the trouble. "Al Campagna" wrote: Saylindara, I may have confused you with my previous code... As we discussed, you shouldn't be filtering the report by EmployeeName. We should use the unique EmpID value. Your combo (cboEmpID) on the ParamForm should return a legitimate EmpID, and make sure you use cboEmpID value in your Open Report Where argument. Notice in this code that the EmployeeID field of your query is filtered by the cboEmpID on form ParamForm. The form must be open when the report is run. Remove any criteria you have hard wired in the query itself. Notice the Refresh I've added. Each DoCmd should all be on one line. Private Sub Command2_Click() Refresh If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.cboEmpID) End If End Sub Doing it this way, we don't hard-wire the query to any specific filter. Another button on your form could filter the same query and report by some City value, or some State value, or whatever we want. Have patience... we will get this resolved -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I added this to the command button OnClick event: Private Sub Command2_Click() If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.EmployeeID) End If End Sub This opened the form when the combobox was blank but when I tried it with employee names the form opens but is blank. I had already checked as you recommended that I had the correct bound column (such a good idea as it showed I hadn't and I was able to correct it). I then thought I had a brainwave (I should have known better) that it was because I hadn't got employeeID on the form, so I added that in the details section but it didn't make any difference. Should I be putting something in the query? I tried what I had originally 'forms!PramForm!cboFindName' but a form popped up asking me for parameters. I know you must be getting fed up with me now but any further help would be greatly appreciated. "Al Campagna" wrote: Saylindara, I'm using my own object names... please use your own real names in any code you use. In form design mode, select your RunReportButton's OnClick property. Put your cursor in the text field next to the OnClick event. Usng the arrow on the right, select [Event Procedure] Click on the 3 dots to the right (...) You'll now be in the form's module where you'll see... Private Sub RunReport_OnClick() End Sub Put the code between the lines... Private Sub RunReport_OnClick() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub Test by setting a cboEmpID value, or deleting any cboEmpID value... -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I'm having a lot of trouble posting replies. I don't know if there is something wrong with my PC or it's a more general problem. I'm sorry to be so dense but I'm not sure where I put Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub or what I have to delete. Here's hoping this post goes through. To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP "Al Campagna" wrote: Saylindara, You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
#10
|
|||
|
|||
Report parameters
OK Saylindara,
Thanks for letting me know that. -- Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... For some reason my posts have not been accepted for several days - I don't know if this one will be accepted either but I'm giving it another try. Because of this I had to go to another forum to fine tune what you started. I just wanted you to know you really helped me with my problem and to thank you for taking the trouble. "Al Campagna" wrote: Saylindara, I may have confused you with my previous code... As we discussed, you shouldn't be filtering the report by EmployeeName. We should use the unique EmpID value. Your combo (cboEmpID) on the ParamForm should return a legitimate EmpID, and make sure you use cboEmpID value in your Open Report Where argument. Notice in this code that the EmployeeID field of your query is filtered by the cboEmpID on form ParamForm. The form must be open when the report is run. Remove any criteria you have hard wired in the query itself. Notice the Refresh I've added. Each DoCmd should all be on one line. Private Sub Command2_Click() Refresh If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.cboEmpID) End If End Sub Doing it this way, we don't hard-wire the query to any specific filter. Another button on your form could filter the same query and report by some City value, or some State value, or whatever we want. Have patience... we will get this resolved -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I added this to the command button OnClick event: Private Sub Command2_Click() If IsNull(cboFindName) Then DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview Else DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "& Me.EmployeeID) End If End Sub This opened the form when the combobox was blank but when I tried it with employee names the form opens but is blank. I had already checked as you recommended that I had the correct bound column (such a good idea as it showed I hadn't and I was able to correct it). I then thought I had a brainwave (I should have known better) that it was because I hadn't got employeeID on the form, so I added that in the details section but it didn't make any difference. Should I be putting something in the query? I tried what I had originally 'forms!PramForm!cboFindName' but a form popped up asking me for parameters. I know you must be getting fed up with me now but any further help would be greatly appreciated. "Al Campagna" wrote: Saylindara, I'm using my own object names... please use your own real names in any code you use. In form design mode, select your RunReportButton's OnClick property. Put your cursor in the text field next to the OnClick event. Usng the arrow on the right, select [Event Procedure] Click on the 3 dots to the right (...) You'll now be in the form's module where you'll see... Private Sub RunReport_OnClick() End Sub Put the code between the lines... Private Sub RunReport_OnClick() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub Test by setting a cboEmpID value, or deleting any cboEmpID value... -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I'm having a lot of trouble posting replies. I don't know if there is something wrong with my PC or it's a more general problem. I'm sorry to be so dense but I'm not sure where I put Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub or what I have to delete. Here's hoping this post goes through. To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP "Al Campagna" wrote: Saylindara, You should have the ParamForm open, and an EmpID (or none) selected BEFORE you run the report. The report should be run from a ParamForm button, and the form should remain open while the report is run. Instead of using a query parameter, use the Where condition of the OpenReport method to report on just 1 Employee or All... depending on the value in cboEmpID. Private Sub RunReportButton_Click() If IsNull(cboEmpID) Then DoCmd.OpenReport "rptReport", acViewPreview Else DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID) End If End Sub To be sure that cboEmpID actually contains the correct "bound" value, add a text control to the form with = cboEmpID calculation in it's ControlSource. That will show you what the real vs displayed cboEmpID value is. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I've been having trouble posting a reply and getting 'Service temporarily unavailable' messages. I don't know if anyone else has had the same problem. I should have explained the problem better. I created an unbound form: Paramform. Then I added a combobox: cboFindName. The combobox showed a list of employee names with the bound field EmployeeID. Then I added a command button and coded the ClickEvent: Me.Visible=False. In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName. I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog and the CloseEvent: DoCmd.Close acForm, "ParamForm". I would also like to have an option on the unbound form to print all the records. "Al Campagna" wrote: Saylindara, Yes, it is possible. But, there are many ways to handle parameters. I'm not sure what you've done with Fred in the past, but... If you have a specific question about a specific parameter, that would be best to post. But here's a sample... Say you had a parameter, in your query, that asked for a Company Name. And you had the following Companies in the table. Smith Smith & Sons Smithers Products In the CompanyName column you had the following criteria... = Like [Enter Company Name] if you enter "Smith" at the parameter prompt... only Smith will be returned (Exact match) In the CompanyName column you had the following criteria... (using a global * variable) = Like [Enter Company Name] & "*" if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would be will be returned. (begins with... and after that I don't care) In the CompanyName column you had the following criteria... = Like "*" & [Enter Company Name] & "*" if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and Smithers Products will be returned. (Has this string anywhere within the Company Name) ***But*** if prompt is null (left blank), will return all Company Names. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "Saylindara" wrote in message ... I am tentatively dipping my toe in the water. I would like to add parameters to several of my reports and have successfully followed the directions posted by fredg on 1/8/2009 on a simple test report using one parameter. (Ultimately my reports will have 2 or more parameters, when I daresay I will get my knickers in a knot.) I would also like users to have the option to view the whole report (i.e. no parameters) as well if they want to. Is that possible? |
Thread Tools | |
Display Modes | |
|
|