Member Avatar for mundetas

I have a query which has a prompt when you insert the year a graph on a report displays.My problem is that when I enter the date the same prompt appears two times after I inserted the year. Also when I what to print the graph it also prompts me for a year two times on the same thing. can some please help. :(

  • 2 Contributors
  • 7 Replies
  • 96 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by timothybard

Dani AI

Generated

The behavior described by (single prompt when the report displays, but a second prompt when printing) usually means the same unresolved parameter is being evaluated more than once by different data sources: the report RecordSource and an embedded chart, subreport, or a hidden query/control. Access prompts each time it encounters an unresolved parameter expression, and printing can force a requery or separate evaluation of embedded objects.

Suggested diagnostic steps: open the report in Design view and inspect the Report RecordSource plus every control's ControlSource and RowSource (especially chart and subreport controls). Check group/sort settings and any event code (Format/Print/OnOpen/OnLoad) that might reassign or requery data. Use Object Dependencies or a staged disable (hide/remove controls temporarily) to isolate which object causes the second prompt. Also search saved queries for literal uses of the parameter token (for example [Year]) so no hidden query is still prompting.

Practical fixes that avoid multiple prompts: create a tiny modal parameter form and have all queries and controls reference Forms!frmParam!txtYear; use Access TempVars (Access 2007+) to store the year once and reference TempVars!MyYear everywhere; or open the report from code and set QueryDef parameter values (or set control RowSource) before the report runs so the parameter is resolved programmatically. Each approach ensures the value is available to every object and prevents printing from triggering a fresh prompt.

Final notes: keep the parameter name identical everywhere, consider declaring the parameter type in the saved SQL with a PARAMETERS clause, compact & repair if results are inconsistent, and remember 's alias approach is a useful first step—if that removed the view-time duplicates but printing still prompts, isolate the embedded object and apply one of the above fixes.

Recommended Answers

All 7 Replies

Member Avatar for timothybard

This sounds like something I have run into before.... I don't remember if this was the issue or not, but it is something you can try...

If you have more than one field that relies on the year prompt, then instead of using [year] in each of those fields, create a separate field with the name yearprompt and the value [year]. That way, the field will hold the value of the prompt but have an alias yearprompt. Then, for all the fields that rely on the prompt, use the field [yearprompt] instead of [year].

Again, I don't know if that will solve the problem or not; I just remember I had a similar issue before and I'm guessing that is the first thing I tried.

Member Avatar for mundetas

Hey there tim thanks for the post I hope you are online for a bit I am going to try what you post right now.thanks again

Member Avatar for mundetas

I creared another field in the query assign an alias and changed the prompt there it still has that prompt issue. when I want to make a print. it prompts once for the year. I view the report then when I click the print it prompts again please help:(

Member Avatar for timothybard

Just so I am clear...it sounds like we solved at least part of the issue. Before it prompted several times before the report would be displayed but now it only prompts once. The problem now is that it prompts again when you want to print. Is this correct?

Member Avatar for timothybard

I tried duplicating the issue, but I can't. I am able to create a report that prompts me for the year when I view it; however, when I go to print it, it doesn't prompt me a second time.

Can you provide me with additional information (is there any vba code in the report, are the any special controls, what is the sql statement you are using, etc) or, better yet, provide me with the relevant part of the database?

Member Avatar for mundetas

to summarize its a query with prompt within a query. Yes its true about the first been solved. is there vba code i can use to just print what it see?

Member Avatar for timothybard

Again, I don't know why you are prompted again when you try to print, because I don't run into that problem.

However, when you can do is us the On Open event to prompt the user for the year and then record the input into a table. Then change the query to join to that table instead of prompting the user. The result will be that the report prompts the user during the On Open event and the query does not prompt the user at all.

This is not an ideal solution, but it's the only thing I can think of without seeing the database.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.