Reports, Subreports & VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user form. My
problem is I have a sub report within that report that I want to be able to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User. All 3
criteria will be passed to the main report; however I want to only pass the
Start & End Date to the sub-report without prompting the user to re-enter
information.

The sub-report was created only to determine the “# of Accounts – by typeâ€
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I need to use
the Total # of Accounts Field (For all Users) on the sub–report to perform a
calculation on my main report to find the percentage of work for that user.

Is there a way to pass criteria to a Sub-report when passing the criteria to
the main report through code?

Ideally, I’d love to be able to eliminate the sub report and just determine
the value I need thru code; however I’m not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
 
The simplest way to pass a range to the subreport is to have its query read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time it formats
another section where the subreport is. That means the form needs to stay
open in the background until the report is closed.
 
This won't necessarly work, or I'm not sure how to make it work.

Based on who the user is, and from what Command button they open the Form
with the date fields, different fields appear on the user form.

In all case scenarios, I want to run the sub-report for the same time frame
as the main report. However, the date fields will not always show. If they
select a button to run from the current month, the dates will not show on the
form and I pass the dates through code. In that case I am using different
criteria or fields to pass the variables as I would if they entered them on
the user form.

If dates are entered onthe userform, the fields passed are:
txtStartDate & txtEndDate

If both the dates are left blank (or do not appear on the form), I pass the
following to run for the current month:
" Between " & Format(DateSerial(Year(Date),
Month(Date), 1), conDateFormat) & " And " & Format(Date, conDateFormat)

Furthermore, the user has the option of leaving just one of the date fields
blank, in which case I am not using "Between" but ">" or "<" to pass the date.

The field name on the report is EntryDate.

I am not sure who to handle all these options through the reports criteria.

Any more suggestions would be greatly appreciated.



Allen Browne said:
The simplest way to pass a range to the subreport is to have its query read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time it formats
another section where the subreport is. That means the form needs to stay
open in the background until the report is closed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kathy - Lovullo said:
I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user form. My
problem is I have a sub report within that report that I want to be able
to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User. All 3
criteria will be passed to the main report; however I want to only pass
the
Start & End Date to the sub-report without prompting the user to re-enter
information.

The sub-report was created only to determine the "# of Accounts - by type"
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I need to
use
the Total # of Accounts Field (For all Users) on the sub-report to
perform a
calculation on my main report to find the percentage of work for that
user.

Is there a way to pass criteria to a Sub-report when passing the criteria
to
the main report through code?

Ideally, I'd love to be able to eliminate the sub report and just
determine
the value I need thru code; however I'm not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
Click to expand...
Click to expand...
 
Okay, what I do when nothing else works is to actually re-write the SQL
property of the query the subreport is based on before you OpenReport.

Of course, this only works correctly if you always open the report through
code, but it's quite simple to execute. Sounds like you are already building
up the WhereCondition string for the OpenReport action? In just the same
way, you build up the WHERE clause for the SQL statement for the query of
the subreport.

This kind of thing:
Dim strSql As String
strSql = "SELECT Table2.* FROM Table2 WHERE ...
CurrentDb().QueryDefs("Query2").SQL = strSql
DoCmd.OpenReport ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kathy - Lovullo said:
This won't necessarly work, or I'm not sure how to make it work.

Based on who the user is, and from what Command button they open the Form
with the date fields, different fields appear on the user form.

In all case scenarios, I want to run the sub-report for the same time
frame
as the main report. However, the date fields will not always show. If
they
select a button to run from the current month, the dates will not show on
the
form and I pass the dates through code. In that case I am using different
criteria or fields to pass the variables as I would if they entered them
on
the user form.

If dates are entered onthe userform, the fields passed are:
txtStartDate & txtEndDate

If both the dates are left blank (or do not appear on the form), I pass
the
following to run for the current month:
" Between " & Format(DateSerial(Year(Date),
Month(Date), 1), conDateFormat) & " And " & Format(Date, conDateFormat)

