Solved

Can I VBA Loop through a chain of command? (who reports to who)...

Posted on 2007-04-02
19
330 Views
Last Modified: 2008-02-01
I have 2 columns Employee ID and Manager ID... how is it possible to run a query which traces for employee #1's manager and his manager etc... and keeps going until it hits the CEO (who is #4)  who reports to no one?
so the results would look like this:
Employee      Manager
2      9
9      88
88      4
Next…            
Employee      Manager
5      7
7      56
56      4

Next...
0
Comment
Question by:computerider
19 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 18838058

Is there a top number of levels you may see?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 18838073
using the northwind employees table

select
  Emp.employeeid
  ,Emp.lastname as Employee
  ,Emp.reportsto
  ,Mgr.employeeid
  ,Mgr.lastname as Manager
from employees Emp left outer join employees Mgr
on Emp.reportsto  = Mgr.employeeid

0
 

Author Comment

by:computerider
ID: 18838086
no -  just a list of who reports to who so it doesn’t distinguish titles – you either have someone reporting to you or you don’t…which is why I am guessing it has to loop until it hits the CEO
0
 

Author Comment

by:computerider
ID: 18838206
Close but you this produces a simple who reports to who - I am trying to have the results look like this (these are based on the results of the Northwinds query)

so If I report to Buchanon tell me who my boss (Buchanon)  reports to, then who his boos reports to until we hit the top of the chain and grab the next guy...

Employee ID      Last Name      Reports To      Employee ID      Last Name
6      Suyama      Buchanan, Steven      5      Buchanan
5      Buchanan      Fuller, Andrew      2      Fuller
2      Fuller                  
Next Employee                        
1      Davolio      Fuller, Andrew      2      Fuller
2      Fuller                  
Next Employee                        
7      King      Buchanan, Steven      5      Buchanan
5      Buchanan      Fuller, Andrew      2      Fuller
2      Fuller                  
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18838667
Check out this question I asked a while back... An assembly is expanded from the top to the bottom, so that all of its parts are listed and subassemblies are expanded to show their parts....

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22105395.html

It seems analagous since you're dealing with-- a Top level manager, his/her subodinates, their subordinates, etc.  

Is this headed in the right direction?
0
 

Author Comment

by:computerider
ID: 18838730
yes - i see this code:
**************************************************************************************************************************
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblMBIZ WHERE ID = '" & sID & "' OR ParentAssembly = '" & sID & "' ORDER BY ID")
    Do While rs.EOF = False
        If rs!ID <> sID Then
            ListAssemblies rs!ID, True
        Else
            Debug.Print rs!ID, rs!PN
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
End Sub
**********************************************************************************************************************
It loops until it hits EOF – how will it define EOF?

When it hits (example above) Andrew Fuller who has a null in reports to field? And thus starts with then next EMP ID? If so it could work…
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18838814
This actually works the opposite way...    So rather than answering the question "who is above me?",  it will give you a heirarchy from the top - down.  So entering "Andrew Fuller" would give you his subordinates, and their subordinates down to the lowest level.  Is that an acceptable alternative?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18839456
You also *can* get the chain of command from the bottom to the top.  The inherent problem with this approach is that if you expand this to include other factors (such as projects worked on) where the same employee can have different managers based on project, the chain of command becomes ambiguous.  This is why a bottom-up approach would not work in the assembly parts list example I posted (any part could appear on several different assemblies).  A top-down approach eliminates that ambiguity.

If there is no chance that a single employee can have multiple managers, this bottom up approach (untested air-code) should work...

Function MySuperiors(MeID as long) '*** this will list the chain of command above an employee, based on ID
Dim SubordID as long
Dim rs as DAO.recordset
Dim I as integer
Set rs = CurrentDB.OpenRecordset("tblEmployees", dbopendynaset)
subordID = MeID
Do Until NZ(rs!ManagerID,"") = ""   '*** loop until there is no manager)
      rs.MoveFirst  '**** start the search at the beginning of the recordset
      rs.FindFirst "EmployeeID = " & SubordID
      if rs.NoMatch then
         MsgBox "No matching record found"
         exit Do
      end if
      debug.print "Subordinate ID: " & subordID & "     Manager ID: " & rs!ManagerID
      SubordID = rs!ManagerID '*** this manager becomes the subordinate at the next level
      I = I +1
      if I > 50 then exit Do           '*** Guards against infinite loops
next
end function


     
0
 

Author Comment

by:computerider
ID: 18842411
mbizup - the above code looks like it - there is only 1 mgr per employee and no projects, etc so it is a 1 to 1 all the way to the ceo...

Now to produce a query where I can choose an individual from a combo box on a form and have a listbox show me who he reports to all the way up the ladder, do I build an sql query and attach it to the combo afterupdate event? If not how do I make this user available/friendly?

And do I need this line of code
************************************************************************
Set rs = CurrentDB.OpenRecordset("tblEmployees", dbopendynaset)
************************************************************************
If it used in a single database which has all my collection?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 61

Expert Comment

by:mbizup
ID: 18842620
>Now to produce a query where I can choose an individual from a combo box on a form....
Yes, you would use the after update event of the combo box to call a function similar to what I posted.  The code would have to be modified to add items to a listbox instead of printing the names in the debug window

>And do I need this line of code
Yes... That allows your table data to be manipulated in VBA

>If it used in a single database which has all my collection?
Can you elaborate on this by providing specific table and field names?  I'm guessing it is Something like this, but let me know the details specific to your application:

tblEmployees
-----------------
EmployeeID
EmployeeFullName
ManagerID

Also, if you haven't done this already, set up a combo box using the wizard, that displays the names employees, but returns the ID when one is selected.  This can be verified by adding this to the after update event of the combo:

MsgBox Me.cboEmployees

The message box should display a numeric ID when a selection is made.
0
 

Author Comment

by:computerider
ID: 18842678
"Yes, you would use the after update event of the combo box to call a function similar to what I posted.  The code would have to be modified to add items to a listbox instead of printing the names in the debug window"

I am not experienced enough to write the function code whole myself...your table/field description is accuarate...but I am in Access 2K and  I get 2 errors...DAO is not recognized (so I put RS as recordset) and then it doesn't accept FindFirst as a known method - and then wants a FOR at the end...can you give me a better idea how the code should be structured?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18842717
>DAO is not recognized
From the VBA editor's menu:
-Tools -> References
-Make sure that Microsoft DAO Version x.x is checked.
-If it is labeled as MISSING, uncheck it, and select the most recent version from the list
-Click OK to save and close the references window

Then use the original line I gave you...
Dim rs as DAO.recordset

Let me know if that cured that issue... I'll help you out with the code later on today.
0
 

Author Comment

by:computerider
ID: 18842959
Got that fixed now it only bombs at the last NEXT with a compile error of "Next without For"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18847806
Okay...

1. Add a listBox;  the row source is unimportant.  Call it lstManagers.
2. Add a combo box named cboEmployee,  like I described in my earler post to select an employee by ID.  Add the following code to that combo box's afterupdate event:

MySuperiors Me.Combo0, Me.lstManagers

3. Add a table:
 tblTempManagers
------------------------
ManagerID -- AutoNumber, Primary Key
ManagerName -- Text

This will hold the list of employee x's superiors (chain of command)

4.  Add the following code to a module (Save the module as modChainOfCommand)

Public Function MySuperiors(MeID As Long, lst As ListBox) '*** this will list the chain of command above an employee, based on ID
Dim SubordID As Long
Dim rs As DAO.Recordset
Dim I As Integer
Dim strManagerName As String

Set rs = CurrentDb.OpenRecordset("tblEmployees", dbOpenDynaset)
SubordID = MeID
CurrentDb.Execute "DELETE * From tblTempManagers" 'Clear temp table
lst.RowSource = ""               '** clear rowsource of listbox
lst.RowSourceType = "Table/Query"
'Add selected name as first entry in list
strManagerName = DLookup("FullName", "tblEmployees", "EmployeeID = " & MeID)
CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values('" & strManagerName & "')"
Do Until Nz(rs!ManagerID, "") = ""  '*** loop until there is no manager)
      rs.MoveFirst  '**** start the search at the beginning of the recordset
      rs.FindFirst "EmployeeID = " & SubordID
      If rs.NoMatch Then
         MsgBox "No matching record found"
         Exit Do
      End If
      If IsNull(rs!ManagerID) Then Exit Do
      strManagerName = DLookup("FullName", "tblEmployees", "EmployeeID = " & rs!ManagerID)
      'Add manager to temp table
      CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values('" & strManagerName & "')"
      SubordID = rs!ManagerID '*** this manager becomes the subordinate at the next level
      I = I + 1
      If I > 50 Then Exit Do           '*** Guards against infinite loops
Loop '***** (Changed 'next' to 'Loop')
lst.RowSource = "SELECT ManagerName FROM tblTempManagers ORDER BY ManagerID DESC"

rs.Close
Set rs = Nothing
End Function

0
 
LVL 61

Expert Comment

by:mbizup
ID: 18847810
Correction:
The code in the combo's after update event should be:
MySuperiors Me.cboEmployee, Me.lstManagers
0
 

Author Comment

by:computerider
ID: 18849712
Excellent!!!

This works now if I may ask a few questions so I understand what I have done before I close this question (no other board has understood my question but you apparently)

1.      Why the temp table? Is it because the results of the query are just to view, created dynamically and then discarded?
2.      I can rewrite the query to display titles and other related fields to display in the list box, right?
3.      and only 1 bug so far, if a manager’s name has a quote mark in  the name (example: O’Connor) it crashes here:

CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values('" & strManagerName & "')"

How do I rewrite the line above to allow for such cases?

Again, I salute you sir…I’ve been asking for months…
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 18850200
1.  (Why the temp table? ) Iterating through the recordset from the bottom level to the top gives you the chain of command in the wrong order.  A temp table with a sequential numeric ID field is a convenient way to store the chain of command.  When listbox's rowsource is set to the temp table, the order of the chain of command is corrected by reversing the sort order (in the rowsource: ORDER BY ManagerID DESC).

2.  (I can rewrite the query) Yes... you need to include the new fields in the INSERT statement to include them in the temp table, and also in the SELECT statement to include them in the listbox.

3. (and only 1 bug so far)  Try revising the code like this:

CurrentDb.Execute "INSERT INTO tblTempManagers (ManagerName) Values(" & chr(34) & strManagerName & chr(34) & ")"

3.5 (I salute you sir)  
thank you :-)   :-)     (but you are talking to a lady :-)
0
 

Author Comment

by:computerider
ID: 18850459
I thought when I wrote that "Wait - it may be a lady" but I got sidetracked!  What a great lessson for me to learn!!! I salute you, Ms.!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18850479
Glad to help :)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now