Split a report via VBA

[Ivan Grozney]

I have a report that is grouped by Department and Division in my company.
Sometimes the end user wants it all together in one report and sometimes they
want each department split out onto different reports and sometimes they want
each department and division split out into one report.

Is there a way with VBA to let them select if they want it all in one report
or split into separate reports without writing a whole bunch of reports?

TIA

Vanya

 

[Allen Browne]

Could you provide a little unbound form where the user can select the
Department or Division. Then when the click the command button on the form,
you open it filtered to the appropriate Department or Division, or
unfiltered if they chose neither.

Would that achieve what you need? It would just be a matter of:
Dim strWhere As String
'Set the filter string based on what they chose.
DoCmd.OpenReport "Report1" acViewPreview, , strWhere

 

[Armen Stein]

Could you provide a little unbound form where the user can select the
Department or Division. Then when the click the command button on the form,
you open it filtered to the appropriate Department or Division, or
unfiltered if they chose neither.

For some techniques on how to do this, see Report Selection Techniques
at http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Ivan Grozney]

Allen,

Thanks for the idea. What they want though is if they choose no
department or a single department to have a choice to print it all in one
report OR to have it split all the dept/div onto their own report without
choosing the department or division...

So if they chose HR as the department and the divisions are
Volunteer
Recruiting
Benefits
Admin

They would like a button that would all HR/Admin, HR/Volunteer,
HR/Recruiting, and HR/Benefits either all on one report or have the Dept/Div
combo on their own report without choosing which divisions...

I was thinking of running a loop of each department/Division, passing that
into the source query of the report, printing it and then loop to the next
dept/div set.

My hope is that there might be a more elegant way of doing it.

Thanks,
Vanya

 

[Ivan Grozney]

Thank you, Armen. I check it out.

Vanya

For some techniques on how to do this, see Report Selection Techniques
at http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Allen Browne]

Ivan, I'm not sure I understand exactly what you are doing, but I'm
convinced you can do this just by filtering the report rather than using VBA
to loop the records.

Perhaps you have a table where you schedule resources, with fields:
- SchedID autonumber
- ResourceID what is being scheduled
- SchedDateTime when it is scheduled for.
Now say a resource belongs to a divison, which belongs to a department. What
I suggested would let you create a query where you can filter by Department.
Under the Department, your report groups by Divison. Under Division, each
resource scheduled is listed. That's a common need.

Or perhaps you want to do it this way. Any time any resoruce is scheduled,
you want it shown for ALL divisions of the department: not just the division
that owns the resource. So, if the Recruiting department owns a data
projector, you don't just want:
Department: HR
Division: Recruiting
Resource: Data Projector 22
but you want that row *repeated* for every division of the department, even
though those divisions don't own the resoruce, i.e.:
Department: HR
Division: Recruiting
Resource: Data Projector 22
Division: Volunteer
Resource: Data Projector 22
Division: Benefits
Resource: Data Projector 22
Division: Admin
Resource: Data Projector 22

Is that what you want? If so, create a query using justg Department and
Division. Save it. Then use it as a source 'table' in your main query,
joined on the DepartmentID. It will then repeat every record for all
divisions of the relevant department.

If that's not what you are doing, I have not understood the problem.

 

[Ivan Grozney]

Allen,

I appreciate your willingness to help me through this. I guess I am not
explaining it very well...

What I have is a Workers' Comp system. When the Safety people meet with
the different Department and Divisions they like to have reports to hand out.
Of course who the meet with determines the type of report.

So one might be all the items on one report.

Dept: HR
Div: Volunteer
John Doe Broken Arm Urgent Care
Bob Bosco Contusion First Aid

Div: Benefits
Amy Olds Laceration First Aid

Div: Admin
Ted Gross Broken Nose Emergency Room

Dept: Finance
Div: Purchasing
etc.


Or they need to print them as individual reports.

Dept: HR
Div: Volunteer
John Doe Broken Arm Urgent Care
Bob Bosco Contusion First Aid

----------New Report

Dept: HR
Div: Benefits
Amy Olds Laceration First Aid

----------New Report

Dept: HR
Div: Admin
Ted Gross Broken Nose Emergency Room

----------New Report

Dept: Finance
Div: Purchasing
etc.

I would like to have them have a way on the form (toggle or ???) that when
selected and they print a report, the report will come out all on one or in
separate reports without them having to select HR, Volunteer, run the report.
Go back, select HR, Benefits, run the report and so on until all the
dept/div combinations are selected.

I hope this explains it better

Thanks,

Vanya

 

[Armen Stein]

I would like to have them have a way on the form (toggle or ???) that when
selected and they print a report, the report will come out all on one or in
separate reports without them having to select HR, Volunteer, run the report.
Go back, select HR, Benefits, run the report and so on until all the
dept/div combinations are selected.

If it's just to hand out to different people, could you just use page
breaks to have each section print on a separate page? See Force New
Page property. You could even control that property in code if you
want it on sometimes and off sometimes.

Regarding selecting individual sections: To do a multi-select with
checkboxes, you can load up a work table with all the possibilities,
and include a Yes/No field in it, say IncludeOnReportFlag. Then
display a continuous subform with that table and allow the user to
checkmark the ones they want. Then use that work table in the report
with an inner join to the main records you are reporting, with a Where
clause that selects IncludeOnReportFlag = True. This will limit the
report to the sections that were selected by the user.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Ivan Grozney]

Thanks for sticking with me on this one, Armen. I'll take a look at your
suggestions.

Vanya