A bit lost here

  • Thread starter Thread starter Bill Stanton
  • Start date Start date
B

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
 
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
 
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
 
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

Bill Stanton said:
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 said:
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
Click to expand...
Click to expand...
 
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


Kelvin said:
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

Bill Stanton said:
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 said:
I'm lost to your question. Why are you using a sub-report if they
Click to expand...
both
have
the same recordsource? If you are trying to perform different counts
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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


Kelvin said:
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

Bill Stanton said:
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 said:
I'm lost to your question. Why are you using a sub-report if they
Click to expand...
both
have
the same recordsource? If you are trying to perform different counts
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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

Bill Stanton said:
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


Kelvin said:
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
Click to expand...
report
as
a subreport to your current report in the page footer section. Make
Click to expand...
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

Bill Stanton said:
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
Click to expand...
Click to expand...
counts
of recordsource.
Can
Click to expand...
Click to expand...
 
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



Kelvin said:
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

Bill Stanton said:
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


Kelvin said:
Let me get this straight. You have a 1 page report. In the top
Click to expand...
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
Click to expand...
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
Click to expand...
report
as
a subreport to your current report in the page footer section. Make
Click to expand...
sure
it
is large enough to show the number of lines you might have. You do
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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

Bill Stanton said:
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



Kelvin said:
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
Click to expand...
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

Bill Stanton said:
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
Click to expand...
(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
Click to expand...
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
Click to expand...
Click to expand...
as
I
Click to expand...
Click to expand...
 
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
Kelvin said:
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

Bill Stanton said:
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



Kelvin said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
selects
as report
and
Click to expand...
Click to expand...
 
Back
Top