Table of Contents 3
By Richard Rost
2 years ago
Create TOC/Index in Access Report, Part 3
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 3 of 3.
Members
Members will learn how to make the Table of Contents dynamic in size, so the page numbers will stay accurate regardless of the size of the TOC.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Update
- Platinum Member Matt Hall recommended something I never thought of: "To stop the flicker, you might set the report to acHidden for all but the last pass." Brilliant! Thanks, Matt.
Recommended Courses
Table of Contents Series
Keywords
TechHelp Access, Generate Table of Contents, Access Report Index, Dynamic TOC, Group Page Numbers, VBA Report Navigation, SendKeys Command, TempVars in Access, Debug Compile VBA, ACCDE File, VBA DoEvents, Report Footer Index, Sort List by Name, Tech Help Video.
Intro In this video, we continue building a Table of Contents in a Microsoft Access report by adding the ability to programmatically navigate to the last page using VBA and SendKeys, control report processing with TempVars and DoEvents, and improve the report's appearance with additional formatting changes. You will also see how deleting or updating records affects the Table of Contents, and learn some practical tips for handling complex report behaviors. This is part 3.Transcript This is part 3 of my Table of Contents series where I'm teaching you how to put a Table of Contents in a Microsoft Access Report. If you haven't watched Parts 1 & 2 yet, what are you doing here? Go watch Parts 1 & 2 first and come on back. Alright, so in Part 2, we got to this point where we have the page header here. We got the, excuse me, the Report Header, we got the title page without a blank Table of Contents now because it gets deleted the first time it runs through, and then we got our data.
So what we have to do is we have to open up this report once, go to the last page, then close it and re-open it again but tell the database not to mess with the page numbers the second time. Right now, we can do that. Well, let's go back up here. Let's tackle the part about going to the last page first. Now, research as I did, I spent a little while Googling this with the Google machine, there is no way with VBA to open a report and go to the last page. Like with a form, you can open it up and move through the records. There is a GoToRecord command, doesn't work with reports in Print Preview mode. So Sandy, and that's a list for the Access team, that you should be able to open up a report and programmatically go to any page. You can't do it, so we have to rely on our old friend SendKeys. Yes, yes, yes, I know, I'm not a fan of SendKeys. There are some things you just have to use it for, and there is no other programmatic way to do.
So we're going to have to use SendKeys. If you've never used SendKeys before, don't worry, it's really easy. You can go watch this video if you want to. It's basically it sends keys as if they were typed on the keyboard, as if you were sitting there and you hit it yourself. And I try not to use SendKeys for anything that's automated but in this particular case, we kind of need it. So what we're going to do is after we open the report, we're going to say SendKeys. Now the way that you can get to the last page of a report is if you, the user, hit the End key on your keyboard. All right, but we're going to make Access do that for us. So, inside of quotes, inside of curly braces, put the word END, just like that, comma true, and that just tells Access to wait until that's finished processing, which is necessary if you got multiple keys, but that's just one key, so it should work. In other words, open the report and immediately hit the End key, and that will move to the last page of the report. I know it's cheesy, but it works.
Don't try doing this in anything that's automated, by the way. If you've got some kind of like nightly event that runs and maybe prints out this report for you, no. Make sure this is in a button you click yourself because SendKeys is not reliable. One out of 50 tries, alright. Save it, debug, compile, ready, hit the button, boom, and you're automatically on the last page. Now, the benefit of that is if you open up the Table of Contents, all the data is in there now, see? So now we got all the data in our Table and that's just fine.
So now what we can do is close this and open the report, don't change the Table of Contents stuff, leave it because we already generated it. How are we going to do that, well we can use our good friend TempVars. And right now I know Adam is somewhere clapping. So what we're gonna do is in here we're gonna say TempVars, make some kind of a variable, we'll call it MakeTOC, and we'll say that's equal to True. Okay, now in the report, let's go to the report, where's the Customer Contact Report, here it is, in here we're going to say if TempVars("MakeTOC") equals true then do this stuff, otherwise don't mess with it, don't insert the stuff again, we've already inserted it, okay.
So now back to my button, now that we've done that, now we can open the report and display it. Display... Okay, we got to close it first, all right, so we're going to do Close, ACReport, Customer Contact R, ACSave Yes. I put ACSaveYes in there all the time because, and this is for you, the developer, because you don't want to make some design changes, run your VBA code or macro, and it closes the object, and it doesn't save it. And your end-users won't have access to make design changes because they are using an ACCDE file. So don't worry about that. So, I always use ACSaveYes, okay. And now that we've run it through once, closed it, now we can open it a second time. I'm gonna copy this, right? Paste that here, this should actually say close and reopen the report. All right, now I'm going to say MakeTOC is false, and open the report again, okay.
So what are we going to do? We're going to set the TempVars("MakeTOC") to true, open the report, it's going to generate the Table of Contents, close it, and then reopen it, and tell it not to regenerate that Table of Contents the second time. Now, one other thing we're going to put in here, you need a DoEvents right there. DoEvents just lets Access process background stuff, and I messed with this for about a half an hour before I realized that a DoEvents fixes the problem, and I'm not going to make you walk through it all. Just, you need a DoEvents there, or if you have my Status function, for those of you who have been following my videos for any length of time, you know I've got this box here called a Status Box where I can display information. My Status function has a DoEvents in it right there. So what you can do is you can, instead of putting it's DoEvents by itself here, you can put some status in here. Like you can do Status("Generating TOC") like that. So it shows the user what's going on. And then here, you can do, before you close it, you can do Status("Opening Report") like that. So it just looks like something's going on, and then down here, you can say Status("Done"). But the status has the DoEvents in it, so that takes care of that problem.
Okay, alright, save it, close it, debug, compile, of course, come back out. We're gonna close everything down, open my main menu back up, and let's click the button. Boom. And you'll see it show up once and then disappear, and then reopen it again, which is what we wanted. Now, page two, let's look at this. Table of Contents, let me make this bigger so you can see everything. Oh, look at that. Three, four, five, oh. Oh, that looked nice. And it starts on page three. See? Isn't that special?
And the last one, Peregrine Took, Fool of a Took, is on page 9. Let's make sure that's correct. Come over here, page 9. Let's get rid of that gray background and the border around this. A couple of little more formatting things here. I just wanted this to be on here to show you what it looks like at first. Let's get rid of that background color. Let's make it white. Let's get rid of this border format, shape outline, transparent, save it, close it, open it. Okay, looks good. Table of Contents looks good.
Now if we did have to make a major update to our data here, let's say for example, let's say we lost a bunch of customers. Let me delete these customers here. Oh, we got order two, we got related records. Okay, I'll see them. All right, it deleted a few of them. So let's see if it changed where Peregrine Took is. Let's make sure. Okay, come over here. And Peregrine Took is on page seven now. See how it updated? Okay, if we go to the end, let's make sure. Oh, yeah, blank page eight, that sometimes happens. Let's see, page seven, there you are, Peregrine Took. Beautiful, and there you go.
That's it, I want to say that's it. It's not like that was all. It's not like it's simple to do this, but you follow along with me and I'll show you stuff. Now there's one problem that remains, and that is unfortunately the way this is set up, the Table of Contents can only be one page in size. If it spills onto a second page, all the other page numbers are going to be wrong because when this opens up the first time, this comes in as a blank page. We just saw it earlier, right? And so the report calculates all these page numbers based on that first page only being one page long. So if your Table of Contents is going to spill over onto two pages, you have to dynamically update these page numbers, and that's a little bit trickier, but I will show you how to do that in the extended cut for the members. Silver members and up get access to all of my extended cut videos and of course, Gold members can download these databases and you have access to the Code Vault.
Now, one way to get around this limitation is, instead of putting the Table of Contents in the page, or excuse me, in the Report Header, you could put it in the Report Footer and call it an Index. And that way, just instead of having this sorted by page number, you could sort that list by the person's name, call it an Index, and then they can easily look it up that way. So that's another thing you could do. But if you want to learn how to make this thing size dynamically, then that's going to be covered in the extended cut. A little bit more programming involved.
Do you like this stuff? Do you like learning with me? Do you like my style? Am I cool? If you like learning with me, come to my website and check out all my developer lessons that are available. I've got lots of them. I've got 45 at last count. And each one is at least an hour long. Some of them are like 3 or 4 hours long. But check them out. I teach you VBA the way that it's supposed to be taught, from the beginning. We go through all the basics and then we slowly get more advanced. And by the time you're done, you'll know as much as me, or almost. Well, okay, maybe not a little bit more than me, maybe I don't think that out later. That's going to be a TechHelp video for today. I hope you enjoyed the film, and the series. I hope you learn something. Once again, thank you very much for the question.
If you guys got questions like this, you want to see how to do something in Access, and you can't find anyone else that's done an article or video on it, let me know. I did see a couple of other articles and some videos on doing a Table of Contents when I was doing my research. Some of them were pretty decent, but I have my own flair for things. I like to put things together my way, sometimes. But yeah, that's going to do it. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main challenge with generating a Table of Contents in a Microsoft Access Report as described in the video?
A. Access does not allow the addition of text boxes to reports
B. There is no built-in way to programmatically navigate to the last page of a report in Print Preview using VBA
C. Reports do not support headers or footers
D. MS Access cannot generate page numbers
Q2. Why is the SendKeys method used in this tutorial?
A. To copy data between reports
B. To simulate pressing the End key and navigate to the last page of the report
C. To automate closing and reopening reports
D. To automatically add a table of contents
Q3. What is an important caution mentioned about using SendKeys?
A. It is recommended for use in automated, scheduled tasks
B. It is unreliable, so it should only be used in user-initiated actions such as clicking a button
C. It saves reports automatically
D. It can only simulate the Tab key
Q4. What does TempVars("MakeTOC") control in the process?
A. Whether the report contains customer data or not
B. Whether the Table of Contents should be generated or skipped during report creation
C. The color of the Table of Contents
D. The default page size of the report
Q5. Why is DoEvents used in the VBA code?
A. It pauses the code for user input
B. It allows Access to process background operations before proceeding
C. It saves all open forms
D. It deletes variables from memory
Q6. What benefit does the Status function provide in addition to DoEvents?
A. It displays progress messages to the user while performing tasks
B. It renames all reports automatically
C. It encrypts data in the database
D. It exports the report to Excel
Q7. What is a limitation of this Table of Contents technique as described in the video?
A. The Table of Contents must be in a separate report
B. The Table of Contents can only be one page long; if it goes over, page numbers become inaccurate
C. Only blank tables can be included
D. It will not work with color printers
Q8. What does the instructor suggest as a workaround if your Table of Contents is likely to be more than one page long?
A. Split the report into multiple files
B. Place the Table of Contents in the Report Footer and call it an Index sorted by name
C. Reduce the font size
D. Disable headers altogether
Q9. What step ensures that updates (like deleted customers) are reflected in the Table of Contents page numbers?
A. Re-opening the report after deleting records
B. Saving the report layout as a template
C. Manually adjusting page numbers
D. Restarting Microsoft Access
Q10. Why does the code close and reopen the report after generating the Table of Contents?
A. To update the report design
B. To ensure the Table of Contents and page numbers are generated correctly without regenerating on the second open
C. To remove old formatting
D. To clear TempVars variables
Q11. What general advice does the instructor give about saving report objects during automation?
A. Always use ACSaveNo to avoid changes
B. Use ACSaveYes to ensure design changes are not lost for developers
C. Always save as a new object
D. Design changes are never saved automatically
Q12. If you want the Table of Contents to update dynamically for multi-page TOCs, what will you need to do according to the video?
A. Add more headers to the report
B. Use more advanced programming logic, which is covered in the extended cut
C. Convert the report to a PDF first
D. Export data to Excel
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-A; 7-B; 8-B; 9-A; 10-B; 11-B; 12-B
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 my series on adding a Table of Contents to a Microsoft Access report. This is part 3, so if you have not already gone through parts 1 and 2, I recommend you do that first. We are building on what was covered in those previous lessons.
At the point where we left off in part 2, we had created the report header, included our title page, and set things up so that the Table of Contents page is created and then deleted after the initial run. However, we still have to take care of updating the page numbers and working with the Table of Contents display.
To achieve this, the report needs to be opened once, navigated to the last page, then closed and reopened again. The second time it opens, we want Access to leave the page numbers alone so everything displays correctly. In Access VBA, you might be familiar with moving through records in a form, but with reports, especially in Print Preview mode, VBA does not provide a straightforward way to jump directly to the last page. There is no GoToRecord equivalent for reports in Print Preview.
After spending some time researching and experimenting, I discovered there is really no direct VBA command for this, so the only practical method is to use SendKeys. I am not generally a fan of SendKeys because it is not always reliable, but in this case, it gets the job done. If you are not familiar with SendKeys, it simply simulates keystrokes as if you were typing them yourself. Here we use it to trigger the End key, which takes the user to the last page of the report. This combination is effective, though I strongly recommend you only use SendKeys with user-initiated actions, like clicking a button. Do not try to automate it in something that runs in the background, because the reliability just is not there.
After opening the report and sending the "End" keystroke, you will be able to see all the data properly listed in the Table of Contents. Once the report shows correctly and the Table of Contents data is accurate, you can close the report and reopen it again without changing the contents, since you have already generated them.
To control this, I use TempVars to set a flag variable, which I call MakeTOC. You set this variable to True for the first opening, so the Table of Contents is built. In the code that processes the report, check TempVars("MakeTOC") so that the Table of Contents build step is only performed when needed. After the first run, set MakeTOC to False so the routine does not regenerate the contents on subsequent opens.
It is important to ensure the report is closed and saved before reopening it. I always use ACSaveYes when closing database objects as a habit as a developer, so any design changes are saved. End users generally do not have access to the design or VBA, since those features are locked down in ACCDE files, so ACSaveYes is really just for developers.
Between closing and opening the report, you should include a DoEvents command. Access sometimes needs this to finish processing behind the scenes before continuing the next VBA step. If you have seen my videos before, you know I often use a custom Status function, which displays messages on a status box for the user. My Status function incorporates DoEvents, so if you use it here, you also provide feedback to the user, like "Generating TOC," "Opening Report," and finally "Done."
Once these steps are set up, you can save and compile your application, close everything down, then test it by clicking your button. You should see the report open, generate the Table of Contents, close, and automatically reopen with all of the pages and page numbers correctly in place. As an example, the Table of Contents now accurately points to the correct pages for each item.
If you ever need to make a major update to your data, say you delete a few customers, simply rerun this process. The Table of Contents will adjust and reflect the new page numbers accordingly. Access sometimes leaves a blank page at the end, but as long as you navigate to the correct last page, your contents will be correct.
One limitation with this method is that the Table of Contents can only be a single page. If your Table of Contents spills onto a second page, the calculations for the page numbers will be thrown off, because the report initially assumes it is working with just one blank page for the Table of Contents. If you know your Table of Contents will exceed one page, you will need a way to dynamically recalculate the page numbers. I will cover this more advanced scenario in today's Extended Cut, where I will walk through handling a multi-page Table of Contents so the page numbers remain accurate. If you are looking for alternate solutions, consider moving the Table of Contents to the report footer and calling it an Index, sorting by names instead of page numbers.
If you enjoy these lessons and my teaching style, I invite you to visit my website where you will find all of my developer lessons, including dozens of in-depth tutorials on Access and VBA. I structure my courses to take you from beginner topics all the way up to advanced concepts, so you can build your skills step by step.
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 Using SendKeys to navigate to the last page of a report
Automating Access to display the last page in Print Preview
Setting and using TempVars to control report flow
Conditional logic in reports using TempVars
Steps to regenerate or preserve the Table of Contents
Using DoEvents to process background operations in Access
Replacing DoEvents with a custom Status function
Updating report formatting including background and borders
Demonstrating real-time updates of Table of Contents page numbers
Explaining the limitation of a single-page Table of Contents
Suggesting moving the Table of Contents to the Report Footer as an Index |