Furthermore, the user has the option of leaving just one of the date
fields
blank, in which case I am not using "Between" but ">" or "<" to pass the
date.

The field name on the report is EntryDate.

I am not sure who to handle all these options through the reports
criteria.

Any more suggestions would be greatly appreciated.



Allen Browne said:
The simplest way to pass a range to the subreport is to have its query
read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time it
formats
another section where the subreport is. That means the form needs to stay
open in the background until the report is closed.


message
I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user form. My
problem is I have a sub report within that report that I want to be
able
to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User.
All 3
criteria will be passed to the main report; however I want to only pass
the
Start & End Date to the sub-report without prompting the user to
re-enter
information.

The sub-report was created only to determine the "# of Accounts - by
type"
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I need to
use
the Total # of Accounts Field (For all Users) on the sub-report to
perform a
calculation on my main report to find the percentage of work for that
user.

Is there a way to pass criteria to a Sub-report when passing the
criteria
to
the main report through code?

Ideally, I'd love to be able to eliminate the sub report and just
determine
the value I need thru code; however I'm not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen,

It sounds like this is exactly what I am looking for. I do have a question
on your solution however.

In the example of your SQL Statement, you refer to "Table2". I am I
correct that this is the name of the table used in my "Subquery"? Likewise,
is "Qeury2" the my subreport? And when I run the "Docmd.OpenReport" am I
having the code run the main report or the subreport?

Sorry for so many questions, but although I manage to get by, I am not too
familiar with SQL statements.

Thanks!

Allen Browne said:
Okay, what I do when nothing else works is to actually re-write the SQL
property of the query the subreport is based on before you OpenReport.

Of course, this only works correctly if you always open the report through
code, but it's quite simple to execute. Sounds like you are already building
up the WhereCondition string for the OpenReport action? In just the same
way, you build up the WHERE clause for the SQL statement for the query of
the subreport.

This kind of thing:
Dim strSql As String
strSql = "SELECT Table2.* FROM Table2 WHERE ...
CurrentDb().QueryDefs("Query2").SQL = strSql
DoCmd.OpenReport ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kathy - Lovullo said:
This won't necessarly work, or I'm not sure how to make it work.

Based on who the user is, and from what Command button they open the Form
with the date fields, different fields appear on the user form.

In all case scenarios, I want to run the sub-report for the same time
frame
as the main report. However, the date fields will not always show. If
they
select a button to run from the current month, the dates will not show on
the
form and I pass the dates through code. In that case I am using different
criteria or fields to pass the variables as I would if they entered them
on
the user form.

If dates are entered onthe userform, the fields passed are:
txtStartDate & txtEndDate

If both the dates are left blank (or do not appear on the form), I pass
the
following to run for the current month:
" Between " & Format(DateSerial(Year(Date),
Month(Date), 1), conDateFormat) & " And " & Format(Date, conDateFormat)

Furthermore, the user has the option of leaving just one of the date
fields
blank, in which case I am not using "Between" but ">" or "<" to pass the
date.

The field name on the report is EntryDate.

I am not sure who to handle all these options through the reports
criteria.

Any more suggestions would be greatly appreciated.



Allen Browne said:
The simplest way to pass a range to the subreport is to have its query
read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time it
formats
another section where the subreport is. That means the form needs to stay
open in the background until the report is closed.


message
I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user form. My
problem is I have a sub report within that report that I want to be
able
to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User.
All 3
criteria will be passed to the main report; however I want to only pass
the
Start & End Date to the sub-report without prompting the user to
re-enter
information.

The sub-report was created only to determine the "# of Accounts - by
type"
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I need to
use
the Total # of Accounts Field (For all Users) on the sub-report to
perform a
calculation on my main report to find the percentage of work for that
user.

Is there a way to pass criteria to a Sub-report when passing the
criteria
to
the main report through code?

Ideally, I'd love to be able to eliminate the sub report and just
determine
the value I need thru code; however I'm not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Yes: replace Table2 and Query2 with the names of your own objects.

