A bit lost here

[Bill Stanton]

A bit lost here......

I have a report and sub-report. The RecordSource
for the parent is a union select. The sub-report has
essentially the same RecordSource and I want to
inherit the filter expression from the parent, which
is set in the Open event of the parent. I.e., in the
Open event of the sub-report, I want to do something
like:
Me.Filter = Report!FundsRecentSunday.Filter
Me.FilterOn = True

Where "FundsRecentSunday" is the parent report name.

The sub-report uses conditional formatting to create
a SUM on an Amount field identical to the parent report,
only the sub-report makes a distinction as to the type
of the "Amount".

I have thus far been unable to get anything to display
in the sub-report unless I use a separate union query as
the RecordSource and hard-code a "Where expression"
to effect a filtering.

I have the idea that I'm ignorant to a more straight-forward
method of sharing the RecordSource between the parent
report and sub-report.

What say ye?

Thanks,
Bill

 

[Kelvin]

I'm lost to your question. Why are you using a sub-report if they both have
the same recordsource? If you are trying to perform different counts of the
same data you could have done this through the original recordsource. Can
you give a little more detail on what fields are on your report and what the
subform is for?

Kelvin

 

[Bill Stanton]

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects as I
attempt to explain.

The report fits on a single page.

1) The filter to be applied is the value of DOE, DateOfEntry.
2) The main portion of the report creates sums for each FundTitle
found in RecordSource and displays them in the page footer
section.
3) A total for all the Funds is displayed in the report footer.

As it is, or as I was attempting to fix, an additional summation is
performed on the same RecordSource in a sub-report according
to the "Type" of the Amount, i.e., currency versus check.

If I knew how to maintain separate accumulators as each record
is read during the process for the main/parent report, I could easily
format those accumulators into the report's footer and eliminate the
sub-report.

Hope this helps to give a meaningful picture.

Bill

 

[Kelvin]

Let me get this straight. You have a 1 page report. In the top portion of
this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report (in the
footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report. Create
a second query based on your union query. Group it by FundTitle and sum the
Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this report as
a subreport to your current report in the page footer section. Make sure it
is large enough to show the number of lines you might have. You do not need
to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report already so any
filter you apply to the record source for the main report will automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects as I
attempt to explain.

The report fits on a single page.

1) The filter to be applied is the value of DOE, DateOfEntry.
2) The main portion of the report creates sums for each FundTitle
found in RecordSource and displays them in the page footer
section.
3) A total for all the Funds is displayed in the report footer.

As it is, or as I was attempting to fix, an additional summation is
performed on the same RecordSource in a sub-report according
to the "Type" of the Amount, i.e., currency versus check.

If I knew how to maintain separate accumulators as each record
is read during the process for the main/parent report, I could easily
format those accumulators into the report's footer and eliminate the
sub-report.

Hope this helps to give a meaningful picture.

Bill

I'm lost to your question. Why are you using a sub-report if they both have
the same recordsource? If you are trying to perform different counts of the
same data you could have done this through the original recordsource. Can
you give a little more detail on what fields are on your report and what the
subform is for?

Kelvin

 

[Bill Stanton]

Not quite right:

(fund title footer [FundTitle] SUM([Amount]) )
Fund number one: $1000
Fund number two: $2000
Fund number three: $1500

(report footer "Total Funds" SUM([Amount]) )

Total Funds $4500

(sub-report, summation according to [Type] of [Amount]... what I need)

Total Funds by cash: $750
Total Funds by check: $3750

Let me get this straight. You have a 1 page report. In the top portion of
this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report (in the
footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report. Create
a second query based on your union query. Group it by FundTitle and sum the
Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this report as
a subreport to your current report in the page footer section. Make sure it
is large enough to show the number of lines you might have. You do not need
to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report already so any
filter you apply to the record source for the main report will automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects as I
attempt to explain.

The report fits on a single page.

1) The filter to be applied is the value of DOE, DateOfEntry.
2) The main portion of the report creates sums for each FundTitle
found in RecordSource and displays them in the page footer
section.
3) A total for all the Funds is displayed in the report footer.

