list boxes in reports

I work for a fairly large organization. While the
organization itself does not change all that frequently,
personnell do, or change department. etc. Then of course
there is the musical chairs problem with people using
desks in other Depts. etc. (Yes, it's a government
organization.) Therefore it is a challenge to keep track
of who is currently in what dept. and who reports to whom.
I have an organization table that lists the various
departments and numbers the level in the org, similar to a
WBS. That is, the CEO's office is 1.0, the three VP's are
1.1, 1.2, 1.3, the first three directors are 1.1.1, 1.2.1,
1.3.1, etc.

I have developed two reports. One answers the
question "Who are the current members of dept "X"?" and
the other answers the question "Who reports to "John
Doe"?" and these work OK.

Now I'd like to make an emergency phone tree report that
is presented in a format similar to an Org Chart. Now, I
can draw the tree diagram on a blank report and populate
the branches with text boxes. Then if I put a dlookup in
the box that says

dlookup("[Name]","Organization","[Level = '1.3.1'")

then I'll get the name of the first director reporting to
the 3rd VP. To get is phone number I could conjoin another
text box with a similar dlookup for the phone number.

If I use a similar approach with a list box I can get both
the director and his assistants and the phone numbers in
one box. Likewise I can get a dept. head and all his
direct reports in one box. That works OK except a list box
doesn't resize as required, so there is a certain amount
of maintenance required as department staff increases.
Likewise if (when) the organizational structure changes.

The first three levels will fit on a one page report.

Lower levels will require separate reports because once
you get down to the department level all the staff in one
department are at the same level and the list boxes get
large. You may then have a director, and three or four
departments he controls, on one page.

All the same info is available on the "Reports to" report,
but it is useful for this application to present just
limited info(phone numbers) on a semi-graphical report.

So, a few questions:

How come a list box works on a report but a combo box with
the same input doesn't? I realize the functionality of a
combo box is meaningless on a report, but I don't see whiy
it would work just like a list box.

How would I go about automatically resizing the list
boxes, recognizing that I might still have to adjust their
position on the page manually? I suppose I could create
the whole report in code, but that may come later.

Anybody got a better approach? I thought of exporting data
to an org chart diagram in PPoint, but the org chart tool
there is too rudimentary.

List Boxes are for allowing the user to choose a value or values; they are
not for displaying information on Reports.

You may find it exceedingly difficult to accomplish exactly what you want in
the format you describe. Consider "stepping back from the problem" and
attempt to accomplish the same result in a format that is more compatible
with Access reporting.

Otherwise, you likely will need to use a tool that is more oriented to
organization charts (which Access reporting isn't, really). Perhaps Visio
with its VBA might be an option, or some third-party org chart tool for
which you can export a text file in its required format.

Larry Linson
Microsoft Access MVP



"Mia_placidus" wrote in message
...
I work for a fairly large organization. While the
organization itself does not change all that frequently,
personnell do, or change department. etc. Then of course
there is the musical chairs problem with people using
desks in other Depts. etc. (Yes, it's a government
organization.) Therefore it is a challenge to keep track
of who is currently in what dept. and who reports to whom.
I have an organization table that lists the various
departments and numbers the level in the org, similar to a
WBS. That is, the CEO's office is 1.0, the three VP's are
1.1, 1.2, 1.3, the first three directors are 1.1.1, 1.2.1,
1.3.1, etc.

I have developed two reports. One answers the
question "Who are the current members of dept "X"?" and
the other answers the question "Who reports to "John
Doe"?" and these work OK.

Now I'd like to make an emergency phone tree report that
is presented in a format similar to an Org Chart. Now, I
can draw the tree diagram on a blank report and populate
the branches with text boxes. Then if I put a dlookup in
the box that says

dlookup("[Name]","Organization","[Level = '1.3.1'")

then I'll get the name of the first director reporting to
the 3rd VP. To get is phone number I could conjoin another
text box with a similar dlookup for the phone number.

If I use a similar approach with a list box I can get both
the director and his assistants and the phone numbers in
one box. Likewise I can get a dept. head and all his
direct reports in one box. That works OK except a list box
doesn't resize as required, so there is a certain amount
of maintenance required as department staff increases.
Likewise if (when) the organizational structure changes.

The first three levels will fit on a one page report.

Lower levels will require separate reports because once
you get down to the department level all the staff in one
department are at the same level and the list boxes get
large. You may then have a director, and three or four
departments he controls, on one page.

All the same info is available on the "Reports to" report,
but it is useful for this application to present just
limited info(phone numbers) on a semi-graphical report.

So, a few questions:

How come a list box works on a report but a combo box with
the same input doesn't? I realize the functionality of a
combo box is meaningless on a report, but I don't see whiy
it would work just like a list box.

How would I go about automatically resizing the list
boxes, recognizing that I might still have to adjust their
position on the page manually? I suppose I could create
the whole report in code, but that may come later.

Anybody got a better approach? I thought of exporting data
to an org chart diagram in PPoint, but the org chart tool
there is too rudimentary.