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.