As it is, or as I was attempting to fix, an additional summation is
performed on the same RecordSource in a sub-report according
to the "Type" of the Amount, i.e., currency versus check.

If I knew how to maintain separate accumulators as each record
is read during the process for the main/parent report, I could easily
format those accumulators into the report's footer and eliminate the
sub-report.

Hope this helps to give a meaningful picture.

Bill

I'm lost to your question. Why are you using a sub-report if they

both
have

the same recordsource? If you are trying to perform different counts

of
the

same data you could have done this through the original recordsource. Can
you give a little more detail on what fields are on your report and

what
the

subform is for?

Kelvin

A bit lost here......

I have a report and sub-report. The RecordSource
for the parent is a union select. The sub-report has
essentially the same RecordSource and I want to
inherit the filter expression from the parent, which
is set in the Open event of the parent. I.e., in the
Open event of the sub-report, I want to do something
like:
Me.Filter = Report!FundsRecentSunday.Filter
Me.FilterOn = True

Where "FundsRecentSunday" is the parent report name.

The sub-report uses conditional formatting to create
a SUM on an Amount field identical to the parent report,
only the sub-report makes a distinction as to the type
of the "Amount".

I have thus far been unable to get anything to display
in the sub-report unless I use a separate union query as
the RecordSource and hard-code a "Where expression"
to effect a filtering.

I have the idea that I'm ignorant to a more straight-forward
method of sharing the RecordSource between the parent
report and sub-report.

What say ye?

Thanks,
Bill

 

[Bill Stanton]

Kelvin,
It seems to me that I could write simple invisible controls
in the detail section that invoke a function that would update
global accumulators, initialized in the report's OnOpen code, as
each record is processed. Then, in the reports footer section,
retrieve those values via a simple function for display in the
footer. Does that sound like a viable plan?

For example:

Private Function AccumTypes(Amt As Long, Typ As Long)
If Typ = 1 Then AccumCash = AccumCash + Amt
Else: If Typ = 2 Then AccumChks = AccumChks + Amt
End Function

Private Function TotalChecks() As Currency
TotalChecks = AccumChks
End Function

Private Function TotalCash() As Currency
TotalCash = AccumCash
End Function

Bill

Let me get this straight. You have a 1 page report. In the top portion of
this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report (in the
footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report. Create
a second query based on your union query. Group it by FundTitle and sum the
Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this report as
a subreport to your current report in the page footer section. Make sure it
is large enough to show the number of lines you might have. You do not need
to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report already so any
filter you apply to the record source for the main report will automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects as I
attempt to explain.

The report fits on a single page.

1) The filter to be applied is the value of DOE, DateOfEntry.
2) The main portion of the report creates sums for each FundTitle
found in RecordSource and displays them in the page footer
section.
3) A total for all the Funds is displayed in the report footer.

As it is, or as I was attempting to fix, an additional summation is
performed on the same RecordSource in a sub-report according
to the "Type" of the Amount, i.e., currency versus check.

If I knew how to maintain separate accumulators as each record
is read during the process for the main/parent report, I could easily
format those accumulators into the report's footer and eliminate the
sub-report.

Hope this helps to give a meaningful picture.

Bill

I'm lost to your question. Why are you using a sub-report if they

both
have

the same recordsource? If you are trying to perform different counts

of
the

same data you could have done this through the original recordsource. Can
you give a little more detail on what fields are on your report and

what
the

subform is for?

Kelvin

A bit lost here......

I have a report and sub-report. The RecordSource
for the parent is a union select. The sub-report has
essentially the same RecordSource and I want to
inherit the filter expression from the parent, which
is set in the Open event of the parent. I.e., in the
Open event of the sub-report, I want to do something
like:
Me.Filter = Report!FundsRecentSunday.Filter
Me.FilterOn = True

Where "FundsRecentSunday" is the parent report name.

The sub-report uses conditional formatting to create
a SUM on an Amount field identical to the parent report,
only the sub-report makes a distinction as to the type
of the "Amount".

