Hide Pricing
By Richard Rost
20 months ago
Display or Hide Line Item Invoice Pricing in Access
In this Microsoft Access tutorial, I will show you how to toggle line item pricing on invoices with ease, perfect for creating detailed or summary invoices. We'll add just a touch of VBA to make your reports more flexible, avoiding the hassle of maintaining separate invoice templates. A practical solution for enhancing your database's invoicing capabilities.
Ivan from Paramus, New Jersey (a Platinum Member) asks: How can I make my invoices in Access show prices for each thing we sell, but also hide those prices when I need to? Like, if I want to give a customer a summary invoice without all the details of what each item costs. Can I do that in the same report or do I need to make two different ones?
Members
There is no extended cut, but here is the database download:
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
TechHelp Access, on format, onformat, show hide line item pricing, invoice customization, VBA programming Access, conditional visibility report, toggle price display Access, Access report controls, modify Access reports, detail section on-format event
Subscribe to Hide Pricing
Get notifications when this page is updated
Transcript
In today's video, we're going to see how to show or hide the line item pricing on your invoices, whichever one you want. If you want to show them, we'll show them. If you don't want to show them, don't show them. It's okay. It's alright. We'll see how to do that in your Microsoft Access database.
Today's question comes from Ivan in New Jersey, one of my Platinum members, and yeah, I'm done trying to pronounce all your town names and city names because I get them wrong, and then I have to hear about it in the comments. "Oh, you pronounced it wrong." Okay, fine. Ivan from New Jersey. He says, "How can I make my invoices in Access show prices for each thing we sell, but also hide those prices when I need to? Like if I want to give the customer a summary invoice without all of the details of what each line item costs, can I do that in the same report, or do I need to make two different ones?"
Well, if you're a beginner, making two invoices is the easiest way to do it. Just make one with and one without the line item prices. But with a teeny tiny little bit of VBA programming, you can use the same report and just hide them based on whether you want to show those or not. So in this video, we'll do it the proper way. This will be a developer video. What does that mean? That means if you haven't done any VBA programming yet, go watch this video. It's easy. It's about 20 minutes long. It teaches you everything you need to know to get started. Make sure you understand how to use if-then statements.
And if you have not yet watched my invoicing video, go watch this. I teach you how to make the order form, the order detail form, and the invoice report that we're going to be using in today's video. These are all free. You can watch them on my website, on my YouTube channel. Go watch them, and then come on back.
Alright. So here I am in my "TechHelp" free template. This is a free database. You can grab a copy off my website if you want to. And in here, we have customers, and customers can have orders with order details, and each of those can produce an invoice. Now, in the invoice that we have right now, you can see we've got the quantity, the unit price, and the extended price right there. You might not want to see all this detail for every invoice that you print. I've worked with a lot of companies in the past where they like this so that they can generally put together a rough outline of what something is going to cost, but they don't necessarily want the customer to see all these details. And in fact, what one customer used to do is he'd generate this and then he'd put a labor item in here, and that's where he would use, he'd use this as like a sponge factor to put in, you know, how much extra he wanted to mark it up or if he wanted to put a discount in here, and that way the user didn't know exactly what, the user, the customer didn't know exactly what all the details were, but he got the total price.
Now like I said, it would be totally possible to make a copy of the order invoice. Right, take this guy here, make a second one, call it, you know, order invoice no line item pricing, and just delete these fields from it, right, if you're a beginner, and then you can just make a second button in here that says invoice no line item pricing and just print that one up. But the trouble with that is, in the future, if you decide to make enhancements and upgrades and changes to this thing, you've got to do it in both of those. I hate having to do that. I don't want to keep track of maintaining two separate invoice reports.
So really, all we have to do is just add another field to our table and our form so we can track whether or not you want to print the line item totals for this invoice. That way, each invoice you can keep in here separately. Some you might want to, some you don't want to. Then what we'll do is we'll just hide these things depending on the value of that field.
Alright, so let's start off with putting a field in our order table, this guy, design view, and we'll put in here something like, let's call it show line pricing, like that. Alright, that'll be a yes, no value. I'm going to set the default value to yes. And of course, this is completely up to you. These are your Legos, put them together however you want. If you like to show the details on most orders that you print out, then set that to yes. If you hide them most of the time, leave it no. That's completely up to you.
Alright, let's save that, Ctrl + S and go in here and obviously, set the ones that you want to see the line item pricing for as true. If you've got lots of them, you'll have to use an update query. If you don't know about update queries, there you go. There's a video.
Alright, so let's close this. Let's go back to our order form. Bring up the design view here. And we'll need to add that field to our order form. I'm just going to make this a little bit smaller. And let's just copy this is paid. Copy, paste. We'll slide it maybe down here. And we'll call this guy, control source is show line pricing. Move this up so you can see it better. We're going to copy that and paste it in the name field too, so it's called the same thing. Control source is where it gets its data from, the name is the name of this box itself, and I like them to be the same most of the time.
Alright, let's save that. And then we'll just change the caption here too. Show line pricing. That could be whatever you want it to be.
Alright, let's save that, close it, reopen it again. Now, with that off, when I open up my invoice, I want these fields here to hide themselves if this is no or false. So how do we do that? Well, a little tiny bit of VBA code.
Alright. Here are the fields we're most concerned with. We'll deal with the labels later. Right, these guys here, the unit price, and the extended price are in the detail section. Okay. Now, on a form, we have something called the on current event. The on current event runs when you move from record to record, when you open the first record up, that kind of stuff. It's called the on current event. You can learn more about that here. In a report, we have something called the on-format event. If you open up the properties for the detail section, go to events, you'll see on-format. That is code that writes as that section is being written, as it's being displayed on the screen or printed or whatever. It's called on-format. We're going to go into here. Alright, dot, dot, dot, bring up the builder event. There's our VBA code builder. We're in the detail format section. We're going to say if show line pricing, then do some stuff, else do some other stuff. What are we going to do? Well, we're going to say if I want to see the line pricing, we're going to say unit price dot visible equals true and extended price dot visible equals true. Those are the names of those two fields, those two text boxes. Right? Otherwise, we're going to set that equal to false.
Okay. Save it. Let's come back to our report. Let's close that and open the report back up. And oh! Oh! Look what happened! I got variable not defined. What does that mean? Variable not defined. That means that the report has no idea what show line pricing is. Now wait a minute though, isn't that a field in the table under the report. What's going on here? Let's stop. And by the way, this is one of those good examples of why it's usually a nice idea to hit debug compile. Here, watch this. Right, debug compile and it would have caught it right away. That's why I always say throw in a debug compile.
Now, usually with forms, you can get away with not having the control on the form itself as long as it's in the underlying record set, as long as it's in the table or query that's underneath the form. Now this report gets its data from order invoice Q. If we look at order invoice Q, you'll see that it's right there, show line pricing, see? And for forms, that's usually good enough, but you know what, it's not for reports. Reports really want that control to be on the report itself. Otherwise, it's going to give you that, it has no idea what you're talking about.
So we're going to go to report design and add existing fields, and we're going to find that field right there, show line pricing, drag it, drop it, somewhere up here in the report header is fine because remember the report header is stuff about the entire report itself. As long as it's on here somewhere it's fine. I'm going to delete the label and I'm going to take this box here and I'm going to go to format and make it not visible. We don't care to see it but we just have to have it on here somewhere.
Okay, alright now save it, close it, close it and open it back up again and there we go, look at that. And now it's working. See, and now we don't see those prices over there. Let's do the same thing with the labels. Now the labels right here, when we made these labels, unfortunately, we didn't give them good names. It's just label 15, label 17, whatever they're called. So first thing is let's give them good names. to be the unit price label and this guy will be the extended price label. Extended price label. Label, I can type that. Now, they're in the page header, so guess what? We have to put that in the page header's format event right there. Dot, dot, dot. And look at this. Now, watch. Watch this trick. You ready? We're just going to take this stuff up here. We're going to copy that, we're going to paste it down here, and then we're just going to change this to label, just like that. I'm going to copy that right there. Copy, label, paste it, paste it, paste it there, paste it there. Alright, debug, compile. And by the way, earlier, I intentionally typed this in all caps because I knew what was going to happen, but one key that would have given it away is if you did if show line pricing and just press, you know, and then, and then press Enter, that wouldn't have capitalized itself because of the automatic camel casing. Now it does, of course, because we added it.
Alright. Save that. Debug compile, always a good idea. Alright. We're good. Come back over to here. We're going to close that, close that, open her up, and there go the labels. See? Now, it looks kind of funky having this empty space over here, so what I like to do is if you're going to be doing this with your stuff, I'm going to move over the products like this. And then we're going to put the... I moved the line too. Hang on. Undo. Don't move that line. Let's move over quantity first. Quantity. And we'll slide them over here. And then we'll take this. I'm going to hold the shift key down. Shift, click, click. And then we're going to drag those over this way and then we'll just left-align this stuff. Alright, make sure you're left-aligned, left-aligned, save it, close it, and now you've got your quantities over on the left, and that looks a lot better.
Alright, could you have it both ways? Yeah, you could. You can put two quantity fields on here if you want the quantity to still be over here when you do show the pricing. Just, I would put two quantity fields on here and just show whichever one you want to show. You can even move these fields with VBA code. All kinds of stuff you can do, folks. But this is a whole lot better than trying to make two reports and then maintaining two separate reports.
Now if you want to learn more about this invoicing stuff, I spend a lot of time over the course of my Expert and Developer lessons working on invoicing and order entry. I just picked Expert level 10 because it's just one of them. But it's one of the main themes of my entire course is building a database that you can use to run a small business, especially a retail business. That's the one that I use because I think most people can kind of get that concept of like the lemonade stand, right, a normal retail type business. But invoicing is a lot of it. We spend a lot of time working on this. And even in the extended cut for my invoicing video, which is available for members, we cover a lot of other cool stuff. For example, I'll show you how to set up a product list, where you've got your list of products, and then when you go to make orders, right, you can then pick a product and then hit add and add it to the order like that. All kinds of extra cool stuff we do in the extended cuts for the members.
But there you go, that's how you can show or hide the pricing for your line items and your invoices in your Microsoft Access database. That's your "TechHelp" video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Show or hide line item pricing on invoices
Use Microsoft Access database for invoice management
Considerations for beginners vs. using VBA programming
Adding fields to a table in Access
Setting default values for new fields
Use of update queries in Access
Modifying forms in Access
Adding control sources and name fields
Incorporating yes/no fields for user input
Using VBA code to hide or show report items
Understanding on-format event in Access reports
Creating and naming labels for report clarity
Using the debug compile feature in VBA
Aligning and positioning fields in reports
Dynamically adjusting report layout with VBA
Maintaining a single report versus multiple reports
Extended functionality in invoicing for members
Adding products to orders in Access
Using Access for small business retail management
Quiz
Q1. What is one method mentioned for hiding line item pricing on an invoice for beginners using Microsoft Access?
A. Adjusting report display settings for each invoice printout
B. Creating two separate invoice reports, one with and one without the pricing
C. Implementing conditional formatting rules on the invoice template
D. Using a password-protected section to toggle the visibility of prices
Q2. What programming language is used to customize the invoice report to show or hide the line item pricing?
A. SQL
B. JavaScript
C. VBA
D. Python
Q3. What is the recommended step before you can customize the invoice report to show or hide line item pricing?
A. Understanding if-then statements in VBA programming
B. Learning to create macros in Access
C. Creating a complex query for invoice data
D. Enabling advanced settings in Access Options
Q4. What is the primary reason the author prefers to use VBA coding to hide line item pricing rather than creating two different invoice reports?
A. It is less time-consuming to code in VBA than to adjust report fields.
B. Changes and enhancements can be applied in one place rather than maintaining two separate reports.
C. Access does not allow more than one type of invoice report.
D. VBA coding is required for creating invoices in Access.
Q5. Which event is used in reports to run code for hiding or showing fields based on certain conditions?
A. On Load
B. On Click
C. On Open
D. On Format
Q6. What must be added to the report in order for the VBA code to recognize the "show line pricing" field?
A. A new query
B. An underlying table
C. A text box with the "show line pricing" field on the report
D. An exclusive VBA module
Q7. What is the purpose of setting the default value for the "show line pricing" field in the order table?
A. To ensure that the pricing is shown unless manually turned off
B. To automatically apply discounts to all invoices
C. To link the invoice report to the customer table
D. To standardize the report layout for printing
Q8. How should labels for unit price and extended price be made visible or invisible?
A. By setting their font size to 0
B. By changing their color to match the background
C. By using VBA code to toggle the 'Visible' property
D. By permanently deleting them from the report
Q9. In the video, what does the speaker suggest adding to the report to better maintain the layout when line item prices are hidden?
A. A placeholder field
B. An additional quantity field
C. A subreport
D. A graphical element like a line or a box
Answers: 1-B; 2-C; 3-A; 4-B; 5-D; 6-C; 7-A; 8-C; 9-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.
|
