Solved

SubReport-TOC/Html-linked TOC

Posted on 2000-05-07
8
264 Views
Last Modified: 2012-08-14
Two questions with a common thread:
I have a simple report (done) that needs a simple Table of Contents (done, and in place as a subreport, but I've disassociated it for now, partly because I couldn't force a page break at its end...make that an added value item!).  The  TOC needs to pick up the page numbers from the generated report pages (per item of course).  I'm guessing the secret is in some code in the Print event of the report? ...  but do I need to bury any hooks in the underlying tables?

Then: I got "creative" and tried out the export-to-html function. Overall, not bad. BUT I need to build (this time, two levels of) TOC pages hyperlinked to the detail below.  Can I make Access do the work?  It's generating the pages and knows their titles, tho I could probably guess most of string (up to the sequence number).  What function could I put where to make this happen?

many thanks as usual!

Marilyn Justman

Ask for more points if it's trickier than I think!
0
Comment
Question by:mjustman
  • 6
  • 2
8 Comments
 
LVL 10

Accepted Solution

by:
paasky earned 150 total points
ID: 2796012
Hello Marilyn,

Here's some suggestions to your first question. The second seems much harder to solve and might require quite serious coding depending how complex is your report because it might not be solved using export-html-function but a customer function that would parse data to html pages.

In my sample example I've got a table "Articles" which has the following fields:

Article_ID   Autonumber (PK)
Topic        Text 200
Article      Memo
PageNum      Integer

After I added some test data into table I created a main report which has the following visible sections: Report Header, Page Header, Article Header, Detail, Article Footer and Page Footer.

In the report header there's cover page information and I also added there two pagebreak controls (later I added TOC subform there).

After this was done, I added the following code to the report:

' declare variable which is used to determine if article
' continues to next pages (when True, show label "(Continued...)"
Dim Continued As Boolean

Private Sub ArticleFooter_Format(Cancel As Integer, FormatCount As Integer)
    ' new article starts
    Continued = False
End Sub

Private Sub ArticleHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim Sql As String

    Continued = True
   
    ' update page numbers that are shown in TOC
    Sql = " UPDATE Articles SET PageNum = " & CStr(Me.Page) & _
          " WHERE Article_ID = " & CStr(Me![txtArticle_ID])
   
    CurrentDb.Execute Sql

End Sub

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
    Me![lblContinued].Visible = Continued
End Sub

Then I made the subform which data source is same as main report but has only detail section which shows topic and page number so it tooks like this:

Topic I             3
Topic II           15          
....

After I have added this subreport to my main report's Report Header section this was almost done. Now only I need to add a code that runs the report twice: first run defines the correct page numbers to TOC and the second time its shown correctly.

In my print button there is the following code:

Private Sub B_Print_Click()

    On Error Resume Next

    ' prepare report - this is not shown to user
    Application.Echo False
    DoCmd.OpenReport "Article", acViewPreview
    DoCmd.Close acReport, "Article"
    Application.Echo True
   
    ' now show the report
    DoCmd.OpenReport "Article", acViewPreview
   
End Sub

When I press the print button I get the report which has cover page, the next page is TOC (with page numbers) and articles starts from page 3.

I can send you my sample db if you like.

Hope this helps,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796023
If you don't want to add PageNum field to your source tables, you can easily do a (temporary) worktable which should contain grouping and page number fields and instead of updating it, you need to fill it on the first run and then join its fields to report tables with a query.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796045
Forgot to mention that I there's always page break before "Article section" - it will force next article always start from new page.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796096
After thinking more of this (I've been suffering flu few days now so my mind doesn't work as well as it could) I might have an idea how to create html TOC that has hyperlink links to articles:

You need a code that creates only TOC html page. It could work like this (here's pseudo-code):

1. Open file for output
2. Open Recordset "Articles"
3. Print the html page header tags to file
4. Loop all articles and write the following information to file: <A href="Article_Page" & Cstr(rs![pagenum] &) ".html>" & rs![Topic] & "</A><P>"
5. Write html footer tags
6. Close file and recordset

If you need any assistance with these, please let me know.

Regards,
Paasky
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:mjustman
ID: 2796532
Adjusted points from 100 to 150
0
 

Author Comment

by:mjustman
ID: 2796534
Paasky:

Many thanks!

I'm accepting your first "Comment" as answer, but anyone who reads this should read the following comments as well.

I haven't tried it yet--that is a volunteer project and I've got a "real" one cooking today.  Will try to comment after I've tried it.

Sure: I'd LOVE to see your model. If it'll zip down to 2 meg or less, please just email pkg to
mjustman@enteract.com
Otherwise, send correspondence there and we'll figure out a better way.

Marilyn Justman
0
 
LVL 10

Expert Comment

by:paasky
ID: 2796955
Enchantment...

Here's the code of form which saves report as HTML and creates TOC in HTML format:

' define here the html file path and name
' when using output-method, the name is usually
' <reportname>Page<pagenumber>.html
' the code will add <pagenumber>.html
Const PAGENAME = "Article"

Private Sub B_BuildHTML_Click()
Dim Outputfile As String
Dim rst As Recordset
Dim html As Recordset
Dim f As Integer

    On Error GoTo Build_Error

    Outputfile = InputBox("Enter HTML TOC file name:", "TOC file", "c:\TOC.html")
    If Outputfile = "" Then Exit Sub
   
    Set rst = CurrentDb.OpenRecordset("Articles")
   
    ' no articles?
    If (rst.BOF And rst.EOF) Then
        Set rst = Nothing
        Exit Sub
    End If
   
    f = FreeFile()
    Open Outputfile For Output As f
   
    ' write html header to file
    Set html = CurrentDb.OpenRecordset("select [data] from [HTML] where [SectionCode]=1 order by [RowID]")
    html.MoveFirst
    While Not html.EOF
        Print #f, html![Data]
        html.MoveNext
    Wend
   
    ' write toc and link to file
    With rst
        .MoveFirst
        While Not .EOF
            Print #f, "<A HREF=" & Chr(34) & PAGENAME & ![PageNum] & ".html" & Chr(34) & ">";
            Print #f, ![Topic];
            Print #f, "</A><P>"
            .MoveNext
        Wend
    End With
   
    Set rst = Nothing
   
    ' write html footer to file
    Set html = CurrentDb.OpenRecordset("select [data] from [HTML] where [SectionCode]=2 order by [RowID]")
    html.MoveFirst
    While Not html.EOF
        Print #f, html![Data]
        html.MoveNext
    Wend
   
    Set html = Nothing
    Close f
   
    MsgBox "TOC succesfully written to file " & Outputfile

Build_Exit:
    Exit Sub
   
Build_Error:
    MsgBox Err.Description
    Resume Build_Exit
   
End Sub

Private Sub B_SaveAsHtml_Click()
    DoCmd.OutputTo acOutputReport, "Article", acFormatHTML
End Sub

0
 
LVL 10

Expert Comment

by:paasky
ID: 2796961
Table HTML contains the following data:

RowID      Data      SectionCode
1      <HTML>      1
2      <HEAD>      1
3      <TITLE>Table Of Contents</TITLE>      1
4      </HEAD>      1
5      <BODY>      1
6      <H2>Table Of Contents</H2>      1
100      </BODY>      2
101      </HTML>      2

SectionCode defines which part of HTML document the data is, 1 = header, 2 = footer.

And this is an example of TOC.html created with code:

<HTML>
<HEAD>
<TITLE>Table Of Contents</TITLE>
</HEAD>
<BODY>
<H2>Table Of Contents</H2>
<A HREF="ArticlePage3.html">A Technical Introduction to XML (By Norman Walsh)</A><P>
<A HREF="ArticlePage13.html">Beyond HTML: XML and Automated Web Processing</A><P>
<A HREF="ArticlePage19.html">Exploring XML-RPC (Byte Magazine)</A><P>
<A HREF="ArticlePage20.html">Going from HTML to XML</A><P>
<A HREF="ArticlePage24.html">Introduction to XML (By Lars Marius Garshol)</A><P>
</BODY>
</HTML>

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

746 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

13 Experts available now in Live!

Get 1:1 Help Now