I have thus far been unable to get anything to display
in the sub-report unless I use a separate union query as
the RecordSource and hard-code a "Where expression"
to effect a filtering.

I have the idea that I'm ignorant to a more straight-forward
method of sharing the RecordSource between the parent
report and sub-report.

What say ye?

Thanks,
Bill

 

[Kelvin]

Sorry, I misunderstood your report structure. You could still use the
subreport option I mentioned just change it to group by Type instead of
FundTitle. Or as you mentioned use unbound fields.

You don't need to go as far as creating functions. You could just create a
public variable at the top of the code page for the report. Then on the
OnFormat code for the section place the code to add to this variable.

dblTotal = dblTotal + [Amount]

Since you want to keep track of the 2 types, you could create 2 variables
then use an if statement to add to the correct variable.

If [Type] = "Currency" then
dblTotalCurrency = dblTotalCurrency + [Amount]
Else
dblTotalCheck = dblTotalCheck + [Amount]
End if

Then in the OnFormat of the footer update the total boxes

txtTotalCurrency = dblTotalCurrency
txtTotalCheck = dblTotalCheck

Kelvin

Kelvin,
It seems to me that I could write simple invisible controls
in the detail section that invoke a function that would update
global accumulators, initialized in the report's OnOpen code, as
each record is processed. Then, in the reports footer section,
retrieve those values via a simple function for display in the
footer. Does that sound like a viable plan?

For example:

Private Function AccumTypes(Amt As Long, Typ As Long)
If Typ = 1 Then AccumCash = AccumCash + Amt
Else: If Typ = 2 Then AccumChks = AccumChks + Amt
End Function

Private Function TotalChecks() As Currency
TotalChecks = AccumChks
End Function

Private Function TotalCash() As Currency
TotalCash = AccumCash
End Function

Bill

Let me get this straight. You have a 1 page report. In the top portion of
this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report (in the
footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report. Create
a second query based on your union query. Group it by FundTitle and sum the
Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this

report

as
a subreport to your current report in the page footer section. Make

sure

it
is large enough to show the number of lines you might have. You do not need
to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report already so any
filter you apply to the record source for the main report will automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects as I
attempt to explain.

The report fits on a single page.

1) The filter to be applied is the value of DOE, DateOfEntry.
2) The main portion of the report creates sums for each FundTitle
found in RecordSource and displays them in the page footer
section.
3) A total for all the Funds is displayed in the report footer.

As it is, or as I was attempting to fix, an additional summation is
performed on the same RecordSource in a sub-report according
to the "Type" of the Amount, i.e., currency versus check.

If I knew how to maintain separate accumulators as each record
is read during the process for the main/parent report, I could easily
format those accumulators into the report's footer and eliminate the
sub-report.

Hope this helps to give a meaningful picture.

Bill



I'm lost to your question. Why are you using a sub-report if they both
have
the same recordsource? If you are trying to perform different

counts

of recordsource.
Can

 

[Bill Stanton]

Kelvin,
I've encountered some sort of problem that might be due
to the recent migration to Windows XP and a fresh
installation of Office 2000.

I added the Public variables to the report's code sheet and
coded the accumulator updates in the OnFormat of the
detail section. When I run (preview) the report from the
report's Design View, the report does not display. It seems
that some sort of error is being encountered but not being
reported by Access. I put a Msgbox statement to confirm
that the OnFormat code is executing on each of the records
in the RecordSource, but the Msgbox only displays when
I've commented out the code in that subroutine, thus my
conclusion that an error is occurring.

What might have done in the re-installation of Office 2000
that would disable error reporting in Access?

Bill

Sorry, I misunderstood your report structure. You could still use the
subreport option I mentioned just change it to group by Type instead of
FundTitle. Or as you mentioned use unbound fields.

You don't need to go as far as creating functions. You could just create a
public variable at the top of the code page for the report. Then on the
OnFormat code for the section place the code to add to this variable.

dblTotal = dblTotal + [Amount]

Since you want to keep track of the 2 types, you could create 2 variables
then use an if statement to add to the correct variable.

