Report with Dynamic TOC in ADP file

  • Thread starter Thread starter Drew
  • Start date Start date
D

Drew

I would like to know if anyone has successfully built a dynamic table of
contents in Access using an ADP file. I saw this,
http://support.microsoft.com/?kbid=210269, but at the top it says, "This
article applies only to a Microsoft Access database (.mdb).", so I wasn't
sure. I need to make one but I'm not sure how to go about doing it.

Thanks,
Drew Laing
 
You can easily set TOC report's RecordSource to a Stored Procedure in the
SQL Server/MSDE database, which returns a recordset containing TOC data. You
can do whatever you want to do in the SP to get TOC data.
 
The KB article uses DAO and a local table in the MDB to store the data, so
it would require significant modification to work with an ADP.

If you use a table in the SQL Server database, you'll want to change the DAO
code to use ADO instead, and you'll also have to deal with potential
multi-user issues - the code replaces all data in the TOC table each time it
is run, and you don't want two users doing that at the same time. You'd
either need to lock the table while it is in use, or add a field to identify
which records belong to which user, and modify the code to replace only the
current user's records.

Alternatively, you could use a local MDB to store the TOC table. Add a
reference to the DAO object library to the ADP, and then you could use the
code from the KB almost as is, except you'd have to use OpenDatabase instead
of CurrentDb to get a reference to the MDB file containing the TOC table.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Has anyone done this to a report in ADP? I don't quite understand how it
gets the page number from the report. Any explanations would be great!

Thanks,
Drew Laing
 
At the top of the code in the KB article, a variable 'intPageCounter' is
declared. This is set to 1 in the Open event of the report, then incremented
in the UpdatePageNumber function, which is called from the OnPrint event
property of the Page Footer section. (I found the UpdatePageNumber function
a little difficult to find myself at first, because there is no space
between it and the end of the previous 'UpdateToc' function in the KB
article). The UpdateToc function is responsible for writing the value of the
intPageCounter variable to the Page Number field in the TOC table. The
UpdateToc function is called from the OnPrint event property of the section
containing the data you want to use as your TOC entry (the CategoryName
Group Header section in the example).

In summary: When the first report opens, it first deletes any records (from
previous runs) from the TOC table. Then as this first report runs, as it
starts each new group (category name in the example) it writes the name of
the group and the current page number to the TOC table. The second report
(the TOC) simply displays the data that the first report recorded in the TOC
table.

It is this deleting of previous records from the TOC table that is the main
complication when attempting to adapt this for an ADP - you need to ensure
that the records you delete are records from this user's previous run of the
report, not some other user's work in progress. In the MDB, this could be
solved by using a local table. In an ADP, there are no local tables, so you
need to either add a field to the TOC table that you can use as a flag to
ensure that you select only this user's records, or use a local MDB.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
I understand alot better than before! Thanks for spending the time to make
a writeup like that. I think I will try locking the SQL Server table first
and see if that works.

Thanks,
Drew Laing
 
Back
Top