VB Code for Record Sets Needed

[foxhound]

Hello,

Can anyone share with me how to set up the following in vb code?

Rs = Select Distinct Super from Snapshot
Rs.movefirst
Do while not rs.eof
Rs1 = select all from Snapshot where super equal rs!empnum or empnum equal rs!super
Rs2 = select all from Snapshot where super equal rs1!empnum or empnum equal rs1!super
Rs3 = select all from Snapshot where super equal rs2!empnum or empnum equal rs2!super
Rs4 = select all from Snapshot where super equal rs3!empnum or empnum equal rs3!super
Rs5 = select all from Snapshot where super equal rs4!empnum or empnum equal rs4!super
Rs6 = select all from Snapshot where super equal rs5!empnum or empnum equal rs5!super
Rs7 = select all from Snapshot where super equal rs6!empnum or empnum equal rs6!super
Rs8 = union rs1, rs2, rs3, rs4, rs5, rs6, rs7, sort ascending by empname
Rs.movenext
Loop

Thank you in advance for your help!

Foxhound

 

[Dugantrain]

Can you clarify what exactly you're trying to do? I'm looking through your instructions and your procedure seems overly complex; surely there's an easier way to accomplish what you're looking to do.

 

[foxhound]

It is very complex for me. I hope there is an easier way! I have a table (Snapshot) with a list of employees. This list has a field called empnum, empname, super, and details. I want to create a list of unique super records, go to the first record in this list and pull a list of employees (empname) that report to that super. This is like a combo box that looks up records based upon the selected record in the combo box. I need to have this loop through each level of subordinates (up to 6 levels of management). Once completed, the code will be adapted to create an Excel Workbook for each unique super that includes a separate worksheet in it for each level of super’s that subsequently fall under that unique super.

 

[Dugantrain]

Wow. OK, the bad news is that I don't immediately have a solution for you. However, I can give you a few tips:
-The big problem that you're looking at here is Recursive Relationships, which basically means that the data in one table is pointing at itself. Your case is especially difficult because you don't know ahead of time how many levels deep your data is going to relate itself.
-I may have lied when I told you that there's an easier way to do this, but I can assure you that there is a better way of doing this. You almost certainly want to use pre-defined queries to do this. Even if you were to get this thing working via code, your performance would be a big ole naughty mess of doo doo. Plus, VBA Recordsets don't clean up after themselves and, if you're looking at a lot of data, you're going to be looking at a lot of db 'bloating' (adding to my doo doo theory).
-Go to http://www.access-programmers.co.uk/forums and have a Search on 'Recursive'. There are more examples than what you'll find here and it may get you farther along.

 

[foxhound]

Too bad there isn't an easy solution. Thanks for the tips!

 

[mdmilner]

I followed dugantrains' link and was looking at this:

http://www.access-programmers.co.uk...d83e0689d7&threadid=56116&highlight=recursive

4th Post down purports to being a 2 query method to break it down.
-
While I've never read anything explictly written to handle recursive searches, until today, most of my attempts have been based on creating some method to sort my data first...aka, in this case, the queries are re-writing the data into a form that allows you to easily extract your info.

Good luck. Nice link.

Mike