If [Type] = "Currency" then
dblTotalCurrency = dblTotalCurrency + [Amount]
Else
dblTotalCheck = dblTotalCheck + [Amount]
End if

Then in the OnFormat of the footer update the total boxes

txtTotalCurrency = dblTotalCurrency
txtTotalCheck = dblTotalCheck

Kelvin

Kelvin,
It seems to me that I could write simple invisible controls
in the detail section that invoke a function that would update
global accumulators, initialized in the report's OnOpen code, as
each record is processed. Then, in the reports footer section,
retrieve those values via a simple function for display in the
footer. Does that sound like a viable plan?

For example:

Private Function AccumTypes(Amt As Long, Typ As Long)
If Typ = 1 Then AccumCash = AccumCash + Amt
Else: If Typ = 2 Then AccumChks = AccumChks + Amt
End Function

Private Function TotalChecks() As Currency
TotalChecks = AccumChks
End Function

Private Function TotalCash() As Currency
TotalCash = AccumCash
End Function

Bill

Let me get this straight. You have a 1 page report. In the top

portion
of

this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report (in the
footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report. Create
a second query based on your union query. Group it by FundTitle and

sum
the

Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this

report

as
a subreport to your current report in the page footer section. Make

sure

it
is large enough to show the number of lines you might have. You do

not
need

to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report already so any
filter you apply to the record source for the main report will automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects as I
attempt to explain.

The report fits on a single page.

1) The filter to be applied is the value of DOE, DateOfEntry.
2) The main portion of the report creates sums for each FundTitle
found in RecordSource and displays them in the page footer
section.
3) A total for all the Funds is displayed in the report footer.

As it is, or as I was attempting to fix, an additional summation is
performed on the same RecordSource in a sub-report according
to the "Type" of the Amount, i.e., currency versus check.

If I knew how to maintain separate accumulators as each record
is read during the process for the main/parent report, I could easily
format those accumulators into the report's footer and eliminate the
sub-report.

Hope this helps to give a meaningful picture.

Bill



I'm lost to your question. Why are you using a sub-report if they both
have
the same recordsource? If you are trying to perform different

counts

of
the
same data you could have done this through the original recordsource.
Can
you give a little more detail on what fields are on your report

and
what

the
subform is for?

Kelvin

A bit lost here......

I have a report and sub-report. The RecordSource
for the parent is a union select. The sub-report has
essentially the same RecordSource and I want to
inherit the filter expression from the parent, which
is set in the Open event of the parent. I.e., in the
Open event of the sub-report, I want to do something
like:
Me.Filter = Report!FundsRecentSunday.Filter
Me.FilterOn = True

Where "FundsRecentSunday" is the parent report name.

The sub-report uses conditional formatting to create
a SUM on an Amount field identical to the parent report,
only the sub-report makes a distinction as to the type
of the "Amount".

I have thus far been unable to get anything to display
in the sub-report unless I use a separate union query as
the RecordSource and hard-code a "Where expression"
to effect a filtering.

I have the idea that I'm ignorant to a more straight-forward
method of sharing the RecordSource between the parent
report and sub-report.

What say ye?

Thanks,
Bill

 

[Kelvin]

Sorry I haven't gotten back to you on this. I lost track of this message.

I am not sure why error are not being shown. The only thing I can think of
is that you might have something in your code turning the warnings off.
Check through all the code for "DoCmd.SetWarnings False". Also check any
Macros you might have for this. Try to purposely create an error such as
setting a variable to a text box that doesn't exist and see if this error
appears. You can also put an error handling routine in the report code to
check for the error.

Private Sub Report_OnFormat()

On Error goto ErrorHandlingRoutine
Other code
Exit Sub

ErrorHandlingRoutine:
MsgBox "There was an Error"

End Sub

You said you just upgraded to Windows XP. Do you have a default printer set
up? Access prints to the default printer, so if you don't have a default
printer set, it can't preview it.

Kelvin

Kelvin,
I've encountered some sort of problem that might be due
to the recent migration to Windows XP and a fresh
installation of Office 2000.

