|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Open Report By Richard Rost 
5 years ago
Open Report to a Specific Record in Microsoft Access In this video, I will show you how to open a report and show only a specific, single record. We'll create a customer report and make a button on the customer form to open just the report for just that customer. I'll show you how to do it with and without VBA. Kara from Jupiter, Florida (a Platinum member) asks: I have created a customer report, however when I go to print it out, I get ALL of my customers. How do I show just a single, specific customer? MembersMembers will see how to filter the records on a continuous form and use that resulting set of records to print a report with just the selected data.
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!
Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, open report, openreport, report filter, report where, display a single record in a microsoft access report, access open report for current record only, how to print selected records in access, how to print only one record in access
IntroIn this video, I will show you how to open a report and display specific data for a single record in Microsoft Access. You will learn two different methods: the first uses a query based on a value from an open form with no programming required, and the second uses a simple one-line VBA command to achieve the same result. I will walk through the steps for each approach so you can print or preview reports for just the customer you want instead of all records.TranscriptWelcome to the TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to open a report and show specific data in Microsoft Access. You can do it easily with a form, but you can't easily do it with a report. In this video, I'm going to show you how.Today's question comes from Cara in Jupiter, Florida, one of my platinum members. Cara says, "I have created a customer report. However, when I go to print it out, I get all of my customers. How do I show just a single specific customer?" Well, Cara, it's not your fault. This is actually specifically difficult to do in Microsoft Access. Microsoft gave us a way with the command button wizards to open a form and show a specific record, but you can't do it with a report. So in this video, I'm going to show you two different ways that you can open a report and show specific data. Now first up, prerequisite. Go watch my video on how to open a form and show a specific record. That's what I just talked about a second ago. Go watch this and come back. While you're at it, I want you to also watch these other videos: the value from a form video. I showed you how to get a value from an open form; that's going to be required for this. Watch the blank template video where I build my blank TechHelp template. And, optionally, watch my intro to VBA video. The second technique I'm going to show you requires one line of VBA code. You just have to know where to put it. It's the best way to do it. The first way I'm going to show you requires these other two things, and it'll work. You don't need any programming for this, but if you know a little tiny bit of VBA, you'll see how easy it is. Of course, I always recommend watching my Access Level One class, four hours long, absolutely free. Go watch all this stuff first. So here I am in my TechHelp free template. This is a free download off my website. You can go grab yourself a copy if you want to. And if you watched all those other videos, you know that already. So in here, I've got a customer list. Now, like I said, Microsoft gives us a way to open a form and show a specific record. If I click on Will Riker here, I can click on this button and it opens up Will Riker's form. That is something, if we go into design view, is real easy to do. Grab a button, drop it down here, go to form operations, open form, and then you pick the form you want to open and then you find specific data to display. Let me cancel this. But there's no option to do that with reports. Watch. Report operation, let's go to preview report. Take one. And that's it. They don't give you an option for that. So we have to know how to do this ourselves, the hard way. Now, I don't have a customer report handy, so let's make one real quick. I've got my blank report that I made in my template video. I'm going to copy and paste that. We're going to call this my CustomerR. We'll use it to show one customer. Right click, design view. Now in here, we don't need a lot of this stuff. We don't need the report header. We don't need the page header. We don't need the footers. I'm going to click here, and I'm going to bind this to a record source. Let's bind it to the customer table. Let's put some controls in here. Now, a lot of people ask me, "Can't you just build forms and have those be printed out?" Yes, you can. But forms are really designed for working with on the screen. As I emphasize in my Access Beginner One class, you want to use forms on the screen. Reports are for printing out. But if you've got a form that you particularly like, like my customer form, there's no reason why you can't borrow the controls out of here. Go to design view and just copy what you want. If you want all this stuff, just draw a box that touches all this stuff like that. Switch over here to your report. I'm going to get rid of this label and just paste them in. There's no reason you can't do that. You can just take all the controls and this way you can format this to look like a nice report. I'm going to save this and close it. Let's open it up now. I'm going to right-click on it and go to print preview. Print preview is the best way to look at your report. It gives you a nice, realistic version of what it's going to look like when you print it or create a PDF out of it. Looks pretty good. That's my customer report. But as you can see now, I'm seeing every one of them. What if I want to see just a specific customer? Again, there's no way to do that with the wizard. You have to know how to tell this report, "Just show me record two, record three, whichever one you want." There are two ways to do it, like I mentioned earlier. The first way doesn't require any programming, but you have to know how to get a value from a form. So if we have, let's say, this form open, I need to know how to get that value right there. If you watched my value from a form video, which I told you to, you'll know it's Forms!CustomerF!CustomerID. So we can make a query that shows just this record. Let me close this. Let's make a query. Create, we're going to do a query design, bring in CustomerT, we can close that. I'm going to bring in the star and then bring in CustomerID and set the criteria equal to Forms!CustomerF!CustomerID like that. So we don't have duplicates, I'm going to turn off that show box. That way we don't see two of those fields. Save this as the CustomerQ, CustomerQ. If I close that and let's say open up a customer, let's go to this one. If I open up the CustomerQ now, look at that. It shows just "Dina". You have to have this form open, though. If you open up the query without that, it's going to ask you for that value. Anytime you see "Enter Parameter Value," that means Access doesn't know what you're talking about. It's a form field that it can't find, or usually you spelled something wrong. I have a whole video on "Enter Parameter Value." I'll put a link down below. Go watch that too. But now I have a query that I can use to show just one customer. So all I have to do is make this query the record source for that customer report. So go back to your customer report, right click, design view. Go to the report's properties. Change the record source to CustomerQ. Save it and close it. Now if I open up the customer report in print preview mode, I see just "Dina." Or if I go over here and open it, print preview, I'll see just "Dina." See how easy that is? You have to make a query that gets that value and shows only that record. Then you use that query as the record source for that report. Now, if you want to make a button for it, you can. We'll use the wizard. Drop a button down here. Report operations, preview report, CustomerR. We don't have to worry about showing specific data because the query takes care of it for us. Make that say "Print." Print button. There's a little print button right down there. Save it, close it. Go to the customer list. Open up Jean Luc and then hit the print button. And there it is. It's a report that gets its value from a query that gets its value from the open form, in a nutshell. Now I promised there was an easier way if you know one line of VBA. So for those of you who've watched the intro to VBA class and want to learn a teeny tiny bit of VBA, here's how you do it with a button with one line of code, without the need for that extra query or that report that's based on that query. You still have to have the report. Obviously, you've got to have something to print. But first, a quick word from our sponsor. That sponsor is me. If you want to learn more about this kind of stuff, Access Expert Level Five, I cover printing a letter report. We do something similar. We make an actual letter in a form and then we go to print it like that. It uses the same kind of techniques. That's Access Expert Level Five. I cover lots more stuff in this class too, over an hour long. Advertisements over. So let's basically undo everything we just did. I'm going to get rid of that print button. We'll leave room down here because we'll still put something there. I'm going to get rid of that CustomerQ. Goodbye, delete, see you. In the customer report, I'm going to design it and I'm going to change the record source back to CustomerT. There we go. So now if I open it up again, you can see everybody's back. The band's back together again. We've got all the records in here. So how do I print just a specific record? Well, let's make a button. Design view, grab a button, drop it down here. No wizard does this though, so cancel. This will be my print button. Slide it over here with the others. Open up the properties. Let's give it a good name: print button. Right click, build event, or go to events and then use the On Click event. Click the dot dot dot button. That'll open up your code builder. Here's my VBA window. One line of code. You can see there that I've got some other buttons that I made. These are open form commands. DoCmd.OpenForm what's the form name, a few parameters we don't need to worry about, and then a where condition. See that? It opens this form to this specific record where the CustomerID equals the current CustomerID in the current form. We can do the same thing. DoCmd.OpenReport what's the report name, CustomerR. How do you want to view it? We're going to go to acViewPreview (that's print preview), comma, we don't need a filter name. What we want is the where condition. The where condition is CustomerID = " & CustomerID. A little string concatenation there that says take the current CustomerID (whoever is on the form, Customer 7), put a 7 in there, and tack it on the end of that string. So what gets sent to the OpenReport command is CustomerID = 7. That works. Close it, close it, close it, open it back up again. Let's go to somebody, let's go to Jean Luc. Where is he? There he is. Print, boom, Jean Luc. Go back to the customer list, go to the D'Amicos, print, there you are. See how easy that is with just one line of VBA code? That's why I tell people, learn VBA. It's not that hard. With just a dozen or so commands under your belt, you can do all kinds of crazy cool things. You don't have to load up your database with tons of unnecessary queries. Learn a little bit of VBA, learn a little bit of SQL, and you'll take your Access database to the next level. I've got classes for all that stuff. I've got developer classes, SQL classes, whatever you need. Come to my website, you'll find it there. If you want to learn more in the extended cut for members, I will show you how to take a continuous form like the customer list and apply a filter to it, however you want to filter it. You can right-click and go "equals Florida" or "customers since after this date" or "the last name starts with R," whatever filters you want to apply. And then whatever filters are on that form will be how the report prints. Here I am in my customer list. This is a continuous form. Let's say I filter it to show just people from Florida. Right-click, equals Florida. Now when I go to print it, you can see only the people from Florida show up on the report. Turn the filter off. Maybe I want to see people whose last name starts with R. Right-click, begins with R. See that? Print. Now I get those people. You can apply multiple filters if you want to. We're going to basically use the filter property of the form to determine what gets printed when you print the report. That is all covered in today's extended cut video for the members. Silver members and up get access to all of my extended cut videos. Gold members can download these databases. How do you become a member? Click on the join button below the video. After you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series. Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more. You can now become a diamond sponsor and have your name or company name listed on a sponsors page. That will be shown in each video as long as you're a sponsor. You'll get a shout out in the video, and a link to your website or product in the text below the video and on my website. But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. QuizQ1. What is the main challenge the video addresses regarding Microsoft Access reports?A. Reports cannot display any data from tables B. Reports cannot be opened without programming C. Reports do not let you easily show a specific record like forms do D. Reports cannot be printed or exported Q2. What does Microsoft Access provide an easy method for via command button wizards? A. Opening a report for specific data B. Opening a form and showing a specific record C. Exporting a report to PDF D. Printing all records at once Q3. When using a query to display just a specific customer in a report, which of the following is used in the criteria row for CustomerID? A. "=[CustomerID]" B. "=Forms!CustomerF!CustomerID" C. "=CurrentRecord" D. "=SelectedCustomer" Q4. What happens if you try to open the query linked to a form value, but the form is not open? A. The report opens showing all records B. Nothing displays, and the window closes C. You are prompted to "Enter Parameter Value" D. The database crashes Q5. Why is it preferable to use reports for printing rather than forms, according to the video? A. Reports always print faster B. Reports are designed for screen use only C. Reports are specifically intended for printing and output, forms are for on-screen use D. Forms cannot contain controls Q6. What is the key advantage of using a query based on a form value as the record source for a report? A. No need for any forms in the database B. It lets you print just the record shown on the form C. It allows the report to display all related tables D. It creates duplicate records automatically Q7. What single line of VBA code can be used to open a report for a specific record? A. DoCmd.OpenForm "CustomerF", , , "CustomerID = " & CustomerID B. DoCmd.OpenReport "CustomerR", acViewPreview, , "CustomerID = " & CustomerID C. OpenReport "CustomerR", "CustomerID = CurrentID" D. Report.Open "CustomerR", CurrentID Q8. Compared to the query method, what benefit does using VBA provide for this task? A. It eliminates the need for extra queries and can be done with one line of code B. It opens all records by default C. It makes the report design more complicated D. It requires multiple steps and complex functions Q9. What must you remember to do when using either method to view a specific record in a report? A. Always close the database before running the report B. Ensure the relevant form is open to supply the correct value C. Delete all queries in advance D. Print all records every time Q10. In the extended cut for members, what additional feature is demonstrated? A. How to export a report to Excel B. How to filter a continuous form and print the filtered results in a report C. How to merge multiple reports into one D. How to use macros instead of VBA Q11. Who can download the sample databases from TechHelp videos? A. Platinum members only B. Gold members and above C. Silver members and above D. Any website visitor Q12. What is described as the best way to learn to do more advanced tasks in Access (like opening reports with specific data)? A. Watching advertisements B. Taking classes to learn some VBA and SQL C. Creating blank templates only D. Only using built-in wizards Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 7-B; 8-A; 9-B; 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. SummaryToday's video from Access Learning Zone covers how to open a report and display specific records in Microsoft Access. As your instructor, I want to clarify how this differs from working with forms. While Access makes it straightforward to open a form and show a single record using the command button wizard, doing the same with a report is more of a challenge. This is a topic that often confuses users, so I'll walk you through two ways to achieve it.The problem we are addressing comes from a common scenario. You have created a report, perhaps for your customers, but every time you print or preview it, you get data for all customers instead of just one specific customer. You may have noticed that Microsoft provides the ability to open a form and filter to a particular record using the built-in wizard options. However, when it comes to reports, that option simply does not exist. You may have tried to use the wizard, but it only lets you open the report without any filters. Before we go further, there are a few prerequisite topics I recommend you review if you are not already familiar with them: how to open a form and display a specific record, how to retrieve a value from an open form, how to create a basic blank template, and for the more advanced users, an introduction to VBA. The first solution I show does not require any programming, but a bit of VBA will provide a more efficient approach. And as always, if you are new to Access, my free four-hour Access Level One class on my website will help you get started. For today's example, I am using my TechHelp template, which you can download from the website. In my template, I have a customer list form. With a form, it is straightforward: you select a record and can easily create a button that opens just that customer's form. This is handled within design view by adding a button using the command button wizard and specifying the record to display. Unfortunately, when you try to do something similar with reports using the wizard, there is no option to display a single record—the wizard simply lacks the filter feature for reports. Let me show you how to work around this. First, if you do not already have a customer report, create one. I typically start with a blank report template and bind it to the customer table. Reports can be formatted similarly to forms; in fact, you can copy controls from a form you like and paste them onto the report to save time. Just remember, forms are optimized for data entry and screen interaction, while reports are designed to look good when printed. Once you have a basic customer report, previewing it will still show every customer. The goal is to narrow that down to just the customer you want. To do this without VBA, we can use a query that pulls the ID from the open form and feeds just one record to the report. The key is referencing the value on your open form using an expression like Forms!CustomerF!CustomerID in the query's criteria. That expression tells Access to filter for the currently viewed customer ID. Save and use that query as the record source for your report. Now, when you view the report, only the customer from the open form appears. If you want a print button on your customer form, set it up to open this report. The query handles the filtering for you. It is important to note that the open form must remain open; otherwise, Access will prompt for a parameter it cannot find. If you have ever seen the "Enter Parameter Value" popup, that means Access does not know where to pull the referenced value. Double-check your field references and make sure the form is open. Now, for those who are comfortable with a tiny bit of VBA, there is an even simpler approach. After removing that special query and setting your report's record source back to the customer table, you can add a button to your form and, in its On Click event, use one line of VBA to open the report and pass a filter condition directly. This single line tells Access to open the report but only show records where the customer ID matches the one on the form. This allows you to avoid cluttering your database with extra queries and accomplish the same result programmatically. Learning a bit of VBA is highly worthwhile. With just a handful of commands, you can make your Access applications far more powerful and efficient. If you need more training, my Expert and Developer courses cover these skills in depth, as well as SQL, which is equally valuable for handling data. For those interested in going even further, today's Extended Cut available to members explores how to apply filters from a continuous form—such as filtering by state or by customers added after a certain date—and transfer those filters to the report when printing. For example, you can filter your customer list on the form, and when you print the report, only the filtered records appear. This is accomplished by making the report reference the filter property of the open form. Members at the Silver level get access to all of my extended cut videos and a free beginner course each month. Gold members can download all the sample databases and access code from my videos, plus receive higher priority for TechHelp questions and a free expert class each month. Platinum members enjoy all the previous perks, as well as access to the full set of beginner courses for every subject on my website, and a free developer class each month. I also have other classes for Word, Excel, Visual Basic, and more. Although there are premium membership options, rest assured that my free TechHelp videos will continue as long as there is demand for them. If you want complete, step-by-step instructions and demonstrations for everything discussed here, you'll find the full video tutorial on my website at the link below. Live long and prosper, my friends. Topic ListCreating a report to display a single specific customerCopying controls from a form to a report Setting the record source of a report to a table Creating a query to filter records based on a form value Using Forms!FormName!FieldName in a query criteria Setting the report's record source to a filtered query Adding a button to a form to open a report Using the command button wizard for report preview Creating a print button without VBA Opening a report for a specific record using VBA Using DoCmd.OpenReport with a WHERE condition Constructing a WHERE clause using field values from a form Assigning an On Click event to a button for print preview Comparing non-VBA and VBA solutions for report filtering |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access open report, openreport, report filter, report where, display a single record in a report current record only print selected records print only one record, report criteria from a form PermaLink Open Report in Microsoft Access |
