Access Report / Sub report with parameters

[Scott Gorman]

I have a report with a sub report for an income / expense report for
church. My expense report is the sub report.
My question is, when I run the report, it prompts me 2-3 times for the
parameters of the expense report. Is there a way around this annoyance?
Thank you for your help.

Scott

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters

How are you defining these parameters?

"Scott Gorman" <sgor1@comcast. net> wrote in message
news:41fc4459$1 _2@127.0.0.1...[color=blue]
>
>
> I have a report with a sub report for an income / expense report for
> church. My expense report is the sub report.
> My question is, when I run the report, it prompts me 2-3 times for the
> parameters of the expense report. Is there a way around this annoyance?
> Thank you for your help.
>
> Scott
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Scott Gorman]

Re: Access Report / Sub report with parameters

In the query, I have a column with the following entries.

Field- Month([Date])
Sort- Expression
Criteria- [Expense Report for month: 1-12: ]

When I run this query on its own, it works fine. The problem seems to be
when I have this query in my report as a sub report. It currently is
prompting me 2 times for this data. Not sure how I changed it, but it
was prompting me 3 times before.
Thanks,
Scott

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters

I'd suggest you take the criteria line out of the query.
Instead, put a textbox on your main form to enter the month you want.
Then use the LinkMasterField s and LinkChildFields properties of your subform
control to take care of your filtering.


"Scott Gorman" <sgor1@comcast. net> wrote in message
news:41fcf7a9$1 _1@127.0.0.1...[color=blue]
> In the query, I have a column with the following entries.
>
> Field- Month([Date])
> Sort- Expression
> Criteria- [Expense Report for month: 1-12: ]
>
> When I run this query on its own, it works fine. The problem seems to be
> when I have this query in my report as a sub report. It currently is
> prompting me 2 times for this data. Not sure how I changed it, but it
> was prompting me 3 times before.
> Thanks,
> Scott
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Scott Gorman]

Re: Access Report / Sub report with parameters



I am not sure how to do that. Could you possibly help me to get that
started. I sure appreciate your help

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters


"Scott Gorman" <sgor1@comcast. net> wrote in message
news:420428a8$1 _1@127.0.0.1...[color=blue]
>
>
> I am not sure how to do that. Could you possibly help me to get that
> started. I sure appreciate your help
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[MacDermott]

Re: Access Report / Sub report with parameters

1. I'd suggest you take the criteria line out of the query.
2. Instead, put a textbox on your main form to enter the month you want.
3. Then use the LinkMasterField s and LinkChildFields properties of your
subform
control to take care of your filtering

Sorry, I'm not sure where I need to be clearer.
Which line of my instructions is unclear?
Could you be more specific in the help you'd like?


"Scott Gorman" <sgor1@comcast. net> wrote in message
news:420428a8$1 _1@127.0.0.1...[color=blue]
>
>
> I am not sure how to do that. Could you possibly help me to get that
> started. I sure appreciate your help
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Scott Gorman]

Re: Access Report / Sub report with parameters


Sorry for being so unclear.
OK. I have taken the parameter out of the query. Now, when I open the
report, I get all the dates, and do not get the parameters I want.
I am trying to create a form that will open when the report is opened
that I can put the date range I want to see.
I can not figure out how to link the "Date" form to the query and then
to the report to tie it all together.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters

OK, that's quite different from the report/subreport setup you first
described.

Do you have parameters other than the date?
Do you have a form, perhaps with a button, from which you open this report?
Or are you opening it directly from the database window?

"Scott Gorman" <sgor1@comcast. net> wrote in message
news:420452d7$1 _2@127.0.0.1...[color=blue]
>
> Sorry for being so unclear.
> OK. I have taken the parameter out of the query. Now, when I open the
> report, I get all the dates, and do not get the parameters I want.
> I am trying to create a form that will open when the report is opened
> that I can put the date range I want to see.
> I can not figure out how to link the "Date" form to the query and then
> to the report to tie it all together.
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Scott Gorman]

Re: Access Report / Sub report with parameters

The only criteria I am pulling out is the date range
I have a switchboard from which to open the report from.
I was able to create a form that I am able to have open when the report
opens to input the parameters in now. Seems to be working ok.
The last thing I would like to be able to do is....
I have a text box in the report that prompts to "input last months
balance". This prompt comes up after my form to input the date range. If
I add this to my form, how do I link it to fill in on the report?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters

