Full Version: Table of Contents/ find what page a control is on
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
b_lyles
Feb 15 2008, 06:03 PM
Hello All, these forums have helped me out a lot in the past; thanks so much for that. What I have is a report with several subreports along with page breaks and other controls. By this I mean nothing is grouped. What I want to do is create a small table of contents that will dynamically change according to length of subreports. Let's say i have lblItem1 on page 1. I would then like to set its caption equal to the page that the textbox it's tracking is on. I figured I would just set the textbox to "=[Page]" but that doesn't operate properly. All I want is to find out what page a control is on and then set the label's caption to that page. It sounds easy, and maybe it is; I've wasted far too much time trying. I've looked at the Table of Contents solutions in Microsoft's Knowledgebase, but those don't seem to apply to me. I could be wrong. Sorry this is so long and thanks for any ideas you have.
kfield7
Feb 15 2008, 09:07 PM
Well, I can see a 3 options.

In both, create variables to track where you are when the report is formatted or printed.

Option 1. Print the report twice. the TOC page is updated by code from the variables. This assumes the pages are numbered the same on the second print as on the first (should be in most cases.)

Option 2. Create the TOC page as the LAST page of the report. the TOC page is updated by code from the variables.

Option 3. Make a separate report for the TOC pages. the TOC page is updated by code from the variables. You'd have to use global variables in a module for this option.

Instead of variables, you could also create a table to hold the values.

Edited by: kfield7 on Fri Feb 15 21:08:55 EST 2008.
b_lyles
Feb 18 2008, 05:29 PM
hi, thanks for the quick reply. I suppose it's time for me to show my ignorance. The only way I figured out to track my position in the report was to use the onpage event. i would say I'm most interested in door number 2 - having the TOC at the end of the report. I'm not sure how to go about this, so if you have any code snippets or links i would appreciate those very much. i feel like i can loop through the report just fine, but have no way to test if an object is on the current page. thanks again for your assistance.
kfield7
Feb 18 2008, 11:57 PM
I haven't done this, but on thinking this thru I would create a table

tblTOC
TOCcaption as text255
TOCpage as long

Say your control you're trying to catalogue is in the detail section, and it's named lblSomething.

in the on format or on print event
dim rs as dao.recordset
set rs=currentdb.openrecordset("tblTOC")
if me.page = me.pages then
rs.close
else
rs.add
rs.fields(0) = lblSomething.caption
rs.fields(1) = me.page
rs.update
endif

I'm not sure if this will create repeat occurrances of the caption, so create a totals query ("qryTOC") on tblTOC grouped on TOCCaption and FirstOfTOCPage.

The last page will contain a subreport with recordsource as qryTOC. Put that in the report footer or something like that.
kfield7
Feb 19 2008, 01:18 AM
Well I tried this, it's not working...
b_lyles
Feb 19 2008, 12:04 PM
Thanks again for getting back to me. i've been trying your ideas and thinking about other solutions i've found. They seem to be based around controls in the group header, which doesn't really fit my situation. I'd really like to be able to use "If txtTextBox.page = me.page then..." but of course that property doesn't exist. I started using labels or the .tag property because i could check if it was blank and then set it equal to the current page so that i could avoid the page number changing. if i put a text box on, say, page 3 with controlsource "=[Page]" it will show correctly on that page. but if i make a textbox with controlsource "=txtTextBox" on any other page (first or last) it will use the current page instead of the page the textbox is on. i could force the format or print to run twice by using "If me.page = me.pages..." or something similar. Either way, I haven't been able to actually implement this.
Another idea I tried was to loop through all the controls and see if a control was between two page breaks and just use my own counter. the problem here is that the report controls are organized by first to last added instead of top to bottom.
So then it gets more complicated. I looked at microsoft's solution: ACC2000: How to Create a Table of Contents but that doesn't seem to apply.
Same with the array-based method: Reports: Printing First and Last Page for a Group .
My need for this is far lower less than the effort I've put into this, but I thought I'd post a few ideas to see if someone with more experience could make it work or find a better way. It seems like many people would like to have a TOC with dynamic page numbering.
Anyways, thanks for any ideas you may have.
b_lyles
Feb 20 2008, 05:32 PM
I finally figured it out! The code I'll put here is ugly, but it does the job and I'm happy now! The code basically runs through all controls in the report during the open event. As it does so, it puts all the page breaks and textboxes into two arrays- one for their names and the other for their .top properties.
Once complete, it sorts the two arrays using the .top property. Finally, it increments by the page breaks and sets the TOC textboxes' controlsources equal to to the counter each time it finds one of the textboxes one wants to track.
Totally lost now? I'll post the code! Please note that this could be cleaned up quite a bit and someone else will probably find a much better way to do this.
CODE
Private Sub Report_Open(Cancel As Integer)

'intitialize page_break_name variable
ReDim pgbrname(0) As Variant 'would be better to use Dim pgbrname() but can't with this design
'initialize page_break_top variable - tracks the top property
ReDim pgbrtop(0) As Variant
'also could use multidimension array
Dim p As Integer
p = 0
Dim r As Report
Set r = Me
For i = 0 To r.Count - 1
    If r(i).ControlType = acPageBreak Or r(i).ControlType = acTextBox Then
        If p > 0 Then
        ReDim Preserve pgbrname(p)
        ReDim Preserve pgbrtop(p)
        End If
        pgbrname(p) = r(i).Name
        pgbrtop(p) = r(i).Top
        p = p + 1
    End If
Next

'this bubble sorts 'em. taken and modified from:
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=103
Dim First           As Integer
Dim Last            As Integer
Dim j               As Integer
Dim Temp            As Integer 'string
Dim List            As String
Dim strTemp         As String
First = LBound(pgbrtop)
Last = UBound(pgbrtop)
For i = First To Last
    For j = i + 1 To Last
        If pgbrtop(i) > pgbrtop(j) Then
            Temp = pgbrtop(j)
            pgbrtop(j) = pgbrtop(i)
            pgbrtop(i) = Temp
            strTemp = pgbrname(j)
            pgbrname(j) = pgbrname(i)
            pgbrname(i) = strTemp
        End If
    Next j
Next i

'this picks out the controls you want to track - in this case 3 textboxes.
'a counter increments each time it sees a pagebreak control.
'if a tracked textobject is found, the controlsource of the table of contents (TOC)
'textbox tracking it is updated.
Dim c As Integer
c = 1
For i = 0 To UBound(pgbrtop)
    If r(pgbrname(i)).ControlType = acPageBreak Then
        'List = List & vbCrLf & pgbrtop(i) & ":" & pgbrname(i) 'used only for double checking data
        c = c + 1
    Else
        If pgbrname(i) = "txtTrack1" Then
            r("txtTOC1").ControlSource = "=" & c
        'List = List & vbCrLf & pgbrtop(i) & ":" & pgbrname(i) 'used only for double checking data
        End If
        If pgbrname(i) = "txtTrack2" Then
            r("txtTOC2").ControlSource = "=" & c
        'List = List & vbCrLf & pgbrtop(i) & ":" & pgbrname(i) 'used only for double checking data
        End If
        If pgbrname(i) = "txtTrack3" Then
            r("txtTOC3").ControlSource = "=" & c
            'List = List & vbCrLf & pgbrtop(i) & ":" & pgbrname(i) 'used only for double checking data
        End If
    End If
Next
'MsgBox List 'used only for double checking data
End Sub
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.