Table of Contents 2
By Richard Rost
2 years ago
Create TOC/Index in Access Report, Part 2 In this Microsoft Access tutorial, we'll tackle creating a dynamic Table of Contents, complete with group page numbers, to organize reports efficiently. This is Part 2 of 3. PrerequisitesLinksRecommended CoursesTable of Contents Series
Keywords TechHelp Access, Access Table of Contents, Report Index, Dynamic TOC Access, Group Page Numbers, Organize Reports Access, Access Report Tutorial, CurrentDB.Execute, Report Design Access, Subreport Creation, Page Break Control Access, Dynamic Report Generation Access, VBA Event Handling, Record Source Access, TOC Report Design, Custom Report Title Page Access.
Intro In this video, I continue showing you how to create a table of contents or index in your Microsoft Access database reports. We'll fix issues with duplicate records by automatically clearing the temp table using a button, design a dedicated Table of Contents report, and add it as a sub-report with page breaks and formatting inside your main report. I'll also demonstrate how opening and previewing the report affects data in the Table of Contents, and explain why generating page numbers correctly often involves running the report in multiple passes. This is part 2.Transcript This is part two of my TechHelp video series where I'm teaching you how to put a table of contents or an index in your Microsoft Access Database. If you haven't watched part one yet, go watch part one. You'll find a link down below. Go watch that, then come on back. We now resume our program already in progress.
Okay, so yesterday we finished up; we got these records in here because all we did was look at page one. And that's the problem with generating the report in this way, is because if we preview it, okay, now if I go to page two, I'll get page two records in there. Oh, but look, I got duplicates of page one because that ran a second time. There's me, there's me again, but there's page two. So, the first time this thing runs, we're going to have to have it delete all the records in the table and then go to the end of the document. So, let's see how we're going to work that into this. We've got some work to do. Let's use this button to do our dirty work.
You're going to need a button somewhere to open up this report. So whatever button you're going to use, this will be my open customer report button. Okay, right-click, Build Event, let's go into here now. Now, before we open the report, we're going to be CurrentDb.Execute "DELETE FROM TOCT". It's that simple. It deletes all the records from that table. Be careful with that command. Now we're ready to open up the report.
Alright, so OpenReport and generate the R, acViewPreview. Alright. It's got to be in preview mode. Alright. Save it. I always throw in a Debug Compile from time to time. Let's come back over here. Close that. I'm going to open it back up again and click.
Okay. Now, if you look at the table of contents right now, there's all the page one stuff again. So, we need to go to the last document, the last page of the document. Click on that, and that will take us to the last page of the document, and then open it up, and look at that. There's everybody in there. Since it jumped to the end of the document, it processed all those pages because it has to know where the last page is. Okay? So now we've got all this page data. Well, what do we do with it?
Well, we're going to make another report that represents this table of contents. We're going to stick it in as a sub-report at the beginning of this report. Alright, before the first page. But the problem is, we have to run through this first, go to the last page, close it, and then reopen it again for this information to update. Because if you add customers, add contacts, these page numbers are going to move around. So it's going to involve two passes.
You could do it by going back to the first page and then re-querying stuff. It's just easier to do it this way, alright? So here's what we're going to do. Let's make a Table of Contents report. And this one will be real simple. Create, Report Design. And this is why I had you do this first, because we've got to have some data in here to see what it's going to look like. It works better. It's just easier that way. Right. At their work. But it would be easier. So it's not the work.
The Record Source here to the Table of Contents table. Let's go to bring in the existing fields; we just need the description and the page number. Bring those over here. I'm going to delete the labels again; we don't need a page header. Put the description over here, and bring this bottom up, like that. I'm going to left-align the description, which it should be already, and I'm going to right-align the page number, like that. I'm going to open up the detail section, and this is one where I actually do like the alternating colors in here. I think it looks nice. I mean, you're in that page footer too. All right, let's save this. This will be my TOCR. Let's take a look at what this looks like. Print Preview, there it goes. That's not bad. Let's get rid of the borders. Right-click, Design, select, Format, Shape Outline, Transparent, save it, preview it. Okay, that looks good. That's a nice-looking table of contents right there.
And yeah, sometimes you have little dots going across. I think if you got this gray shading, you don't need the dots. The dots is a whole separate video, folks. Is it possible? Yeah, it's possible. It's a lot more work though. I like this better, to be honest.
Alright. So next, we're going to stick this as a sub-report inside the other report. Okay? So, right-click, Design View, or I click Design View, I'm going to put it in a report. Okay, and while we're at it, let's put a page number down here. I'm gonna grab one of these guys here. Is that "Drop Here"? Let's put it over here on the right, and let's open this up. Let's make this right-aligned. It gives me your properties. I'll call you "Page", and we'll say this equals "Page". Just like that, the page number.
And yeah, I- it just a margin. Then do all that, you know how to do all that stuff, right? I'm not gonna waste time with margins. Let's put in our sub-report in a report header. Remember, the page header goes on top of every page, but a report header, which normally you don't see, goes on top of the entire report. It displays once. You can use that for a title page, and we'll put our Table of Contents on page 2. Okay, so right-click. I'm going to turn on the Report Header Footer. Boom, there it is.
I'm going to leave it gray for now, although I'm going to change this later. I just so you can see what's going on here, uh... this is where you put a big, like, you know, Customer Contact Report, right? Let's make it big, uh... thirty-six point. Right. This would be like your big title page, right? Yes. And others, if you put your logo here, right on the screen capture, this one. But in here, paste it. All right, this is going on my page one.
Okay, now let's say page two. I want to be my Table of Contents. So I'm gonna still keep it in the report header. I'm gonna put a page break control here, because this is what I get. Now watch this. I can go to Print Preview. Okay, that's what I've got. Here's my report header. That's why I left it gray for you to see. And here's where the document starts. Well, you can throw a manual page break in here. And what's that? Go up here, find, where is it? Where are you? It's this guy. I don't use them that often. Alright, we're gonna stick it right there. So you're gonna get a page break there.
Okay, now below that, this is where we'll put our Table of Contents. So we're going to grab our Table of Contents report, click and drag, drop it right there. And you can delete the little label that comes in with it. Okay, and there's your Table of Contents. And you could put a label above it if you want. I know I just deleted the label, but you could put something up here that's like "Table of Contents." Right? Table of Contents. Make it look pretty. Format it, make it look, I don't know, 20 point, bold, whatever.
And then what you're probably going to want is another page break underneath this. Because you're going to want a Table of Contents on a page by itself. Or you might not, depends on what you want. The problem is, is this Table of Contents has to stay a static size and I'll explain why in a minute. So let's find another page break and drop you right there. I'm going to bring this right up against that.
Okay. Let's take a look at what we got. Print Preview. Okay, there's page one, there's page two, and there's page three. Now, page three is actually where the first customer is. But if you look at the Table of Contents data, it thinks I'm on page one. Because when this thing runs and it fills that table with data, it was on page one.
So, let's close this and let's open it up again. Okay, page one. Oh wait a minute, Table of Contents is blank. Why is it blank now? Let's see, page three, there's some data. Okay, why is this blank? Let's take a look at the table. What's going on here? Oh wait, okay, I'm only seeing page three data in there now. What's going on?
Well, I just closed it, reopened it. When you reopened it, what happened was the first line in here ran that deleted all the data from the table. Okay, so the table is blank. That's why the Table of Contents is blank. As soon as I open, let me do it again. Right now, when I click on that, at this moment, none of those other page headers have run, so the Table of Contents is blank. So if I go to it now, if I look at it, it's empty. As soon as I go to the next page, now all of these section headers ran and filled the table with their bit of data. And now it's correct because they're on page 3. See what's going on?
And if I go to page 4 and open up the table data, you can see there is now the rest of the data is in there. Alright, so what we have to do is we have to get this report to generate all of those pages first, then we got to come back and run it again to see all that data properly in the Table of Contents, and we'll get to that tomorrow.
So come on back tomorrow, folks! Same Bat Time, Same Bat Channel for part 3. Or, of course, if you remember, sign up right now; you can watch it right now. I'm getting ready to record it. It's gonna be fun. That's gonna be your TechHelp video for today. Oh, wait a minute, we're on part two. Part two, right. Come back tomorrow for part three, folks. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow.Quiz Q1. What issue occurs if you do not delete the records from the Table of Contents table before rerunning the report?
A. The report will not generate any data
B. Duplicate entries for previously viewed pages will appear
C. Only the last page will be shown
D. The report will freeze
Q2. What SQL command is used to clear the Table of Contents table before generating the report?
A. DROP TABLE TOCT
B. CLEAR TOCT
C. DELETE FROM TOCT
D. REMOVE FROM TOCT
Q3. Why is the report run in Print Preview mode?
A. It allows editing of the records directly
B. It enables the report to process all its pages for Table of Contents generation
C. It prevents users from editing the report
D. It automatically formats the report for web display
Q4. Which Access feature is used to insert the Table of Contents as part of your main report?
A. Linked Table
B. Sub-report
C. Calculated Field
D. Form Control
Q5. What is the purpose of the Report Header section in Access reports?
A. It displays content on every page
B. It is used for filtering report data
C. It displays content only once at the beginning of a report
D. It holds all group totals
Q6. Why is it necessary to run the main report twice when generating a Table of Contents?
A. The first run deletes all data, and the second run updates the table with new data
B. The report cannot update data during a single run
C. The page numbers are only calculated after the first complete run through all pages
D. The database requires a backup report
Q7. If the Table of Contents appears blank on the first page, what is the most likely cause?
A. The report fields are not configured
B. The first step deleted records and new data has not yet been added
C. The Table of Contents report is not saved
D. There are no pages in the report
Q8. Which section of the report should the Table of Contents sub-report be placed in to ensure it appears before detail data?
A. Page Footer
B. Detail Section
C. Report Header
D. Page Header
Q9. What is one suggested method to make the Table of Contents display on a page by itself?
A. Add a summary field after it
B. Insert a page break above and below it
C. Change its record source
D. Make the report landscape orientation
Q10. Why must the Table of Contents section remain a static size in the report?
A. To allow correct boundaries for the alternating colors
B. To keep the report compact
C. To ensure that page numbers do not shift unpredictably
D. To help access runtime compatibility
Q11. According to the video, what is a function of the alternating colors in the detail section of the Table of Contents?
A. They help improve readability
B. They are required for data sorting
C. They control record navigation
D. They link the sub-report to the main report
Q12. When editing the appearance of the Table of Contents report, which property is changed to remove visible borders?
A. Shape Outline set to Transparent
B. Background Color set to White
C. Font changed to Bold
D. Text Alignment set to Right
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-C; 7-B; 8-C; 9-B; 10-C; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone continues the process of creating a table of contents or an index in your Microsoft Access database. This is part two of the series, so if you have not yet worked through part one, I recommend starting there before you read this part.
Previously, we reached the point where the records for page one were included in the table of contents, but there is a limitation to this approach. If you preview the report and move to the second page, you will get the page two records, but you will also end up with duplicates from page one, since the code runs again. For instance, you might see the same entry repeated, along with the new ones from page two. To avoid this duplication, it is necessary to clear out the existing records from the table before generating the report each time. To accomplish this, the right approach is to have the process first delete all the records from the table and then proceed with generating the document.
To make this happen, you should associate the task with a button that opens your report. You might have a button labeled "Open Customer Report" or something similar. In the event code for this button, before you actually open the report, execute a command that deletes all records from your table of contents table. This is a simple, but powerful, step, so be careful with it. After clearing the table, you can then proceed to open the report in preview mode. It is a good habit to periodically compile your code as you make changes.
Now, when you generate the table of contents, you need to ensure you have processed all the pages in the document. Simply previewing the first page will not fill the table with all of the necessary data. You need to go to the last page of the document, at which point all the pages will be processed and the table will contain all the information you need.
With all of the page data collected, the next step is to create a separate report that will serve as the table of contents. The goal is to include this as a subreport at the beginning of your main report, before the first page. However, since the data in the table of contents depends on what is currently in the table, you will have to generate the report to get the correct data, close it, and then reopen it so the table of contents is properly updated. If you add or remove records, page numbers may shift, so this process will usually require two passes: one to generate page data, and a second to display it accurately.
To create the table of contents report, start by using Report Design and select the table of contents table as its record source. You really only need the description and the page number fields for this report. Place the description on the left, and align the page number on the right. Adjust the layout as desired; alternating row colors often look nice in a table of contents. Remove page headers and other unnecessary elements so the report is clean and straightforward. Save this as your table of contents report and check its appearance in Print Preview. You can remove any field borders if you prefer a cleaner look. Some people like to include dotted lines between entries and page numbers, but with alternating shading, this is often unnecessary.
The next objective is to bring the table of contents report into your main report as a subreport. Open the main report in Design View and add a page number control to the footer or wherever it fits your layout. Make sure to align it to your liking.
Now, focus on the report header section, which is only printed at the start of the report. This is where you can include a title page with the name of the report, a logo, or any other introductory content. For our purposes, let's make page two the table of contents. To do this, insert a page break after your title page elements in the report header. Below the page break, add the table of contents subreport by dragging the report into place. You can label this section "Table of Contents" and style it as needed. If you want the table of contents to be on its own page, you might add another page break below it.
When you preview the main report, you should see a title page, then the table of contents, followed by the report data starting on the next page. However, keep in mind the timing of when the page data is generated. If you preview the report and the table of contents appears empty, it's because the table has just been cleared and the new entries have not yet been recorded. Navigating through the pages will trigger the report code that fills the table, and at that point, the table of contents will display the correct information. If you open the table in datasheet view after visiting the later pages, you will see that all the necessary data has been gathered.
The key lesson here is that you need the report to generate and process all its pages so the table of contents table is fully populated. Afterward, you can close and reopen the report to view the table of contents properly, with accurate page numbers next to each entry.
We will continue this process in part three, where I will demonstrate how to bring all these pieces together for a complete and functional solution. If you're a member, you can access the next part immediately; otherwise, come back for the next installment. I hope you found this lesson useful.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Deleting records from the table of contents table before generating the report
Using VBA to execute DELETE FROM TOCT
Opening reports in preview mode
Navigating to the last page of a report to process all records
Creating a separate Table of Contents report
Setting the record source for the Table of Contents report
Adding description and page number fields to the TOC report
Aligning and formatting fields in the TOC report
Removing report item borders and formatting styles
Inserting the Table of Contents as a subreport
Adding a report header for a title page
Adding and configuring page numbers
Inserting manual page breaks in reports
Placing the Table of Contents on its own page
Formatting and labeling the Table of Contents section
Handling issues with empty or partial Table of Contents data
Understanding timing issues with report data population
Explaining the need for two passes to generate an accurate Table of Contents |