Yes: open the main report. After changing the SQL of the subreport's query,
it should give the right results when the main report calls it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kathy - Lovullo said:
Thanks Allen,

It sounds like this is exactly what I am looking for. I do have a
question
on your solution however.

In the example of your SQL Statement, you refer to "Table2". I am I
correct that this is the name of the table used in my "Subquery"?
Likewise,
is "Qeury2" the my subreport? And when I run the "Docmd.OpenReport" am I
having the code run the main report or the subreport?

Sorry for so many questions, but although I manage to get by, I am not too
familiar with SQL statements.

Thanks!

Allen Browne said:
Okay, what I do when nothing else works is to actually re-write the SQL
property of the query the subreport is based on before you OpenReport.

Of course, this only works correctly if you always open the report
through
code, but it's quite simple to execute. Sounds like you are already
building
up the WhereCondition string for the OpenReport action? In just the same
way, you build up the WHERE clause for the SQL statement for the query of
the subreport.

This kind of thing:
Dim strSql As String
strSql = "SELECT Table2.* FROM Table2 WHERE ...
CurrentDb().QueryDefs("Query2").SQL = strSql
DoCmd.OpenReport ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
This won't necessarly work, or I'm not sure how to make it work.

Based on who the user is, and from what Command button they open the
Form
with the date fields, different fields appear on the user form.

In all case scenarios, I want to run the sub-report for the same time
frame
as the main report. However, the date fields will not always show. If
they
select a button to run from the current month, the dates will not show
on
the
form and I pass the dates through code. In that case I am using
different
criteria or fields to pass the variables as I would if they entered
them
on
the user form.

If dates are entered onthe userform, the fields passed are:
txtStartDate & txtEndDate

If both the dates are left blank (or do not appear on the form), I pass
the
following to run for the current month:
" Between " & Format(DateSerial(Year(Date),
Month(Date), 1), conDateFormat) & " And " & Format(Date,
conDateFormat)

Furthermore, the user has the option of leaving just one of the date
fields
blank, in which case I am not using "Between" but ">" or "<" to pass
the
date.

The field name on the report is EntryDate.

I am not sure who to handle all these options through the reports
criteria.

Any more suggestions would be greatly appreciated.



:

The simplest way to pass a range to the subreport is to have its query
read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time it
formats
another section where the subreport is. That means the form needs to
stay
open in the background until the report is closed.


message
I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user form.
My
problem is I have a sub report within that report that I want to be
able
to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User.
All 3
criteria will be passed to the main report; however I want to only
pass
the
Start & End Date to the sub-report without prompting the user to
re-enter
information.

The sub-report was created only to determine the "# of Accounts - by
type"
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I need
to
use
the Total # of Accounts Field (For all Users) on the sub-report to
perform a
calculation on my main report to find the percentage of work for
that
user.

Is there a way to pass criteria to a Sub-report when passing the
criteria
to
the main report through code?

Ideally, I'd love to be able to eliminate the sub report and just
determine
the value I need thru code; however I'm not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Allen,

Thanks for all your help on this... but could you help me one last time. I
think I am very close to getting this work but I am having a little problem,
possibly with syntax.

I believe I am setting my string properly with the following:

strSQL = "SELECT * FROM Transactions WHERE EntryDate Between " _
& Format(Me.txtStartDate, conDateFormat) & " And " _
& Format(Me.txtEndDate, conDateFormat)

( with Transactions is the table I am pulling the data from)

However, I am receiving the message "Item not found in collection" when
trying to execute the second piece of this code:

CurrentDb.QueryDefs("Percent Subreport - New").SQL = strSQL

Where Am I going wrong?
Thanks!!!!!!!
Kathy

PS - Sorry for the delay in responding, I was out of the office most of last
week.

Allen Browne said:
Yes: replace Table2 and Query2 with the names of your own objects.

Yes: open the main report. After changing the SQL of the subreport's query,
it should give the right results when the main report calls it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kathy - Lovullo said:
Thanks Allen,

It sounds like this is exactly what I am looking for. I do have a
question
on your solution however.