I added the Public variables to the report's code sheet and
coded the accumulator updates in the OnFormat of the
detail section. When I run (preview) the report from the
report's Design View, the report does not display. It seems
that some sort of error is being encountered but not being
reported by Access. I put a Msgbox statement to confirm
that the OnFormat code is executing on each of the records
in the RecordSource, but the Msgbox only displays when
I've commented out the code in that subroutine, thus my
conclusion that an error is occurring.

What might have done in the re-installation of Office 2000
that would disable error reporting in Access?

Bill

Sorry, I misunderstood your report structure. You could still use the
subreport option I mentioned just change it to group by Type instead of
FundTitle. Or as you mentioned use unbound fields.

You don't need to go as far as creating functions. You could just

create

a
public variable at the top of the code page for the report. Then on the
OnFormat code for the section place the code to add to this variable.

dblTotal = dblTotal + [Amount]

Since you want to keep track of the 2 types, you could create 2 variables
then use an if statement to add to the correct variable.

If [Type] = "Currency" then
dblTotalCurrency = dblTotalCurrency + [Amount]
Else
dblTotalCheck = dblTotalCheck + [Amount]
End if

Then in the OnFormat of the footer update the total boxes

txtTotalCurrency = dblTotalCurrency
txtTotalCheck = dblTotalCheck

Kelvin

Kelvin,
It seems to me that I could write simple invisible controls
in the detail section that invoke a function that would update
global accumulators, initialized in the report's OnOpen code, as
each record is processed. Then, in the reports footer section,
retrieve those values via a simple function for display in the
footer. Does that sound like a viable plan?

For example:

Private Function AccumTypes(Amt As Long, Typ As Long)
If Typ = 1 Then AccumCash = AccumCash + Amt
Else: If Typ = 2 Then AccumChks = AccumChks + Amt
End Function

Private Function TotalChecks() As Currency
TotalChecks = AccumChks
End Function

Private Function TotalCash() As Currency
TotalCash = AccumCash
End Function

Bill


Let me get this straight. You have a 1 page report. In the top portion
of
this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report

