Four subreports in main report each require the same prompt

[Jimcb8]

I created 4 queries to supply the data for the report.
Each query prompts for the same parameters MONTH and the YEAR desired, I did this so I could test each query individual.
They work fine.
Now the problem.
Each of the above queries is a subreport so the user can see all of the answers together.
Unfortunately, in order to run this report, the user must enter the same MONTH and YEAR four times.
Is there a way to have the user only enter the above parameters once?

I am a newbie to Access and would really appreciate the help.

Thanks,
Jim

 

[pbaldy]

Create a form for the user to enter the criteria in, and have all the queries get them from there.

 

[Jimcb8]

Let me explain my problem further.
I created a query that returns DISTINCT CLIENTID's for a given MONTH and YEAR.
The 4 queries use these CLIENTID's to do calculatons.
Since each of the four queries use this file in a join, Access generates the prompt for the MONTH and YEAR parameters. I'm unsure how to handle this situation to make it easier for the user.
Thank you so much for your patience.

 

[pbaldy]

Like I said, create form for the user to enter the month and year into. It's what most of us do anyway, as you have a great deal more control. Have the query point to the form:

Forms!FormName.TextboxName

instead of having

[Enter year]

and open the report from the form.

 

[Jimcb8]

Paul,
Thanks again for your help.
You always seem to bail me out.
I'm so grateful for your help. Being new to Access I can't tell you the stress I'm under.
I am writing ths app for the Calvary Episcopal Food Pantry which feeds over 300 people each Saturday. All of my time is donated.
Just wanted you to know that helping me, is going to also help a lot of pople.

 

[pbaldy]

Happy to help Jim!

 

[Jimcb8]

Paul,
Sorry to bother you again.

I created a form like you instructed.
Entered the Month and Year parameters
Created a button in the form to run open the report
The report ran great, the parameters where passed to the queries that are subreports to the main report.. The result were correct.
I'm a happy man.

Now I moved the parameter entry form, where you enter the Month, Year parameters, into a navigation tab for the user to select.
When I select that tab, the form appears for me to enter the parameter, just fine.
When I depress the button to open the Report, the queries in the subreports are not getting the parameters passed to them and the system is asking me to enter them. ie
Enter "Forms!FormName.textboxname". ?????

If I run the form not using the navigation tab, all is well.

It does not pass the parameters when it is called from a navigation tab. Strange. It seems that from a navigation tab the linkage to pass the data from the form to the report is different and they are not found???

Any ideas?? They would be most appreciated.

Thanks
Jim

 

[pbaldy]

It sounds like it's a subform now. If so, the syntax to refer to it is different:

http://www.mvps.org/access/forms/frm0031.htm

Or, while in the query in design view, you should be able to right-click in the criteria area and select Build. That will let you navigate to the form, and will build the proper syntax for you.

 

[Jimcb8]

Paul,
In the following situation which format do I use?
HomePage is the name of the Navagation form
The tab called MannaReport point a form called MannaReport which has the two control text boxes where I enter the Month and Year. FRMMonth and FRMYear are the text box control names. I also have created a button on MannaReport form called RUNMannaReport
Therefore
Form MannaReport has three items:
FRMMonth
FRMYear
RUNMannaReport button

I tried to reference, in the query that uses FRMMonth and FRMYear data as follows:
Forms!HomePage!MannaReport.Form!FRMMonth I just changd name to FRMYEAR for the Year control.

actual code

HAVING (((DatePart("m",[DateAttended]))=[Forms]![HomePage]![MannaReport]![frmmonth]) AND ((DatePart("yyyy",[DateAttended]))=[Forms]![HomePage]![MannaReport]![frmyear]));

I get this message ENTER PARAMER [Forms]![HomePage]![MannaReport]![frmmonth]

I tried removing the [] brackets and many many other variations for the last two days and I am stumped.

I tried to use the Bulid in the criteria section for the query also with no success. The build came up with a similiar sysntax as I have.

If I run MannaReport by itself:
Enter the Month and Year and depress the runMannareport button.
It work. The path in the query that works is Forms!MannaReport.frmmonth


If you have any thoughts to help me I would really appreciate them.
When I come down to Vegas with my wife someday I will have to buy you a big dinner.

Thanks,

Jim

 

[pbaldy]

Did you try this, which you mentioned but isn't in your actual code:

Forms!HomePage!MannaReport.Form!FRMMonth

Did you try the Build feature? Is MannaReport the name of the control on the HomePage form (the name of the subform and the name of the subform control can be different)? Can you post the db here?

Regarding Vegas, let me know. My wife and I would have to travel down there too.

 

[Jimcb8]

Attached DB
The query that uses to data in frmmonth and frmyear is Distinctfamiliesinmonth/year.
MannaReport is the form that the user enters enters the month and year in the control frmmonth and frmyear which is on MannaReport Form.


Hold the shift key down to open the db and bypass options
If you do not the database opens to HomePage the navigation form.
I tried your version and still no good.

I did try the build, but maybe I did not do it correctly.
Thanks,

Jim
My wife and I were married 7 years ago in Vegas and would like to visit again soon.
I live in Asheville NC now

 

[pbaldy]

Using Build results in:

[Forms]![HomePage]![NavigationSubform].[Form]![frmmonth]
[Forms]![HomePage]![NavigationSubform].[Form]![frmyear]

which appears to work fine.

I drove through Asheville once. My daughter is in the Air Force, and was moving from OK to NC, over by Raleigh. I helped her drive her car and stuff. As I recall, it was nice country on that side of the state. More mountainous, which I like.

If you were married by a short, round, black guy named George, it was my brother-in-law. He's done weddings in Vegas for a long time. Of course, there are probably a few hundred wedding chapels in Vegas.

 

[Jimcb8]

Paul,
You are the best. It did worked just fine. I am so appreciative I can't begin to tell you.
I really do wish we meet some day. I mean that sincerely. Either here in Asheville or Vegas. My offer still stands.
I wonder if there is a way to exchange emails so we can keep in touch. I tried to send you mine but it was blocked.
How did you learn so much about Acesss, you are a pro.

Thanks so much again.


Jim

 

[pbaldy]

Happy to help Jim. I'll send my email via PM; not sure why you would have been blocked.

 

[pbaldy]

You either have private messaging turned off or haven't been a member long enough. My email is

Pbaldy
Gmail
Com

 

[Jimcb8]

Paul,
Thanks again. You are a very special person to be so helpful to a perfect stranger.
My best wishes to you and your family.
Email
jimcb8
aol
com