In the example of your SQL Statement, you refer to "Table2". I am I
correct that this is the name of the table used in my "Subquery"?
Likewise,
is "Qeury2" the my subreport? And when I run the "Docmd.OpenReport" am I
having the code run the main report or the subreport?

Sorry for so many questions, but although I manage to get by, I am not too
familiar with SQL statements.

Thanks!

Allen Browne said:
Okay, what I do when nothing else works is to actually re-write the SQL
property of the query the subreport is based on before you OpenReport.

Of course, this only works correctly if you always open the report
through
code, but it's quite simple to execute. Sounds like you are already
building
up the WhereCondition string for the OpenReport action? In just the same
way, you build up the WHERE clause for the SQL statement for the query of
the subreport.

This kind of thing:
Dim strSql As String
strSql = "SELECT Table2.* FROM Table2 WHERE ...
CurrentDb().QueryDefs("Query2").SQL = strSql
DoCmd.OpenReport ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
This won't necessarly work, or I'm not sure how to make it work.

Based on who the user is, and from what Command button they open the
Form
with the date fields, different fields appear on the user form.

In all case scenarios, I want to run the sub-report for the same time
frame
as the main report. However, the date fields will not always show. If
they
select a button to run from the current month, the dates will not show
on
the
form and I pass the dates through code. In that case I am using
different
criteria or fields to pass the variables as I would if they entered
them
on
the user form.

If dates are entered onthe userform, the fields passed are:
txtStartDate & txtEndDate

If both the dates are left blank (or do not appear on the form), I pass
the
following to run for the current month:
" Between " & Format(DateSerial(Year(Date),
Month(Date), 1), conDateFormat) & " And " & Format(Date,
conDateFormat)

Furthermore, the user has the option of leaving just one of the date
fields
blank, in which case I am not using "Between" but ">" or "<" to pass
the
date.

The field name on the report is EntryDate.

I am not sure who to handle all these options through the reports
criteria.

Any more suggestions would be greatly appreciated.



:

The simplest way to pass a range to the subreport is to have its query
read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time it
formats
another section where the subreport is. That means the form needs to
stay
open in the background until the report is closed.


message
I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user form.
My
problem is I have a sub report within that report that I want to be
able
to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User.
All 3
criteria will be passed to the main report; however I want to only
pass
the
Start & End Date to the sub-report without prompting the user to
re-enter
information.

The sub-report was created only to determine the "# of Accounts - by
type"
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I need
to
use
the Total # of Accounts Field (For all Users) on the sub-report to
perform a
calculation on my main report to find the percentage of work for
that
user.

Is there a way to pass criteria to a Sub-report when passing the
criteria
to
the main report through code?

Ideally, I'd love to be able to eliminate the sub report and just
determine
the value I need thru code; however I'm not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
Click to expand...
Click to expand...
Click to expand...
 
To find out what's wrong, open the Immediate window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("Percent Subreport - New").SQL
When you press enter do you get the same error? If so, you have the name of
the query wrong. Is then a double space, for example?

If not, add this line to your code just before the CurrentDb line:
Debug.Print strSQL
Run the code. When it fails, reset it, and copy the statement from the
Immediate window. Then create a new query (no table), and swith to SQL View
(View menu, from query design). Paste the statement in.

Can you see what's wrong? For example if the name of the EntryDate field
contains a space, try:
WHERE [Entry Date] Between ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Kathy - Lovullo said:
Allen,

Thanks for all your help on this... but could you help me one last time.
I
think I am very close to getting this work but I am having a little
problem,
possibly with syntax.

I believe I am setting my string properly with the following:

strSQL = "SELECT * FROM Transactions WHERE EntryDate Between " _
& Format(Me.txtStartDate, conDateFormat) & " And " _
& Format(Me.txtEndDate, conDateFormat)

( with Transactions is the table I am pulling the data from)

However, I am receiving the message "Item not found in collection" when
trying to execute the second piece of this code:

CurrentDb.QueryDefs("Percent Subreport - New").SQL = strSQL

