• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Access Report parameter prompted 3 times?

I put together a report in access, the top is the data detail, and the bottom is a chart. The report uses a different query for the data and chart. I named the parameter the same in both queries but when I run the report it prompts me once for the data, which is expected, but then 2 more times for the chart? Is there a way to make all prompts use the same paramter entry without using code, as I dont resort to access very often and thought it would be a quick fix?
0
selectallfrompeoplewhereclueisnotnull
Asked:
selectallfrompeoplewhereclueisnotnull
  • 4
  • 3
1 Solution
 
peter57rCommented:
If the queries are independent then the parameters are independent too.
You can use a form to launch your report and add the parameter as a textbox on the form.  Then both queries can refer to the same textbox instead of your current parameter field.

use your version of ..
Forms!formname!textboxname
in both queries.

If you are getting prompted twice for what you believe to be the same parameter it implies you have used different spellings in the two instances.
0
 
selectallfrompeoplewhereclueisnotnullAuthor Commented:
I've added a form named ReportCode and textbox named RptCode, I've put [Forms]![ReportCode]![RptCode] in the query column, now it prompts me 3 times with Forms!ReportCode!RptCode in the Enter Paramater Value prompt?
0
 
peter57rCommented:
Is the form open?

Please post the sql view of each of your queries.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
selectallfrompeoplewhereclueisnotnullAuthor Commented:
PARAMETERS [Forms]![ReportCode]![RptCode] Text ( 255 );
SELECT [R12 Monthly Unique].Description, [R12 Monthly Unique].Rpt_Code, [R12 Monthly Unique].Frequency, [R12 Monthly Unique].U1, [R12 Monthly Unique].U2, [R12 Monthly Unique].U3, [R12 Monthly Unique].U4, [R12 Monthly Unique].U5, [R12 Monthly Unique].U6, [R12 Monthly Unique].U7, [R12 Monthly Unique].U8, [R12 Monthly Unique].U9, [R12 Monthly Unique].U10, [R12 Monthly Unique].U11, [R12 Monthly Unique].U12, [R12 Traffic].R1, [R12 Traffic].R2, [R12 Traffic].R3, [R12 Traffic].R4, [R12 Traffic].R5, [R12 Traffic].R6, [R12 Traffic].R7, [R12 Traffic].R8, [R12 Traffic].R9, [R12 Traffic].R10, [R12 Traffic].R11, [R12 Traffic].R12, [R12 Traffic].D1, [R12 Traffic].D2, [R12 Traffic].D3, [R12 Traffic].D4, [R12 Traffic].D5, [R12 Traffic].D6, [R12 Traffic].D7, [R12 Traffic].D8, [R12 Traffic].D9, [R12 Traffic].D10, [R12 Traffic].D11, [R12 Traffic].D12, Recipients.CountOfRpt_Code AS M, IIf([Frequency]<>"Monthly","*",IIf([U1]<1,"*",IIf([M]<1,"*",Round(([U1]/[M]),2)*100 & "%"))) AS Z1, IIf([Frequency]<>"Monthly","*",IIf([U2]<1,"*",IIf([M]<1,"*",Round(([U2]/[M]),2)*100 & "%"))) AS Z2, IIf([Frequency]<>"Monthly","*",IIf([U3]<1,"*",IIf([M]<1,"*",Round(([U3]/[M]),2)*100 & "%"))) AS Z3, IIf([Frequency]<>"Monthly","*",IIf([U4]<1,"*",IIf([M]<1,"*",Round(([U4]/[M]),2)*100 & "%"))) AS Z4, IIf([Frequency]<>"Monthly","*",IIf([U5]<1,"*",IIf([M]<1,"*",Round(([U5]/[M]),2)*100 & "%"))) AS Z5, IIf([Frequency]<>"Monthly","*",IIf([U6]<1,"*",IIf([M]<1,"*",Round(([U6]/[M]),2)*100 & "%"))) AS Z6, IIf([Frequency]<>"Monthly","*",IIf([U7]<1,"*",IIf([M]<1,"*",Round(([U7]/[M]),2)*100 & "%"))) AS Z7, IIf([Frequency]<>"Monthly","*",IIf([U8]<1,"*",IIf([M]<1,"*",Round(([U8]/[M]),2)*100 & "%"))) AS Z8, IIf([Frequency]<>"Monthly","*",IIf([U9]<1,"*",IIf([M]<1,"*",Round(([U9]/[M]),2)*100 & "%"))) AS Z9, IIf([Frequency]<>"Monthly","*",IIf([U10]<1,"*",IIf([M]<1,"*",Round(([U10]/[M]),2)*100 & "%"))) AS Z10, IIf([Frequency]<>"Monthly","*",IIf([U11]<1,"*",IIf([M]<1,"*",Round(([U11]/[M]),2)*100 & "%"))) AS Z11, IIf([Frequency]<>"Monthly","*",IIf([U12]<1,"*",IIf([M]<1,"*",Round(([U12]/[M]),2)*100 & "%"))) AS Z12
FROM ([R12 Traffic] LEFT JOIN [R12 Monthly Unique] ON [R12 Traffic].Rpt_Code = [R12 Monthly Unique].Rpt_Code) LEFT JOIN Recipients ON [R12 Traffic].Rpt_Code = Recipients.Rpt_Code
GROUP BY [R12 Monthly Unique].Description, [R12 Monthly Unique].Rpt_Code, [R12 Monthly Unique].Frequency, [R12 Monthly Unique].U1, [R12 Monthly Unique].U2, [R12 Monthly Unique].U3, [R12 Monthly Unique].U4, [R12 Monthly Unique].U5, [R12 Monthly Unique].U6, [R12 Monthly Unique].U7, [R12 Monthly Unique].U8, [R12 Monthly Unique].U9, [R12 Monthly Unique].U10, [R12 Monthly Unique].U11, [R12 Monthly Unique].U12, [R12 Traffic].R1, [R12 Traffic].R2, [R12 Traffic].R3, [R12 Traffic].R4, [R12 Traffic].R5, [R12 Traffic].R6, [R12 Traffic].R7, [R12 Traffic].R8, [R12 Traffic].R9, [R12 Traffic].R10, [R12 Traffic].R11, [R12 Traffic].R12, [R12 Traffic].D1, [R12 Traffic].D2, [R12 Traffic].D3, [R12 Traffic].D4, [R12 Traffic].D5, [R12 Traffic].D6, [R12 Traffic].D7, [R12 Traffic].D8, [R12 Traffic].D9, [R12 Traffic].D10, [R12 Traffic].D11, [R12 Traffic].D12, Recipients.CountOfRpt_Code
HAVING ((([R12 Monthly Unique].Rpt_Code)=[Forms]![ReportCode]![RptCode]));

PARAMETERS [Forms]![ReportCode]![RptCode] Text ( 255 );
SELECT Chart.Rpt_Code, Chart.Usage
FROM Chart
WHERE (((Chart.Rpt_Code)=[Forms]![ReportCode]![RptCode]));
0
 
peter57rCommented:
If the form is open, and the form name and textbox name are both correct,  I can't see any reason why you should be prompted for parameters.

I suggest you open the form, enter a value in rptcode and then do this...
Go to the vba code window (do Crl+G to get there in one step).

Then in the Immediate window type..

?Forms!ReportCode!RptCode
and see if you get the correct value shown.
0
 
selectallfrompeoplewhereclueisnotnullAuthor Commented:
The form wasn't open, I was hoping the report would open the form as a prompt, sounds like I need to open the form first, create a button that will open the report instead.
0
 
peter57rCommented:
You have to open the form first.  It won't happen automatically.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now