Embedded Report
By Richard Rost
3 years ago
Embed a Report Inside a Form for Readability
In this Microsoft Access tutorial I'm going to teach you how to embed a report inside of a form to make it easier to read a customer's contact history right on the customer form.
Linda from Casper, Wyoming (a Platinum Member) asks: I love your customer contact manager, however if I just want to scroll down and read through the customer's contact history, it's not exactly easy to do. I have to zoom in on each record to see all of the text. Is there any way to make like a "reading mode" for this?
Members
There is no extended cut, but here's my database:
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
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, embedded report, subreport in form, reading mode
Intro In this video, I will show you how to embed a report inside a form in Microsoft Access to improve the readability of your data, specifically by making it easier to read through a customer's contact history. We'll walk through creating a simple contact report, configuring the Can Grow and Can Shrink properties for better display, and embedding the report into a customer form. I'll also show you how to add buttons to switch between data entry and reading modes, with a bit of VBA for toggling visibility and refreshing your data.Transcript In today's video, I'm going to show you how to embed a report inside a form for improved data readability in your databases. Basically, what we're going to do is take the customer form and embed a report on it to make it easy to read through all of their contact history.
Today's question comes from Linda in Casper, Wyoming, one of my Platinum members. Linda says, I love your contact manager. Thank you very much. However, if I just want to scroll down and read through the customer's contact history, it's not exactly easy to do. I have to zoom in on each record to see all the text. Is there any way to make like a reading mode for this?
Well, yeah, there are a couple of ways to do this. I think the easiest way is to simply make a nice readable report and embed that inside the customer form. That's what I'm going to show you how to do in today's video. Just like this, here's the customer information and here's your little contact report.
Now this will be an expert-level report map. It's kind of going to be too difficult. I almost made it beginner, but there are a couple things you should know first. I consider beginner being people who are just starting out. First is the advanced stuff with the programming experts, everything in the middle.
So first, if you haven't watched my customer contact manager video, go watch this. It explains how I built this database, including the customer form with a subform on here that you can put the contact history in. Watch this video on subforms; it gives you a little more information on subforms. This is kind of optional, but it's good to know.
And this one is kind of important. I need you to know how to use the Can Grow and Can Shrink properties in a report to make the sections get bigger. Go watch this one too. I'm going to show you what you need to know here, but this will give you some more background on that.
Go watch these first. These are all free. They're on my YouTube channel. They're on my website. Go watch them and then come on back.
All right, so here I am in the TechHelp free template. This is a free database you can download on my website if you want to. We've got customers; here's a customer list. You can open up each individual customer form. Now, on the single customer form, you can go to contacts and here's the contact history for that particular customer, which is what we built in the other database.
We also built another form right here called Customer with Contacts. I built this one to teach you how to do a form with embedded subform. Now, this is good for data entry, but what Linda is saying is it's not exactly easy to go down and read all of this stuff. If you have to see all this, you've got to click here. You can zoom with Shift+F2 if you want to see more. Click here, Shift+F2. If you go to somebody else, let's see what else we've got here. Click Shift+F2.
So yeah, this is great for data entry, but it's not great for readability. If you want to be able to read this stuff easily, then we can put a subreport on here.
How do we do that? Well, first up, what I'm going to do is, in this customer contact form, this is the form we're dealing with. I want to make a copy of this real quick. You'll see why at the end of the video. Just make a copy of that for backup purposes. Always good to, by the way, folks, let me show my backup slide real quick.
I like to throw this warning in there when I do things like delete queries and things that are going to radically change your database, but it's a good idea, a good habit to get into to just make a copy of any objects that you're going to be working on before you start working on them. Just like what I just did. I'm going to make some major changes to this customer form. Just make a copy of it, and then at the end of the day, when you see all these "copy of, copy of," just delete those as long as everything worked and everything went to plan.
And of course, you should still have your nightly backups too. Watch my backup video for more information. I'll put a link to that down below.
So what we're going to do is we're going to make a contact report that's really simple, that we're going to embed in that other form. I'm going to copy my blank report down here. This is just a blank report that I've got in my template that's got all the settings that I like in it. Copy, paste. We're going to call this one my Contact Report.
Open up this guy in design view. We're going to make this real simple. In fact, we're going to make it smaller because it doesn't need to be that wide. Make it about yay big. Get rid of the headers. You can turn these off if you want to, just right-click and turn the sections off. Page header footer. Right-click report header footer. We don't need them for this.
Let's take this and make this the date and time. Let's first change our data source. Our record source is going to be the contact table. You can get rid of the filter. You can get rid of the order by if you want to. Actually, let's leave that order by in there. Let's not sort it by customer ID though. Let's sort it by date, which is going to be—let's check our fields here. Control source; it's contact date.
So we're going to go back. Let's make sure that this is also contact date here in the name. And let's go back to our order by data. We'll make this order by contact date.
Then we're going to copy this. Actually, let's left align it first. Left align. Now copy, paste, paste. There are two fields I want to see in here. We've got description and we've got notes. Let's make this wider, out to maybe there. Let's bold this. This will be our description field, and then they can optionally have notes. Copy and paste. Then we can bring this bottom up like that.
Save it, close it, and just open it. Normally, I go to print preview, but this won't be in print preview mode. You'll be viewing this in an actual view like this.
Looks fine. Notice, however, that this didn't get any bigger, and these sections have a lot of wasted space between them. Why? Because of our Can Grow and Can Shrink. So highlight all three of these, go to Format, and find Can Grow and Can Shrink and set these both to Yes. Remember from the other video, the detail section itself has Can Grow and Can Shrink, so set those both to Yes as well.
Save it, close it, open it up. That looks a lot better. You can see we've got no extra spaces between them, and you've got this one here continued on the line too. So do this one here. Looks good.
Now, you're seeing the contacts for everybody in here, but that's okay. When we embed this report into the form, the relationship we made between the parent and the children will work.
So come over here, go back to this guy, right-click, design view. We're going to delete this guy for now. Just delete it. Goodbye. Don't worry; I did make a copy before.
Now we're going to take that contact report, click, drag, drop. Get rid of that little label that comes in with it, and then resize it, make it bigger like that. Save it.
Now, I want you to take a look at the properties real quick. Double-click on the border, so you're right there, not inside it. You want to click on the border around it. Double-click on that. That'll bring up the properties. Go to Data and make sure it's got CustomerID in both of those spots. Because there's a CustomerID in the table underneath this guy, it still finds that relationship. You don't necessarily have to have the CustomerID on that report. As long as Access sees it in the record source underneath.
Save it, close it, open it. There you go. There's your reading mode. Now you can come over here and scroll down and read through all the contacts. Look at that. Nice. Go to the next person, and there's theirs.
And that makes it so much easier to read.
I am going to say that Firefly has some of the best quotes. Someone ever tries to kill you, you try to kill them right back.
Why did I have you save this guy? Because we can flip between that and that with a little bit of code. Just a little tiny bit. Now, I know I said at the beginning of the video, there's no programming involved and you don't need programming for what we did. What you did right now is fine. It's good. Video is over.
But if you want to learn a little tiny bit of something cool, watch this. Go watch "Intro to VBA"—it's about 20 minutes long. If you've never programmed before, it'll teach you everything you need to know to get started. Then you can do cool stuff like this.
Watch: We're going to come in here to the copy, we're going to copy this object, Ctrl+C, close that. Go back in here, design view. Now this guy—just pay attention real quick—this guy is ContactR. I'm going to click here and paste in that subform again, move it right over the top of this guy. This guy is ContactF. So we've got ContactF, ContactR. One's a form, one's a report.
In fact, I'm going to start it off so that the ContactR is not visible. Visible is No. So when this form opens, you're going to see the ContactF.
Now, we're going to make two buttons up here. One to go to data entry mode, one to go to reading mode. Go to Form Design, give me a button, drop a button right here. Cancel the wizard. We're going to put in here Data Entry. Slide it over like this. Open up the properties. We'll call it DataEntryButton, BTN. Right-click, build event. That brings up my code builder.
ContactF.Visible = True.
ContactR.Visible = False.
That's the Visible property for that ContactF and ContactR object, the subform and subreport that we put in here earlier.
Now, copy, paste that button. We'll make this guy our Reading Mode button. Name ReadingButton. Right-click, build event. We're just basically reversing this.
This one will be false, and this one will be true. Save it, close it, close it, close it. Open it.
We're in data entry mode. Flip to reading mode. Boom. Look at that. The both objects are on there; you're just changing which one is visible. That's all—a little swap—and you can go through your records just like this, and everything will be displayed nicely.
Want to edit that? Data Entry. There it is. Put something else in here, blah, blah, blah. Go back to the reading mode. You might have to throw a refresh in there too, because this record is not going to refresh. So let's go back to our code. Especially when you're switching to reading mode, you need to requery that subform. So it's going to be "ContactR.Requery".
Now let's try it. Boom. There it goes. It updated it. Because you make changes here, hit Reading and it's got to requery. It's already loaded in the background, it's just not visible, so you're telling it, "Hey, refresh yourself".
That's pretty cool. If you like learning this kind of stuff, I have lots of lessons available. Here are my Access Expert courses. There are 32 of them. I go through all kinds of stuff like this, playing with subforms, subreports, queries, you name it. There's all kinds of stuff.
If you like this programming stuff, I have tons of Access Developer lessons too. I'm up to 43, I think right now. 44 is in progress right now. I've got lots of stuff for you to learn. And if you become a member on the site, you get a free class every month. So Silver members, you get a free beginner class; Gold members, you get up to Expert; Platinum members, you get up to the Developer classes. You have to take them in order. That's how you should take them. They're designed so you learn this, then you learn that, and you learn it in the order that I think you should learn it, instead of jumping around like I do in these TechHelp videos.
There you go. There's your TechHelp video for today with a little extra bonus programming stuff in there for you. I hope you learned something.
Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main purpose of embedding a report inside a customer form, as discussed in the video?
A. To enable data entry directly from the report
B. To improve the readability of customer contact history
C. To create a backup of the customer form
D. To generate automatic emails from the database
Q2. Why is using a standard subform for contact history not ideal for reading long records?
A. Subforms cannot display text fields
B. Subforms automatically filter out most records
C. Subforms require zooming in on each record to read all the text
D. Subforms cannot be embedded in forms
Q3. What property needs to be set in the report to allow sections to expand and shrink depending on the content?
A. Control Source
B. Tab Stop
C. Can Grow and Can Shrink
D. Locked
Q4. Before making major changes to a form or report, what is recommended in the video?
A. Delete all related queries first
B. Make a copy of the object as a backup
C. Turn off all form events
D. Remove all form controls
Q5. When embedding a report in a form, which field links the report to display only relevant records for the current customer?
A. CustomerAddress
B. CustomerID
C. ContactDate
D. Notes
Q6. After embedding the contact report into the form, what allows users to switch between data entry mode and reading mode?
A. Changing the form Record Source dynamically
B. Using buttons that set the Visible property of the subform and subreport
C. Refreshing the entire database
D. Deleting and re-adding the controls each time
Q7. What programming language is briefly introduced to handle the "reading mode" and "data entry" buttons in the video?
A. Python
B. VBA (Visual Basic for Applications)
C. JavaScript
D. SQL
Q8. When switching to reading mode, what needs to happen so the displayed data is up-to-date?
A. Close and reopen the database
B. Use the Requery method on the report object
C. Convert the report to a subform
D. Change the Report Header text
Q9. Why is the Can Grow and Can Shrink property important in reports for readability?
A. It locks the report layout so nothing changes
B. It automatically duplicates records for visual spacing
C. It allows sections and controls to adjust size depending on the data
D. It disables editing in the report view
Q10. What is an advantage of using the embedded report method for contact history?
A. It prevents users from making any data changes
B. It is more readable and allows scrolling through all contacts without extra clicks or zooming
C. It makes all contacts editable directly in the report
D. It requires advanced programming skills to implement
Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-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 is all about enhancing the readability of your customer data in Microsoft Access by embedding a report inside a form. This technique is especially useful if you want to provide a clear, easy-to-read log of a customer's contact history rather than using a standard subform layout, which can be cumbersome for reviewing large amounts of text.
One of my Platinum members expressed that while the contact manager works well, it is difficult to simply scroll and read through all of a customer's contact history. The subform design is ideal for entering new records, but not for easy reading. If you want a more readable view or "reading mode," there is a simple solution.
The approach I recommend is to build a user-friendly report—formatted for readability—and embed that as a subreport in your customer form. This creates a seamless experience where you can view the customer's info at the top and their nicely formatted contact history right below it, all on one screen.
Before you start, it's helpful to understand a few foundational concepts. If you have not already, make sure you have watched my other lessons on building customer contact managers, working with subforms, and using the Can Grow and Can Shrink properties in Access reports. These will provide background on how forms and reports interact as well as how to make your report sections dynamically adjust for different amounts of content.
Using the free TechHelp template, which is available for download on my website, you can locate the Customer List form and drill down to an individual customer's details. Traditionally, you see a subform with their contacts, and for each record, you might have to open a zoom box to read the full notes if the field size is too small. This is where the new readable report comes in.
To add this "reading mode," the first thing I do is make a backup copy of the form I want to modify. This is just a good habit whenever you make substantial changes—having a backup lets you recover easily if anything goes wrong.
The next step is to build a simple contact report. I start with a blank report template, adjust its size, and remove unneeded sections like headers and footers. Set the record source to the contact table, and change the sorting so records appear by date. The main fields I want to display are the contact date, description, and optional notes. The design should emphasize readability—aligning text to the left, making fields wide enough, and bolding descriptions for emphasis.
A crucial point to remember is to enable the Can Grow and Can Shrink properties for all relevant controls and the detail section itself. This allows the report to neatly expand or contract each record's area based on the content, avoiding wasted space and ensuring that long notes or descriptions are fully visible without awkward gaps.
When you view this report, it initially shows records for everyone, but when you embed it as a subreport into the customer form, Access automatically links parent and child records by CustomerID, as long as this relationship exists in your tables and form structure. You do not have to place the CustomerID directly on the report for this linking to work.
Back in the customer form, remove the old subform and embed your new contact report using the subreport control. After adjusting the size, check the subreport's properties to confirm the CustomerID is mapped correctly for both the master and child fields. Save the form and open it—now you have a much more readable display of contact history for every customer, with the ability to scroll through their records in an easy-to-read layout.
If you want to go a step further, you can add a bit of VBA programming to give your users the option to switch between data entry mode (using the subform) and reading mode (showing the subreport). This involves placing both controls on the same form and toggling their Visible properties with command buttons. One button enables data entry mode, while another switches to reading mode. You need to make sure that when you switch to reading mode, the subreport is requeried so it displays the latest data. This is a great way to give your users the best of both worlds—efficient data entry when needed and clean, readable historical views for reference.
If you are interested in learning more about Access programming or want to grow your skills with forms, reports, and VBA, there are many lessons and classes available on my website. Members also receive free classes each month, and all my courses are structured to build your skills in a logical order.
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 Embedding a report inside a form for improved readability
Creating a simple customer contact report
Setting up the report's record source to the contact table
Configuring the report to sort by contact date
Customizing report layout by adjusting section visibility
Using Can Grow and Can Shrink properties in a report
Embedding a subreport into a customer form
Linking subreport data using CustomerID
Adjusting subreport properties inside the form
Switching between data entry and reading modes on a form
Adding buttons to toggle between form and report views
Writing basic VBA to control subform and subreport visibility
Refreshing the subreport using VBA's Requery method |