Where Am I going wrong?
Thanks!!!!!!!
Kathy

PS - Sorry for the delay in responding, I was out of the office most of
last
week.

Allen Browne said:
Yes: replace Table2 and Query2 with the names of your own objects.

Yes: open the main report. After changing the SQL of the subreport's
query,
it should give the right results when the main report calls it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks Allen,

It sounds like this is exactly what I am looking for. I do have a
question
on your solution however.

In the example of your SQL Statement, you refer to "Table2". I am I
correct that this is the name of the table used in my "Subquery"?
Likewise,
is "Qeury2" the my subreport? And when I run the "Docmd.OpenReport" am
I
having the code run the main report or the subreport?

Sorry for so many questions, but although I manage to get by, I am not
too
familiar with SQL statements.

Thanks!

:

Okay, what I do when nothing else works is to actually re-write the
SQL
property of the query the subreport is based on before you OpenReport.

Of course, this only works correctly if you always open the report
through
code, but it's quite simple to execute. Sounds like you are already
building
up the WhereCondition string for the OpenReport action? In just the
same
way, you build up the WHERE clause for the SQL statement for the query
of
the subreport.

This kind of thing:
Dim strSql As String
strSql = "SELECT Table2.* FROM Table2 WHERE ...
CurrentDb().QueryDefs("Query2").SQL = strSql
DoCmd.OpenReport ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
This won't necessarly work, or I'm not sure how to make it work.

Based on who the user is, and from what Command button they open the
Form
with the date fields, different fields appear on the user form.

In all case scenarios, I want to run the sub-report for the same
time
frame
as the main report. However, the date fields will not always show.
If
they
select a button to run from the current month, the dates will not
show
on
the
form and I pass the dates through code. In that case I am using
different
criteria or fields to pass the variables as I would if they entered
them
on
the user form.

If dates are entered onthe userform, the fields passed are:
txtStartDate & txtEndDate

If both the dates are left blank (or do not appear on the form), I
pass
the
following to run for the current month:
" Between " & Format(DateSerial(Year(Date),
Month(Date), 1), conDateFormat) & " And " & Format(Date,
conDateFormat)

Furthermore, the user has the option of leaving just one of the date
fields
blank, in which case I am not using "Between" but ">" or "<" to pass
the
date.

The field name on the report is EntryDate.

I am not sure who to handle all these options through the reports
criteria.

Any more suggestions would be greatly appreciated.



:

The simplest way to pass a range to the subreport is to have its
query
read
the values directly from your form.

1. Open the query that the subreport is based on, in design view.

2 In the Criteria row under the date field, enter:
Between [Forms].[Form1].[StartDate] And
[Forms].[Form1].[EndDate]
substituting the names of your form and text boxes.

3. To ensure Access undersands these as dates, declare them.
Choose Parameters on the Query menu. In the 2-column dialog, enter:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The subreport will now read these values from the form every time
it
formats
another section where the subreport is. That means the form needs
to
stay
open in the background until the report is closed.


message
I am having some difficulties with a Report / Subreport situation.

I have a report that I am running based on entries in a user
form.
My
problem is I have a sub report within that report that I want to
be
able
to
pass criteria to and I'm unsure how.

I basically have 3 criteria to pass: Start Date, End Date & User.
All 3
criteria will be passed to the main report; however I want to
only
pass
the
Start & End Date to the sub-report without prompting the user to
re-enter
information.

The sub-report was created only to determine the "# of Accounts -
by
type"
for ALL Users for the same time period as the Main Report.

Generally, the Main Report is run for an Individual user, but I
need
to
use
the Total # of Accounts Field (For all Users) on the sub-report
to
perform a
calculation on my main report to find the percentage of work for
that
user.

Is there a way to pass criteria to a Sub-report when passing the
criteria
to
the main report through code?

Ideally, I'd love to be able to eliminate the sub report and just
determine
the value I need thru code; however I'm not sure how to do that.

Any help or advice would be greatly appreciated.
Kathy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top