If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
list boxes in reports
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can List Boxes be used in a Report | Jay | Setting Up & Running Reports | 1 | July 16th, 2004 02:12 AM |
Display Multi-Select Choices from List Boxes with and with VB code | ExecutiveBest | Worksheet Functions | 1 | July 8th, 2004 03:59 AM |
synchronizing form and list box | Deb Smith | Using Forms | 8 | June 21st, 2004 08:15 PM |
Radio Buttons and Dropdown List Boxes | Pat Stellacci | Worksheet Functions | 1 | October 23rd, 2003 09:26 PM |