Knowing your way around your Access project is important for any developer. In this article, Christopher Weber demonstrates his recursive query and the reporting techniques he uses to generate a tree navigation map of a database. Along the way, he builds a reusable module that uses Access graphics to draw hierarchies in reports.

In my article last month, I introduced you to my Navigation Mapping technique and explained how the code behind zsfrmNavigationMap recursively filled zstblNavigationMap by analyzing coded OpenForm and OpenReport VBA calls in an Access database. In this second part of the series, I’ll show you how I recursively query zstblNavigationMap to return an unformatted tree structure of the navigation routes in the database, and then report those findings using dynamically formatted lines to link the nodes of the pared navigation tree together. We’ll continue with our simple Part 2 NavigationMapper.mdb.

A self join

The query that lies behind my reporting technique is a self join query based solely on the table zstblNavigationMap. I illustrated how this table was loaded with information about your project in last month’s issue. This query joins the table to itself eight times using a left outer join between the ObjCalled column in the left table and the CallingObj column in the right table (see Figure 1). This join reflects the links found database among forms and reports by the zsfrmNavigationMap routine.

 Figure 1

The SELECT statement for the query reveals that zstblNavigationMap has been aliased eight times as Map1, Map2, Map3, and so on. There are nine output fields each named fld1, fld2, and so forth. The numbers don’t directly correspond to the numbered instances of the source table. Instead, fld1 and fld2 are from table Map1 fields CallingObj and ObjCalled. The remaining seven fields are from the remaining seven instances of zstblNavigationMap:

SELECT Map1.CallingObj AS fld1,

Map1.ObjCalled AS fld2, Map2.ObjCalled AS fld3,

Map3.ObjCalled AS fld4, Map4.ObjCalled AS fld5,

Map5.ObjCalled AS fld6, Map6.ObjCalled AS fld7,

Map7.ObjCalled AS fld8, Map8.ObjCalled AS fld9

FROM ((((((

zstblNavigationMap AS Map1

LEFT JOIN zstblNavigationMap AS Map2

ON Map1.ObjCalled = Map2.CallingObj)

LEFT JOIN zstblNavigationMap AS Map3

ON Map2.ObjCalled = Map3.CallingObj)

LEFT JOIN zstblNavigationMap AS Map4

ON Map3.ObjCalled = Map4.CallingObj)

LEFT JOIN zstblNavigationMap AS Map5

ON Map4.ObjCalled = Map5.CallingObj)

LEFT JOIN zstblNavigationMap AS Map6

ON Map5.ObjCalled = Map6.CallingObj)

LEFT JOIN zstblNavigationMap AS Map7

ON Map6.ObjCalled = Map7.CallingObj)

LEFT JOIN zstblNavigationMap AS Map8

ON Map7.ObjCalled = Map8.CallingObj

WHERE (((Map1.Level)=1)

AND ((Map2.Level)=2 Or (Map2.Level) Is Null)

AND ((Map3.Level)=3 Or (Map3.Level) Is Null)

AND ((Map4.Level)=4 Or (Map4.Level) Is Null)

AND ((Map5.Level)=5 Or (Map5.Level) Is Null)

AND ((Map6.Level)=6 Or (Map6.Level) Is Null)

AND ((Map7.Level)=7 Or (Map7.Level) Is Null)

AND ((Map8.Level)=8 Or (Map8.Level) Is Null))

ORDER BY Map1.CallingObj, Map1.ObjCalled,

Map2.ObjCalled, Map3.ObjCalled,

Map4.ObjCalled, Map5.ObjCalled,

Map6.ObjCalled, Map7.ObjCalled,

Map8.ObjCalled;

The WHERE clause of the query limits the output to rows where the corresponding value in the Level field is equal to the value used to alias the table (or is Null). Thus, rows with values from Map1 have a Level value of 1, rows with values from Map2 have a Level of 2, and so on. The second Is Null condition for each output field ensures that corresponding rows are returned from each table regardless of whether the joined fields return values. The output from the query can be seen in Figure 2. Notice, in this simple example, that there are only values for fld1 through fld3 because the navigation tree is only three levels deep. The remaining six instances of zstblNavigationMap (Map3 through Map8) return no values whose Level lies in the range 3 to 8 (in other words, each column returns Null).

 Figure 2

As designed, the query accommodates a hierarchy eight levels deep and no more. If your database has a navigation tree that runs deeper than this, you could add more instances of zstblNavigationMap to the query (and the accompanying report), or give serious consideration as to whether you should rethink your navigation scheme. I doubt whether there are many users who can easily navigate a system more than four or five levels deep.

Just viewing the datasheet from zsqryNavigationMap, you can imagine a report that shows just the first instance of each value in each column, and the subsequent first instances of each value in following columns. In fact, that output is simple to create using the report wizard and turning on the Hide Duplicates property for each column. When I first printed the report, I immediately grabbed a ruler and connected the parent values in each column to their child entries to the right. Voilà!—a navigation tree. But, being the anal-retentive programmer type, I wanted my report to draw the lines for me, and that’s where the fun began.