Here's one way:
Put a label on the report.
In the report_open event, set the label's caption to the value of the
textbox:
MyLabel.Caption = Forms!InputForm !txtBalance

HTH

"Scott Gorman" <sgor1@comcast. net> wrote in message
news:4204e0ff$1 _2@127.0.0.1...[color=blue]
> The only criteria I am pulling out is the date range
> I have a switchboard from which to open the report from.
> I was able to create a form that I am able to have open when the report
> opens to input the parameters in now. Seems to be working ok.
> The last thing I would like to be able to do is....
> I have a text box in the report that prompts to "input last months
> balance". This prompt comes up after my form to input the date range. If
> I add this to my form, how do I link it to fill in on the report?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Scott Gorman]

Re: Access Report / Sub report with parameters



MyLabel.Prevbal ance = Forms!Date!bala nce

This is the code I placed in the On open event procedure.
Prevbalance is the name of the text box, and balance is the name of the
box on the form where you input the previous balance.
When it runs, it errors out and says - run time error '424' Object
required.
Can you tell me where I went wrong.
Thanks for you patience with me...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters

I think you've got the syntax on the right side of the equals sign right,
except for my advice in # 4 below. But there are several problems on the
left side.

1. My instructions were to place a label on the report.
It looks as if you used a textbox.
Use a label.

2. When giving instructions such as "Create a Label. Set its properties
thus:", it's common to refer to the label as MyLabel.
The expectation is that the user will substitute the name of his/her
own label.

3. I instructed you to use the Caption property of the label.
If you have a label named Prevbalance, the syntax would be
Prevbalance.Cap tion
(If Prevbalance is a textbox, this will cause an error, because a
textbox doesn't have a Caption property.)

Since you have nothing on your report named MyLabel, and have an
instruction to Access to do something to MyLabel, it's understandable that
Access says "Object required", i.e. "I/you need an object named MyLabel."

4. This is not something I'd told you about before, but it looks as if your
form is named Date. Date is a reserved word in Access, which means Access
may have its own opinion as to the meaning of that term, which may well not
co-incide with yours. Personally, I avoid this problem by always starting
my form names with frm, so I would have called this form frmDate. You could
just as well name it DateForm, or DateRange. But naming it (or anything
else) just Date is asking for trouble you don't need.


"Scott Gorman" <sgor1@comcast. net> wrote in message
news:42068fe9$1 _1@127.0.0.1...[color=blue]
>
>
> MyLabel.Prevbal ance = Forms!Date!bala nce
>
> This is the code I placed in the On open event procedure.
> Prevbalance is the name of the text box, and balance is the name of the
> box on the form where you input the previous balance.
> When it runs, it errors out and says - run time error '424' Object
> required.
> Can you tell me where I went wrong.
> Thanks for you patience with me...
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Scott Gorman]

Re: Access Report / Sub report with parameters



Thank you. I am beginning to get it through my thick head.

I was able to get the label to work, and I have renamed my form to
dateform.
What would the syntax be to set the format so it would look like the
following. $1,111.11 ?
Also, I had a formula set in another text box to take the previous
balance + income - expenses to come up with the new balance forward.
Seems as though I am getting an error now that I replaced my old text
box with the new label.
Should I create this formula in syntax code as well?

Thank you again,

Scott

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[MacDermott]

Re: Access Report / Sub report with parameters

The Access Help file has a pretty good description of the Format() function.
I suggest you look there to learn its syntax.

Now that you have a label instead of a textbox, you'll need to use its
..Caption property whenever you want to reference the text it displays. This
may be what's causing the error in your calculations.


"Scott Gorman" <sgor1@comcast. net> wrote in message
news:4206f12d$1 _1@127.0.0.1...[color=blue]
>
>
> Thank you. I am beginning to get it through my thick head.
>
> I was able to get the label to work, and I have renamed my form to
> dateform.
> What would the syntax be to set the format so it would look like the
> following. $1,111.11 ?
> Also, I had a formula set in another text box to take the previous
> balance + income - expenses to come up with the new balance forward.
> Seems as though I am getting an error now that I replaced my old text
> box with the new label.
> Should I create this formula in syntax code as well?
>
> Thank you again,
>
> Scott
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]