(in
the

footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report.
Create
a second query based on your union query. Group it by FundTitle and sum
the
Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this report
as
a subreport to your current report in the page footer section. Make sure
it
is large enough to show the number of lines you might have. You do not
need
to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report already

so
any

filter you apply to the record source for the main report will
automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table selects

as

I

 

[Bill Stanton]

Kelvin,
I'm not sure what the story is with the lack of error messages
I reported earlier. One thing that came to light is the fact that
if one has a compilation error in the code-sheet of a sub-report,
the sub-report simply won't run and no messages will be issued.

Usually, when I write new code or change other code, I compile
the database to eliminate "typos" or other such problems. Another
good reason to use the "Explicit" attribute in code.

Anyway, other stuff I've been working on lately have produced
error messages that one would expect, so for now I'm moving on
and not too concerned about what I observed last week.

Speaking of error messages, have a look at my post "Run-time
error 2101"... maybe you could help me with that, as nobody
has responded to that since I posted it about 12 hours ago.

Thanks again,
Bill

Sorry I haven't gotten back to you on this. I lost track of this message.

I am not sure why error are not being shown. The only thing I can think of
is that you might have something in your code turning the warnings off.
Check through all the code for "DoCmd.SetWarnings False". Also check any
Macros you might have for this. Try to purposely create an error such as
setting a variable to a text box that doesn't exist and see if this error
appears. You can also put an error handling routine in the report code to
check for the error.

Private Sub Report_OnFormat()

On Error goto ErrorHandlingRoutine
Other code
Exit Sub

ErrorHandlingRoutine:
MsgBox "There was an Error"

End Sub

You said you just upgraded to Windows XP. Do you have a default printer set
up? Access prints to the default printer, so if you don't have a default
printer set, it can't preview it.

Kelvin

Kelvin,
I've encountered some sort of problem that might be due
to the recent migration to Windows XP and a fresh
installation of Office 2000.

I added the Public variables to the report's code sheet and
coded the accumulator updates in the OnFormat of the
detail section. When I run (preview) the report from the
report's Design View, the report does not display. It seems
that some sort of error is being encountered but not being
reported by Access. I put a Msgbox statement to confirm
that the OnFormat code is executing on each of the records
in the RecordSource, but the Msgbox only displays when
I've commented out the code in that subroutine, thus my
conclusion that an error is occurring.

What might have done in the re-installation of Office 2000
that would disable error reporting in Access?

Bill

Sorry, I misunderstood your report structure. You could still use the
subreport option I mentioned just change it to group by Type instead of
FundTitle. Or as you mentioned use unbound fields.

You don't need to go as far as creating functions. You could just

create

a
public variable at the top of the code page for the report. Then on the
OnFormat code for the section place the code to add to this variable.

dblTotal = dblTotal + [Amount]

Since you want to keep track of the 2 types, you could create 2 variables
then use an if statement to add to the correct variable.

If [Type] = "Currency" then
dblTotalCurrency = dblTotalCurrency + [Amount]
Else
dblTotalCheck = dblTotalCheck + [Amount]
End if

Then in the OnFormat of the footer update the total boxes

txtTotalCurrency = dblTotalCurrency
txtTotalCheck = dblTotalCheck

Kelvin

Kelvin,
It seems to me that I could write simple invisible controls
in the detail section that invoke a function that would update
global accumulators, initialized in the report's OnOpen code, as
each record is processed. Then, in the reports footer section,
retrieve those values via a simple function for display in the
footer. Does that sound like a viable plan?

For example:

Private Function AccumTypes(Amt As Long, Typ As Long)
If Typ = 1 Then AccumCash = AccumCash + Amt
Else: If Typ = 2 Then AccumChks = AccumChks + Amt
End Function

Private Function TotalChecks() As Currency
TotalChecks = AccumChks
End Function

Private Function TotalCash() As Currency
TotalCash = AccumCash
End Function

Bill


Let me get this straight. You have a 1 page report. In the top portion
of
this report you want to show the sum of [Amount] based on the [Type],
grouped by [Fundtitle]. Then on the bottom portion of the report (in
the
footer) you want to show the total for each [FundTitle]. See below:

Report of Funds Deposited between 1/1/03 and 12/1/03

Fund Title: Mutual Fund 1
Total of Currency $1000
Total of Checks $2000

Fund Title: Mutual Fund 2
Total of Currency $3000
Total of Checks $4000

Total of All Funds $10,000

Sub Total for Mutual Fund 1 $3000 'This would be in the page footer
Sub Total for Mutual Fund 2 $7000

If this is what you want then yes you will need to use a sub-report.
Create
a second query based on your union query. Group it by FundTitle

and
sum

the
Amount. Also include any other info you want to show in this section.
Create a 1-line report based on this new query. Then include this
report
as
a subreport to your current report in the page footer section. Make
sure
it
is large enough to show the number of lines you might have. You

do
not

need
to set the parent-child link or apply any additional filters since this
report is based on a query that is based on the main report

already

so

any
filter you apply to the record source for the main report will
automatically
get carried over to the sub query.

Kelvin

Kelvin,
I was afraid I'd not given a better picture of what I'm
trying to do. First, let me say that I too thought I'd be
able to tackle the whole problem without a sub-report,
but failed miserably in trying to generate several different
sums of the same field in a single report.

The best way I can think of to get you a clear picture is
to include the query and explain how the fields are used
and what totals I'm trying to capture.

SELECT ALL
[DonRegFam].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegFam] INNER JOIN
[Funds] ON [DonRegFam].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonRegInd].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonRegInd] INNER JOIN
[Funds] ON [DonRegInd].[FundID] = [Funds].[FundID]

UNION ALL SELECT
[DonUnReg].[FundID],[Funds].[FundTitle],[DOE],[Amount],[Type]
FROM [DonUnReg] INNER JOIN
[Funds] ON [DonUnReg].[FundID] = [Funds].[FundID];

It will probably suffice to look at only one of the table

selects

as report
and