Looking good…

I wanted to have lines linking each Calling Object to each Object Called, much like a tree view control (but without using any third-party ActiveX controls). The first five columns of the desired output are shown in Figure 3. As required, duplicate instances of the same calling object are hidden and subsequent child nodes are linked by appropriate lines.

Unfortunately, those lines are quite tricky to produce. There are five patterns that must be used in the map. The lines, when they appear, can have four different patterns linking the calling object on the left to the object being called on the right. The possible links can be seen in Figure 4. They are:

• At the beginning of a child group
• At the end of a child group
• In the middle of a child group
• For a single child link (group of 1)

There’s a fifth pattern: no lines at all are required when the calling object has no child links.

 
Figure 4

I could have used four overlaid bitmaps in the report and set the visibility of the proper image as needed, but I opted instead to use three line controls and “reconstitute” each segment of the pattern as needed. In the report I have a set of three lines just to the left of each column’s text box (as shown in the blowup view in Figure 5). Horizontal lines are named after the text box on their right. For example, lin2 links txt1 to txt2. Vertical lines are named after the text box on their right as well, so lin2Up and lin2Dn are just to the left of txt2.

Figure 5

In order to control when each line segment should appear, the report uses code that follows a rule-based approach. The horizontal lines’ visibility is controlled by these rules:

• Don’t print a horizontal line to an empty (Null or hidden) control.
• Only print a horizontal line if there’s no previous instance of the same value on the left with this same value on the right along the same path.

Down lines appear following this rule:

• Only print a line down if there’s a different value following in the current column with the same value to the left along this same path.

Up lines may be visible under several conditions, but there must always be a previous instance of the same left value to the left:

• If a Down line shows and a previous instance of the same value to the left appears along the same path, the Up line should also be visible.

or:

• If the previous Up condition is not met, make the Up line visible if there are:
• no other unique values at this level on this path; and
• there has been a previous different value in this column; and
• the current value hasn’t been seen previously (in other words, this is the last child link for the parent to the left).

Believe it or not, it took quite a while to distill these rules and then write the code that applies them. The routine that draws the lines is called in the report’s Detail_Print event and uses a copy of the report’s recordset (as no recordset clone is available for reports). The recordset is created in the Report_Open event and destroyed in the Report_Close event and is referred to by a module-level variable called rst. The routine also uses a module-level constant MAX_LEVELS that equals the number of columns accommodated by our report. This code sets up the module-level declarations:

Option Compare Database

Option Explicit

Const MAX_LEVELS = 9

Dim rst As Recordset

The report also uses a line counter, a text box called txtLineCounter (shown in Figure 5 just to the left of txt1) whose Visible property is set to False. Its control source is =1 and its Running Sum property is set to Over All. This text box effectively numbers each row of output invisibly. The text box’s purpose is to align the module-level recordset with the report’s RecordSource in order to test the conditions that determine the appearance of the lines for each column.

A For loop sets the visibility of the eight groups of lines on the report using integer i, running from 2 to MAX_LEVELS. In each iteration, integer ThisRow is set to txtLineCounter minus 1 and the recordset is aligned using the recordset’s AbsolutePosition property:

Private Sub Detail_Print(Cancel As Integer, _

PrintCount As Integer)

On Error GoTo ErrorHandler

Dim i As Integer, strSearchPathCriterion As String, _

ThisRow As Integer



For i = 2 To MAX_LEVELS

With rst

ThisRow = txtLineCounter - 1

.AbsolutePosition = ThisRow

Now that I’m looking at the row prior to the current row on the report, I set the value of the variable strSearchPathCriterion using my procedure GetSearchPathCriterion. This routine is passed i (the column number) and rst (the recordset for the report). This function creates a WHERE search condition for the recordset’s Find methods that describes each value in each previous column for the current path. In the first row of my example ( Figure 2), if column 3 was being processed, it would return “fld1=’Switchboard’ AND fld2=’frmCustomers’ AND “, which would then have “fld3=’frmCustomerHistory'” added to the end of it.

Function GetSearchPathCriterion(ByVal i As Integer, _

rst As Recordset) As String

On Error GoTo ErrorHandler

Dim PathedCriterion As String

Dim j As Integer



For j = 1 To i - 1

PathedCriterion = "(fld" & CStr(i - j) & _

"='" & rst.Fields("fld" & _

CStr(i - j)) & "') AND " & PathedCriterion

Next j

GetSearchPathCriterion = PathedCriterion



Exit_Here:

Exit Function



ErrorHandler:

MsgBox "Error #" & Err.Number & ": " & _

Err.Description & " by " & Err.Source, _

vbOKOnly, _

"Error in procedure GetSearchPathCriterion"

Resume Exit_Here



End Function

With this WHERE clause in hand I can proceed. If fldi is Null, lini has its visible property set to False, as I don’t want to draw links to blank text boxes. Otherwise, if no previous instance of the current value can be found (NoMatch is True), the line is shown. Alternately, the Hide Duplicates setting makes the text box invisible, then so, too, should be the horizontal line leading to it. It’s unfortunate that there’s no way to determine whether the text box is using its Hide Duplicates setting to make itself invisible. If there were, I could have just made the horizontal line’s visibility the same as the text box’s.

