Table of Contents
By Richard Rost
2 years ago
Create a TOC/Index in a Microsoft Access Report
In this Microsoft Access tutorial, we'll tackle creating a dynamic Table of Contents, complete with group page numbers, to organize reports efficiently.
Juan from Highland Mills, NY (a Platinum Member) asks: Can you show us how to do a Table of Contents with the page number showing on the first page of the group? I have been working on this for some time and hit a dead stop. Please add to your long list of requests.
Prerequisites
Links
Recommended Courses
Table of Contents Series
Keywords
TechHelp Access, table of contents, index report, Access reports, page numbers, report grouping, VBA programming, query design, TempVars, dynamic index, on print event, append query, storing page references, report formatting
Intro In this video, I will show you how to create a table of contents for your Microsoft Access reports, including displaying the page number where each group starts. We'll use report grouping, queries, and a little VBA programming to track and save page numbers for each group header. You'll learn how to design the query and report, set up proper formatting, use the on print event, and store the table of contents data in a separate table. This is a developer-level tutorial, so some basic programming knowledge is helpful. This is part 1.Transcript Got a good one for you today, folks. I'm going to show you how to build a table of contents in your Microsoft Access reports, and by extension, you can use this to build an index for the end of the report. I'm going to show you how to do a table of contents. Today's question comes from Juan from Highland Mills, New York. Juan's actually one of the moderators on my website. So he's got a ton of different badges down here, almost as many as Adam. Don't ask. If you want to know what these all are, well, check out my website. But Juan says, "Can you show us how to do a table of contents with the page number showing on the first page of the group? I've been working on this for some time and hit a dead stop. Please add to your long list of requests." Well, Juan, challenge accepted. I saw Juan's post in the forums this morning and I'm like, "You know what? That sounds like a great idea. So let's do it." Before we get started though, this is a developer-level video. What does that mean? Well, we're going to need some programming. Unfortunately, Access does not have a means to automatically generate a table of contents like Microsoft Word does. So we have to use a little bit of code and a little bit of ingenuity, but it's not super hard. But you will need a little bit of programming. So, if you've never done any VBA programming before, go watch this. It's about 20 minutes long. It'll teach you everything you need to know to get started.
You should have a good grasp on table relationships. We are going to use a report grouping level to indicate where each item in the index or in the table of contents appears. So, we're going to want the page number for the group header. We're going to do customers and contacts, but this could be categories and products or vehicles and drivers, or whatever. We're going to do a little bit of SQL, so go watch this video if you've never worked with SQL before.
And finally, Adam's favorite, we're going to be working with some temp vars. Just one. But if you've never used a temp var before, don't worry about it. They're easy. But you can go watch this video to learn more about them. These are all free videos. They're on my website. They're on my YouTube channel. Go watch them all. Come on back, and I'll be ready for you.
All right. Here I am in my Tech Help free template. This is a free database. You can download a copy off my website if you want to. And in here, I've got customers. Big long list of customers. And each customer can have one or more contacts associated. This is an instance of talking with that customer. You got information about them; you've talked to them on the phone; they came in for an appointment, whatever, you put notes in here. Okay, so we're going to make a report that's going to be customers with their contacts and the group is going to be customer, and the contact is going to be the detail items. Okay, but first before we do that, we'll need a query. So let's go to create, query design, we can close this. I'm going to bring in customers and contacts, and we're going to make this join a left join, like that. That way we'll see all the customers, whether or not they have contacts. And let's bring in the fields we'll need. Let's bring in customer ID, first name, and last name, and then from the contact table, I will bring in the contact date, the description, and the notes. We'll save this as the customer contact query, and if you run it and take a look at what we got, there you go. All right, for every customer, you'll see all their contacts, so this will be the group in the report and this will be the detail information. Okay, very simple straightforward query. Let's take this and build a report from it.
All right. I'll just do it from scratch. Create, report design. I'm going to turn off this. We don't need this just yet. We're going to come back to it in a minute. Let's open up the properties for the report. Let's change the record source to that query that we just built, the customer contact queue. Now we can close that. Add existing fields. We're going to bring all these fields in. Click on the first one, click on the last one, click and drag, drop them right there. Close the field list. And I'm going quickly because this is all covered in those other videos that I mentioned earlier. So, if you're a little lost, go watch those other videos, okay? This is how my regular classes are. In the developer lessons, I go a little faster because I assume you watched all the beginner and expert lessons.
All right. I'm going to delete all these labels. We don't need them. We're going to take the customer ID, first name, and last name, and put them there. All right. I'm going to left-align all these guys. Format, left align, maybe make these bold. All right. The contact date is going to go over here. The description will go next to it like that, and then the notes will go underneath, right there. Okay, let's bring that section bottom up, click and drag, click and drag. Okay, let's save this customer contact R and let's get a print preview. Print preview, okay, looks good. But I want the contacts to appear once under the customer. So we're going to use a grouping level for that. So, back to design view. This is where we turn on grouping and sorting. It's that thing that I turned off earlier. I just want more space. All right, we're going to add a group. The group is customer ID, and I got a group header. I don't need a group footer for this one, so that's fine. Once you've created that group, we can actually close this thing. Now, don't click that X, click that X because this X deletes the group. A note for the Microsoft team, Sammy put this on the list. This and this are too close to each other. People click on this one all the time. So that's bad. Okay, close that. All right, one of my other moderators, Sammy, is keeping a list of all the things suggestions for the access team to fix. Right now that we got this group header, we're going to take these guys, cut them out, Control X, click here in the header, paste them in and there they go. All right; I'm going to make this a little bit bigger; we're going to slide those down just a touch and then now we can take these and slide them up like that. And there we go. Save it, close it, let's take another peek.
All right. Where are we at here? Right here, right click, print preview and every other spatter, I'm not a big fan of that alternating, uh
, detail rows here. So, what we're gonna do is we're gonna turn that off, kinda. We're going to go to the detail section here, change this alternate background color of here, no color; I'm gonna make this background color, only the background one. I'm gonna make the header though; we're going to change the header color. So, again here, make that no color and we'll make this, let's go with a light gray, like that, which means we then need to make these transparent. So, we'll go to format, we'll pick this, and we'll go transparent, and that will look better. Okay, save it, close it, close it, let's open it again, right click, preview. All right, looks much better. There's me; there's the next customer; there you got a couple more formatting things to do here. First, let's get rid of all these borders; all these borders are nasty. The design view, we're gonna select everybody, go to format, find the shape outline, and go to transparent. Next, if you notice, we have a whole lot of wasted space in here because not every one of these has notes under it. Some do, some don't. Let's see. What we're going to do is we're going to select all of these down here, all of those. We're going to come over to format, and we're going to find can grow and can shrink. We're going to set those both to yes. We're also going to set the detail section to can grow, can shrink, both of those to yes. So these boxes can disappear if they don't exist or they can get bigger because you can have a lot of notes in there. And the detail section itself can grow and shrink. That's another thing people always ask me about.
All right, now save it. Let's close this, close it. Let's go one more time into print preview and okay, it looks a lot better. Okay, so we got a few things here, like our dates down here. Let's format those dates. This means that box is too narrow because there are probably dates and times in there. So, we're going to open up this; we're going to go to the format property here, right there. ISO date, the standard format which everyone should use, yyyy-mm-dd. If you want to learn more about that, I'll put a link down below to my ISO date video. I'm on a mission to make the whole world use ISO dates. All right, and we can also, did we left align everybody? Let's make sure we did here. I think I did earlier. I forgot now. I'm getting old and senile. Okay, all right. Let's make this look like that. Save it, close it; we're almost done with the formatting. I can't work if my report isn't formatted properly, and it just, you're not gonna get the right effect anyways with the table of contents. Okay, so there's Richard Rost; there's that; dates look good; contacts look good; all right, everything looks good; this guy can grow and shrink. Beautiful. All right, the report looks perfect. Now we can actually get to some work. So the goal is for this assignment, let's call this our homework assignment here, we want to know, we want to create a table of contents, and I want to mark for each customer what page they start on. All right, this is page one. If I go to page two, I can see Reginald Barclay is on page two; Geordi La Forge is on page two. Let's go to the next page. I want my table of contents to say Mr. Spock is on page three. How do we get that information? Well, it's difficult because we have to let the report run through itself once on all the pages before we can actually get the page numbers for each of those customer ID headers. It's tricky, but we can see what those page numbers are if we go to events for the section header, right, the customer ID header. Find the on print event right down here - on print event. We're gonna hit the dot dot dot button, open up the VB editor. We're in the print event for that section. Now, right in here, let's just do this: MessageBox. Let's MessageBox "FirstName" which is a field in here and a space and then the page number which is just "Page". Page is a special variable; Access knows what it is. All right, now if I print preview, right click, print preview, Richard1, James1, Deanna1, Jean-Luc1, William1. Okay, so all of those page headers ran, and they're all ones. These are all on page one. Guess what? If I go to the next page, all those run. Malcolm, page 2; Wesley, Tasha, and so on. All the page 2 headers are generated. Okay, there's all the page 2 people. Now, I don't want to MessageBox that stuff. I want to save that information. Right? I want the report to run through once, tell me what all of those page numbers are. Let's save that data in a table. Okay, because once we got that, then, then we can close the report and then reopen it another time and put that table of contents information in it. Okay, that's the tricky part. So let's get rid of this for now. We don't need this just yet. Okay, we're gonna come back to you. Hold on to it. All right, close this. Let's make a table to store this data in. So create, table design, this will be our table of contents, so TOC, ID, we'll put an auto number first, all right. We'll put description in here. This is going to be what appears in the table of contents. This can be anything, depending on what you're doing. And you can use this table for multiple reports if you want to. So it could be, you know, your first name and last name; it could be the product name, whatever you're doing. Then "PageNumber", which is our number, that's it; save it as TOCT. All right, you could do T-o-c if you want to, if you want to be picky. All right, so now what we're gonna do is, when this thing runs, we're gonna instead of MessageBoxing it, we're gonna feed that data into here using an append query. Okay, so let's go back over here to our code, and right here, we're going to say CurrentDb.Execute. This is how we run an SQL statement: "INSERT INTO TOCT", the thing we just created. What are the fields? "Description, PageNumber," all right. Put a space there; don't forget that space; we're going to continue the line. And now we need to put in here, let's put "FirstName" and "LastName" together, comma, and then the page number. So it's going to look like this: "VALUES ('', '')", because we've got to put double quotes inside the SQL string, right? Because "FirstName" and "LastName" are a string
, so they have to be inside double, double quotes. And if you're not familiar with double, double quotes, I've got a whole separate video on double, double quotes. I'll put a link to that down below in the link section as well. So now we're in here, we're going to do: [FirstName] & ' ' & [LastName], or you could do [LastName], [FirstName], however you want to look. I don't care. They're your Legos. Now we've got to close the double double quotes and then a comma and then put the page number. Just like that. There's our SQL statement. So each time this group header is printed, is generated on the screen. Print or print preview, same thing, it's going to insert a line into that table with the "FirstName", "LastName", and the page number. Okay? Save it. Always handy to throw a Debug Compile in there. Come back over here. Let's close the report. And now let's open it again. Right-click, print preview now nothing appears to have happened; let's take a look at that table. And look at that; there's all the page one people. It didn't get all of the people because we only generated page one, so we'll see how to get the rest of the pages in here tomorrow. We got lots more to do with this database, folks, but tune in tomorrow, same bat-time, same bat-channel for part two. And, of course, if you're a member, you can watch it right now because I'm going to record it in just a few minutes. But that's going to be the tech help video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time. I'll see you tomorrow for part two.Quiz Q1. What is the primary goal described in the video tutorial?
A. Automatically generating a table of contents for an Access report with group page numbers
B. Designing a form for entering customer data
C. Creating an automatically updating chart for sales data
D. Exporting Access reports to Microsoft Word
Q2. Why is creating a table of contents in Access reports more complex than in Microsoft Word?
A. Access does not support grouping
B. Access has no built-in way to generate a table of contents, unlike Word
C. Access only works with single tables
D. Word uses SQL for table of contents and Access does not
Q3. What programming knowledge is suggested as necessary for this tutorial?
A. VBA programming in Microsoft Access
B. JavaScript for Access forms
C. Python for external automation
D. HTML coding for report themes
Q4. How is the relationship between customers and contacts described in the video?
A. Each contact can have multiple customers
B. Each customer can have multiple contacts
C. Customers and contacts must always be entered in pairs
D. Contacts and customers are unrelated in the database
Q5. Which type of query is built to provide data for the report?
A. Select query with an inner join
B. Crosstab query
C. Query using a left join between customers and contacts
D. Union query
Q6. What is the purpose of using a left join when building the query?
A. To display only customers who already have contacts
B. To show all customers, even those with no contacts
C. To combine unrelated tables
D. To eliminate customers with more than one contact
Q7. What report feature is used to organize details under their related customer?
A. Grouping level based on customer ID
B. Sorting by contact description
C. Filtering by customer last name
D. Conditional formatting by region
Q8. Why are the Can Grow and Can Shrink properties set to Yes in the report?
A. To allow dynamic resizing of text boxes and detail sections based on content
B. To fix field sizes to the smallest possible
C. To prevent users from editing the report
D. To force every detail section to be the same size
Q9. What trick is used to capture the page number for each group header (customer)?
A. Using the On Print event of the group header
B. Writing an SQL update query manually
C. Typing page numbers by hand
D. Reading page numbers from a hardcoded list
Q10. Why is data about group headers and page numbers stored in a separate table?
A. So it can be used later to build a table of contents for the report
B. To improve report rendering speed
C. For historical backup purposes only
D. To export to Microsoft Excel automatically
Q11. What SQL operation is used by VBA to record the table of contents information?
A. APPEND query (INSERT INTO)
B. UPDATE query
C. DELETE query
D. SELECT query
Q12. What variable is used in VBA in Access reports to give the current page number?
A. Page
B. ThisPage
C. Me.PageNum
D. Report.CurrentPage
Q13. What does the video recommend regarding report formatting before creating the table of contents?
A. Ensure the report is formatted nicely for best visual effect
B. Ignore formatting until the end of the project
C. Use random colors for variety
D. Always print in black and white
Q14. What is the main challenge in associating group headers with the correct page numbers?
A. The report must process completely before all page numbers can be known
B. Group headers are hidden by default
C. Access does not support grouping on reports
D. VBA cannot access report variables
Q15. What is the general approach described to create a table of contents in Access?
A. Let the report run, collect group header names and starting page numbers into a table, then use that data for the table of contents
B. Manually read each page and type the results into a table
C. Run a macro to generate random page numbers for each group
D. Import a table of contents from Microsoft Word
Answers: 1-A; 2-B; 3-A; 4-B; 5-C; 6-B; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-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 focuses on how to build a table of contents for your Microsoft Access reports. This technique can also be adapted to create an index at the end of your report if you need one. In this lesson, I'll show you how to display the page number where each group starts in your report - for example, each customer in a list. The scenario for today comes from a forum post: how can you show a table of contents with the page numbers for the first page of each group? This is not a built-in feature in Access the way it is in Microsoft Word, so it takes a bit of programming and some careful planning to achieve.
This tutorial is geared toward developers, so you will need some experience with programming in VBA. If you are new to VBA, I recommend watching my introductory video on the subject, which explains the basics you will need. You should also understand table relationships, as report grouping levels are required for this method. The grouping will be used to indicate where each item in the table of contents or index appears. In this example, we'll demonstrate the technique using customers and their contacts, but you could easily apply it to other scenarios such as product categories and products or vehicles and drivers.
Basic knowledge of SQL is also important, as we'll do a little coding with queries. If SQL is unfamiliar to you, I encourage you to check out my beginner video on the topic. Additionally, we will use temporary variables - only one in this lesson - but if you've never worked with temp vars before, it's a good idea to familiarize yourself with them. All the prerequisite videos are available for free on my website and YouTube channel.
For this lesson, I'm working with the Tech Help free template database, which is available for download on my site. This database has a simple structure with a list of customers, and each customer can have multiple related contacts. The contacts represent interactions with each customer, such as phone calls or appointments, along with notes.
To start, we need to create a query to use as the foundation for the report. I'll use a left join between the customers and contacts tables so that all customers show up, whether they have any contacts or not. The query should include the fields you'll need: for example, customer ID, first name, last name, contact date, description, and notes. Save this as your customer contact query. When you run the query, you should see each customer with their associated contacts. This query clearly defines which fields will be available in your report, with customers as groups and their contacts as the details.
With the query set, it's time to build a report from scratch. Create a new blank report, assign the query as the record source, and add the necessary fields. I drag all of them onto the report at once, and since the details of report layout are covered in my other videos, I'll go quickly through these steps. In developer sessions, the pace is faster under the assumption that students are already comfortable with the basics.
Next, I clean up the layout by removing unnecessary labels and arranging the fields to build a clear group structure: the customer ID, first name, and last name go in the group header, while contact date, description, and notes are detailed items below. I adjust the formatting, aligning text, setting bold fonts, and placing fields for readability.
To group the report by customer, I enable grouping and sorting on the report, add a group on customer ID, and move the relevant controls into the group header. This setup ensures that customer details appear only once at the top of each group, followed by all their contacts. After making further layout adjustments, I disable alternating background colors in the details section for clarity, use a light gray for the group header, and set the controls to be transparent so that colors flow through properly. I also remove borders for a cleaner appearance.
Another important step is to let the controls and the detail section grow and shrink as needed. This is especially helpful if, for example, the notes field is sometimes empty or sometimes contains a lot of text. Setting the Can Grow and Can Shrink properties to Yes ensures the report handles variable-length data gracefully.
Once satisfied with formatting, I press on toward the real goal: creating a table of contents that lists each customer and the page number where their section begins. If you preview the report, you can easily see, for example, that Richard Rost starts on page one, Reginald Barclay starts on page two, and so on. We want this same information available up front in a table of contents.
Access reports cannot provide page numbers for each group header until the entire report has been processed, so this requires a workaround. The key technique is to capture, for each group header (in this case, each customer), the page number when it's printed. Access provides a special "Page" variable for this purpose, and we can trigger code in the group header's On Print event in the report. To test, you could place a message box displaying the first name and page number each time a group header prints. This quickly shows on-screen which group lands on which page.
However, showing a message box for every record is not practical, so instead, you'll need to save this information as the report generates it. To do that, I create a table - let's call it TOCT (for table of contents table) - with an AutoNumber primary key, a Description field for the group name or description (such as customer name), and a PageNumber field.
In the On Print event for the group header, instead of showing a message, use VBA to run an append query that adds the description and current page number to the TOCT table. This way, as Access renders the report, each group is recorded along with its corresponding starting page. After you run the report, the TOCT table will contain a record for every group with the correct page number.
To make this work correctly, you must let the report run through all its pages so everything is written to the table. With this setup, you can then use the TOCT table to build a separate report or section to serve as your table of contents, listing each group description and page number.
This concludes the first part of my lesson on building a table of contents in Microsoft Access reports. There is still more to do, such as ensuring the table is repopulated as needed when the report is refreshed, and actually incorporating the generated table of contents into your report, but I will cover those details in the following lesson.
You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a table of contents for Access reports
Grouping reports by customer
Designing a report with customers and contacts
Creating a query joining customers and contacts
Using a left join in Access queries
Configuring report grouping and sorting
Adding a group header to a report
Moving fields into the group header
Formatting report sections and detail rows
Adjusting colors and background transparency
Removing borders from report controls
Setting Can Grow and Can Shrink for controls
Formatting date fields in a report
Saving and previewing Access reports
Using the On Print event in group headers
Referencing the page number in VBA
Appending data to a table with an SQL statement in VBA
Creating a table for table of contents data
Building an SQL insert statement with concatenated fields
Automatically recording group and page numbers during report printing |