strSearchPathCriterion = GetSearchPathCriterion(i, rst)

If IsNull(.Fields("fld" & CStr(i))) Then

Me("lin" & i).Visible = False

Else

.FindPrevious strSearchPathCriterion & _

"(fld" & CStr(i) & "='" & _

.Fields("fld" & CStr(i)) & "')"

Me("lin" & i).Visible = .NoMatch

End If

To determine the visibility of the Down line for the current column in my For loop, I must first realign the recordset with the previous row as it may have moved during the FindPrevious search. Using strSearchPathCriterion again, I now search along the current path for a following value NOT equal to the present column’s value. The FindNext method and its NoMatch value do the job of setting the Down line’s visibility.

.AbsolutePosition = ThisRow

.FindNext strSearchPathCriterion & "fld" & CStr(i) & _

" <> '" & .Fields("fld" & CStr(i)) & "'"

Me("lin" & i & "Dn").Visible = Not .NoMatch

Lastly, I must deal with the Up line for the current column. It can only be visible if there’s a previous instance of the value to the left along the same path. Again I use strSeachPathCriterion with the last ” AND ” removed. If no match is found, the Up line is suppressed and I’m done. Instead of nesting a series of If…Then statements, I thought it was clearer to simply bail out with a GoTo statement that takes me to the Done label. This is the way the rule base “thinks” about it so I made the code do the same:

Dim fPreviousLeftFound As Boolean

.AbsolutePosition = ThisRow

.FindPrevious Left(strSearchPathCriterion, _

Len(strSearchPathCriterion) - 5)

fPreviousLeftFound = Not .NoMatch

If Not fPreviousLeftFound Then

Me("lin" & i & "Up").Visible = False

GoTo Done

End If

If I haven’t bailed out, a previous instance of the left value has been found along the same path. If the Down line is already showing, then I must have an Up line to accompany it. I set its visibility to True and go to Done.

If Me("lin" & i & "Dn").Visible Then

Me("lin" & i & "Up").Visible = True

GoTo Done

End If

If I’ve gotten this far, the Up line should only remain visible if there are:

• no other unique values following this row along the same path; and
• there has been a previous different value; and
• this current value hasn’t been seen previously.

I declared three new Boolean variables to record these conditions: fLastUniqueValueOnThisPath, fThisValueNOTSeenHereBefore, and fPreviousDiffValueFound. I use appropriate criteria with the Find method of the recordset to determine whether all three conditions are met. If they’re all true, the Up line’s Visible is set to True:

Dim fLastUniqueValueOnThisPath As Boolean

Dim fPreviousDiffValueFound As Boolean

Dim fThisValueNOTSeenHereBefore As Boolean



.AbsolutePosition = ThisRow

.FindNext strSearchPathCriterion & _

"(fld" & CStr(i) & " > '" & _

.Fields("fld" & CStr(i)) & "')"

fLastUniqueValueOnThisPath = .NoMatch

.AbsolutePosition = ThisRow

.FindPrevious strSearchPathCriterion & _

"(fld" & CStr(i) & " < '" & _

.Fields("fld" & CStr(i)) & "')"

fPreviousDiffValueFound = Not .NoMatch

.AbsolutePosition = ThisRow

.FindPrevious strSearchPathCriterion & _

"(fld" & CStr(i) & " = '" & _

.Fields("fld" & CStr(i)) & "')"

fThisValueNOTSeenHereBefore = .NoMatch

Me("lin" & i & "Up").Visible = _

fLastUniqueValueOnThisPath And _

fPreviousDiffValueFound And _

fThisValueNOTSeenHereBefore

Done:

End With

Next i

Is it reusable?

If you’ve read this far you may be asking yourself, “Why did I read this?” or “Do I need to know this to use our Navigation Mapper objects?” The answer to the second question is simple: No. Just import the hidden Navigation Mapper objects from this month’s .mdb file into your database and follow the instructions in my previous article. The Report button on zsfrmNavigationMapper will output a navigation tree for you to print. I’ve kept the RecordSource for zsrptNavigationMapper as a separate query so that its layout is easier to see and manage. You could also embed the SQL statement into the Report’s RecordSource property.

The first question (“Why did I read this?”) is a bit trickier. Notice that the code for setting the line visibility is only dependent upon the names of the controls on the report. Thus, you could use any self join data displayed in aliased columns (as I did in zsqryNavigationMapper) to create a report like zsrptNavigationMapper. If you name the column text boxes generically as I did and copy the lines and position them next to their corresponding text boxes on the right, this code should continue to work just fine.

So, get out that genealogy database you’ve been working on (you do keep all of the entries in a single table with father/mother links, don’t you?) and build a nice branching diagram of your family tree. Oh, one more thing… if it loops back on itself across generations, don’t call me—I don’t even want to know.