Print One Label
By Richard Rost
3 years ago
Print Just One Label Anywhere on a Report Sheet
In this Microsoft Access tutorial, I'm going to teach you how to print just one label on a full sheet of labels in any location on that sheet. This is handy if you have previously printed labels and you have blank spots at the top of the sheet.
Allison from Woonsocket, Rhode Island (a Platinum Member) asks: It happens a lot in my office where I print maybe 10 or 15 mailing labels on a sheet that has 30 labels, and then I can flip it over and print a bunch on the other side. However, I'm always left with one or two labels left in the middle. Is there any way I can utilize those if I only need to print one label?
Members
There is no extended cut video (heck, this whole thing was one big extended cut LOL) 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, ms access print one mailing label, How do I print a single label in Access, print individual label
Intro In this video, I will show you how to print just one label at any position on a partially used sheet of labels using Microsoft Access Reports. We'll create a label report, set up a table for label data, and use a bit of VBA code to ask the user how many blank labels to skip before printing. You'll learn to design the report, handle single-label printing for specific records, and avoid common pitfalls, all with practical steps and sample code. You'll also get important tips about printer compatibility for this technique.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. In today's video, I'm going to show you how to print just one label anywhere on a sheet of labels in your Microsoft Access Reports.
You've got a sheet of labels that's already got some blank spots on it from labels you've already printed. Well, we'll click the Print Label button. It will ask how many blank labels you want. You'll tell it how many. It will leave that many blank spaces, and then you'll get your label. That's what we're doing today.
Today's question comes from Allison in Woonsocket, Rhode Island. I hope I pronounced that correctly. One of my platinum members. Allison says, it happens a lot in my office where I print maybe 10 or 15 mailing labels on a sheet that is 30 labels. Then I can flip it over and print a bunch on the other side. However, I'm always left with one or two labels in the middle. Is there any way I can utilize those if I only need to print one label?
Yes, Allison. Sometimes I'm in the same boat myself, or I used to be. I love to use Avery 5160 labels, that's the standard label format. It comes with 30 labels per sheet, 3 columns, 10 rows. Let's say you print a batch off and you use, say, eight labels on the top. Then you can flip it over and print the other way, and now those labels are all gone. So you've got all these labels in the middle. What do you do about those? How can you use those? These are good for just printing one off if you want to just send one thing out. I'll show you how to do that in this video.
Before we continue, I want to let everyone know, if you print a lot of single labels, like one label here, two labels there, not a lot, the sheet labels are good if you're doing a batch: 10, 15, 30, 500 labels. Okay, great. But if you're only doing one or two here and there, you send a correspondence here and put a package out, get yourself a Dymo label writer. They're great. They're really easy to use. They're not that expensive and they save you a lot of time. I have a whole video on how to use Dymo labels with Access. If you do this a lot, spend a few bucks and get one of these. But if you do print a lot of batches and you've got a lot of extra label sheets left over with a few in the middle, yeah, you can still use those too. Sometimes I'll peel them off and just write stuff by hand on them, like I'll label stuff in the fridge or whatever. But I'm going to show you how to use these labels in today's class.
I want to give everybody a warning that what I'm about to show you does not apply to laser printers. If you have an inkjet printer or a dot matrix printer (if you're still using those), you're fine. If you have a laser printer, do not do this. You should never feed a sheet of labels through a laser printer more than once. One time only. The reason is because laser printers use a lot of heat to basically bake the toner onto the page and make it stick there. What happens is, if you pass labels through that printer one time, they get baked. If you do it a second time or even a third time, that heat can cause those labels to peel off inside the machine. That's a very expensive repair. This happened a couple of years ago to one of my clients. It basically was toss the printer and be done. I'm not going to sit there and take it apart and he didn't want to take it apart. Repair shop's going to charge $300. You can get a new printer for $200. So don't do this with laser printers. Inkjets only.
To do this, as you saw on the first slide, this is a developer-level video, so you're going to need some VBA. There's no way around it. We're going to need a couple of lines of code, at least to ask how many labels you want there. I probably could put a solution like this together without that. We could use a form field. This is just one of those things that's easier if you know a couple of lines of code. We don't need a lot, like maybe 10 lines of code to do it properly.
Here are some prerequisites for you. First, of course, intro to VBA. If you've never programmed in VBA before, go watch this. It's about 20 minutes long, teaches everything you need to know to get started. You need to know what a delete query is. You need to know what an append query is. You should have a little background in SQL. I'm going to show you what you need in this video, but this will help if you have a little background in SQL first. Go watch this video, it's not too long.
We're going to use some variables. We're going to use a For/Next loop. We're going to use an input box to ask the user how many blank labels they want. There will be some confusing double double quotes and some concatenation, so go watch this video. And while you're at it, go watch this video on why I prefer double double quotes instead of single quotes. These are all free videos. They're on my website, they're on my YouTube channel. If you don't know anything that I just mentioned, go watch those first, then come on back. This isn't terribly complicated as long as you understand all of those prerequisites.
Again, I probably could put together a solution that doesn't involve VBA, but this is just easier. Once you know a little VBA, it becomes easier to do some things than to try to... Sometimes it's harder for me to try to figure out a non-programming way to do something. So this is worth learning VBA for. Trust me.
Here I am in my TechHelp free template. This is a free database. You can download it off my website if you want to. We've got a bunch of customers in here. These customers have addresses, and we're going to use these for some labels.
The first thing we're going to do is build a sheet of mailing labels that is set up exactly the way we want, with the label size and the template that you want - all that stuff.
Let's run through the label wizard, which I think is a good wizard. I like this wizard. It sets all the stuff up for you. You just pick your labels. I cover this in my Access Beginner 1 class, by the way. So if you've never made mailing labels before, go watch Access Beginner Level 1.
We're going to make mailing labels off of our customer table. So, create, and then the labels over here in the report section. I'm going to pick 5160s under Avery. That's just the size that I like. If you've got a different style you like, go ahead and find it. Use whatever you want. Next. All these settings are fine here. Next.
Now build your prototype label. It's going to be first name, space, last name, enter, address, enter, and then we've got city, space, or some people put comma space (that's fine), state, space, zip, enter, and then country. There's my prototype label. Next.
What do you want to sort by? That doesn't matter. Customer ID is fine. Whatever. Next. We're going to change it soon. Then we're going to give it a name. We'll call this my customer label R. All right.
It says some data may not be displayed. You get this a lot of the times. It's based on your printer settings. That's basically because the wizard sometimes makes this report too big, too wide. So all you have to do is come in here and just make this a little bit more narrow. You might have to slightly adjust these guys, make them a little bit smaller. But they should still fit. So save it, print preview, and okay, everything looks good now. Just make sure these still fit.
What I usually do before I waste a sheet of labels is print this out on regular paper, and then just hold it up in the light with a label sheet in front of it, and you'll be able to see what the lines are, and make sure everything lines up.
So there's our prototype. This looks fine. But what I want is to pick one person and insert blank lines (or blank records, I should say) in front of them. So we're going to use a separate table. We're going to make a label table, put that one record that we want in that table, and then insert blank records in the table as well. Then, when we sort it, it should sort the blank labels on top and put that label where we want. Get the process? That's what we're doing.
Let's close this down. Let's go over here and make a table. Create table design.
Label ID. That's my auto number. Do we need it? No. But it's a good idea. It's always a good idea to have an auto number on a table.
Then we're going to do line one, line two (these are all short text), line three, and line four. We got a four-line label. Could you put the separate fields in here - first name, last name, city? Yeah, you could, but why bother? These are going to be going right to the label. We'll just format the stuff we want and then send each line to this table. Get it? Save this. Label T. Primary key. Sure. There's our auto number.
Everything so far has been nice and simple. Don't worry. It's going to get harder. Just hang on a minute.
Manually put a record in here. Go to datasheet view. I'll put myself in here.
Rost
101 Wherever Street
Cape Coral, Florida 339
Leave line four blank if you're USA-like.
Now, save that. Now we're going to change that customer label report that we made so it gets its data from here instead of from the customer table.
Come in here, right-click, design view. First thing: change the record source. Come up here, and instead of Customer T, set it to Label T, and it should get rid of the filter and the order by - that's fine, we'll deal with that in a minute.
Now, as soon as I do that, I can change these guys, because the address doesn't exist, and these things aren't going to be there. The reason why I wanted to do this first (I know this seems like I'm doing some double work here) is because the report wizard, the label wizard, makes a nice starting place, because it sets all the margins up, it sets all the widths of the columns, so that's work I don't want to have to do manually. You can, but you don't want to. Let the wizard do some things for you.
Now I just have to change these to lines one, two, three, and four. So we'll come over here, go to all. This one's going to be line one. Don't forget to change your name right here. Line two. This will be guess what? Line three. I'm just copying and pasting that. Line four.
Save it. Close it. Let's take a peek. Preview it. Right-click, print preview.
This took me a second. I had to go off camera and figure this out. I normally don't use the label wizard. When I teach, I use it, but when I build reports myself, I almost never use it, so this snuck in on me.
When we ran through the wizard, I'll do it again real quick: labels, pick something first, Customer T again, create, labels, build the prototype label, sort by - now, normally, I don't put a sort in there, but when we were just goofing around earlier, I brought over customer ID, then I finished the wizard. I'll cancel this real quick.
What happens when you do that is it adds a sorting level to the report, but if you come into the report, go to design view, check your properties, you don't see an order by in here. I even tried setting that to none, that was by default, and so I checked; there's no sort in here. I checked it. There's no order by. Close it. But I'm still getting that. Why am I getting an enter parameter value? Now, I've got a whole video on why you get an enter parameter value. Go watch this video. More times than not, it's a spelling problem. People make a query, type in some criteria wrong, etc. Usually an enter parameter value is because Access is looking for something that doesn't exist. So something in this report is still looking for Customer ID. Where is it though? Where is it? It's a sorting and grouping level. Watch this: go up to report design, turn on group and sort, and ah, it's right here. Now it says "sort by expression" because customer ID is gone, I changed the record source.
If this wasn't a developer-level video, I would have erased all this and started over for the beginners so it's not confusing. But you are developers, so I want you to see this. These things happen to me, they're going to happen to you.
So, to fix that, all you have to do is delete that grouping level, that sorting level, and then the problem goes away. The wizard put a sorting level on that I wasn't aware of.
Tangent over. Now, I have to update my enter parameter value page too, let people know about that. See, I've been doing this stuff almost 30 years, and I still learn something new every now and then from just simple little mistakes. And yeah, the label wizard isn't something that I use a lot. I teach it in my beginner classes and then I never use it myself. So, now that we got rid of that, I should be able to go to print preview and oh, there it is. There's my one label now.
If I were to add additional blank labels to this table, blank records, let's say you want to put this label in position four, add some blank labels. Now to add a blank label, the easiest thing to do is just type a space in there. Space, space, space. I added three blank labels. Save it. Close it. Take a look again, print preview. That's still in position one. So what do we have to do here? Well, just sort it based on whatever you want to sort it on.
You could use the ID, sort it in reverse order by the ID if you want to, or sort it by one of these fields. I'm going to sort by line one. You just have to always guarantee that there's something in line one, because IDs are tough since IDs can go not in ascending order. It's possible, especially if you're in a multi-user database. You might have someone working on it at the same time you are. So, I would just sort it based on one of the lines.
We're going to come in here in design view, back into here, and use that Order By we talked about before. We're going to order by line one, and that should put the blank records on top. You might want to put some code in there to prevent them from adding a customer if there's no first name or last name. Right-click, print preview, and there we go. Now we're sitting in position four.
Now here's where the good stuff comes in. Here comes the programming part of it. We're going to make a button on the customer form. Right here on the customer form, you're going to print one label with this guy, but you're going to ask the user first how many blank labels do you want. This will be a special button for just printing one person at a time.
This button's got to do a bunch of stuff. First, we have to ask the user how many blank labels they want. Check to make sure they gave us a valid response. Then we're going to delete whatever is in that label table, because you have to delete any existing records that are in there. We're then going to insert into the table with an append query, insert into the four lines that we want from our customer - first name, last name, address, city, etc. Then we need a For loop to add the blank records. Then we're going to open the report. All that will happen in one button.
Are you ready? Get your programming caps on. Here we go.
Design view. Make a button. I'm just going to copy this one, copy/paste. Print Label. We'll make this guy bigger. We'll give it a good name: PrintLabelButton. Thank Alex for that. I didn't use to name my buttons until Alex kept yelling at me and now he guilted me into it. But he was right, you should name your button - name all your objects that you might possibly have to call later on. Some things like these labels I don't bother with because I almost never call them. But if I'm going to do something like change colors or fonts or whatever in code, I give them names. I will never refer to Label23. If I'm going to call this guy the first time, I'll call it EmailLabel or something like that.
Anyways, you can tell I'm recording this video late because I'm leaving for a trip, so I'm trying to get one more video in before we leave. It's been a long day and I've had a lot of coffee and I swear I'm okay.
Programmer time. Build event: PrintLabelButton_Click. Here we go.
We are going to need some variables. We need a variable to store the input box answer. When you get data from an input box, it should always go into a string, always Dim as String. But we need a number because we have to count how many blank labels to add. That's going to be a number, a long integer. So we're going to need "howMany as Long" and we're going to need a loop variable to count for our For/Next loop, right? For x = 1 to howMany, so "x as Long". So there's our variables.
Next, let's ask the user how many blank labels they want.
s = InputBox("How many blank labels do you want to add before this record?", "Print Label", "0")
Default value: I'm going to put a zero in the box - and yes, you can usually just put a zero there. With input boxes, I want you to be thinking strings. It will convert to a string. That zero is going to be in the box; that's fine because we're going to convert it in a second.
Now, if the user clicks Cancel (because remember, input boxes have OK and Cancel), if they hit Cancel, then it returns an empty string. So, right here, we're going to say: If s = "" Then Exit Sub. That means they hit cancel, they didn't mean to click the button.
Now, I want to check to make sure that the value they entered is numeric. I don't want them to enter in "cat". So: If Not IsNumeric(s) Then Exit Sub. IsNumeric checks a string value and says, is that a real number? Is that valid? Exit Sub if they entered something like "Jean-Luc Picard". That's awesome, but no.
At this point, we've got a valid number in s. So I can now say: howMany = CLng(s). That's "Convert to Long". I just did a video on type conversions a couple days ago. Go watch this.
Now, if you want to put any kind of parameters on that number, now we have a number. Check and say something like: If howMany < 0 Then Exit Sub. If they type in a number that's crazy high, like 5000, whatever, you can put that in there as well. If you want to give them error messages, you can do that too. You could say: MsgBox "Must be zero or more" then Exit Sub, if you want to be nice instead of just exiting without telling them. That's up to you.
Now we have a valid number from zero to whatever. Now it's time to actually do the work.
The first thing we're going to do is clear the label table. "Label table, label table," I like that. CurrentDb.Execute "DELETE FROM LabelT". That's a delete query in SQL. I cover that in more detail in my SQL seminar and several other developer lessons. I like to use CurrentDb.Execute. It's got some benefits over DoCmd.RunSQL. There's pros and cons with either one. I like CurrentDb.Execute. I have a video coming out soon on the differences between the two and why you want to use one over the other. But for now, just go with this.
Now, the next line is the tough one. This is the one where we insert the lines into our label table. I find that it's easier to write it first as an actual SQL statement and then copy it over. It's going to look like this:
INSERT INTO LabelT (Line1, Line2, Line3, Line4)
VALUES ("Richard", "Rost", "101 Main Street", "Cape Coral FL 339", "")
That's what it looks like as a valid line of SQL. We have to convert this over to a proper VB string. So it's going to look a little different.
CurrentDb.Execute
The first line is easy because it's pretty much valid as it is, so like that.
Now, this is where it gets tricky because all of this has to go inside a string. So, use quotes, and everywhere inside here you see a quote, that has to be doubled (""). If you want to use fields and variables, replace the literal values with concatenated field names.
This honestly is one of the reasons I kind of prefer recordsets sometimes. Recordsets honestly are a lot easier than SQL sometimes. SQL does often work easier if it's simple things, but something like this can be a little crazier.
So let's test this, save it, come back out here, and click the button. This hasn't been programmed yet, so that's not going to do anything. Check the table. It deleted what was in there and inserted those values into those lines.
Now comes a tricky part. Now, instead of "Richard" and "Rost", I need to convert each one of these into the fields that belong there instead. "Richard" is going to be FirstName, so use code like: " & [FirstName] & " etc. Replace each value with the appropriate field reference.
Same thing here for address, city, state, zip, and country. Build your SQL string dynamically.
To add the blank label lines, the easy part: For X = 1 To howMany
CurrentDb.Execute "INSERT INTO LabelT (Line1) VALUES ("""")"
Next
When done:
DoCmd.OpenReport "CustomerLabelR", acViewPreview
Save it. Debug Compile. Everything looks good. Close it. Test it.
Let's go to somebody else: William Riker, Print Label, how many blanks you want? Give me 3. Hit OK... boom, there you go. Three blanks up top here.
Try Wesley Crusher, Print Label, give me 7 blank labels. Boom. Seven blanks followed by the label.
You can do all this programmatically. Like I said, it's not terribly complicated. This is a pain; this really is a pain right here, I will admit it. You could do this with a query, too.
If you don't want to do something crazy like this, you could build a query based on this guy. Create, query design. CustomerT, set criteria to currently open customer ID = Forms!CustomerF!CustomerID. Build your calculated fields and turn it into an append query into LabelT.
Save and run it. That makes attaching the right label easier. You can trigger this query from your code instead of assembling the string yourself.
The query method is easier than all that string building. I just try not to have a million queries in my database. Sometimes I fire through this quickly on my own when I'm putting together a video, but explaining it is a little more difficult - so that's why I wanted you to watch all those prerequisites, too.
I've talked your ear off long enough.
Some extra stuff: I have a Mailing Label Seminar where I show you this and lots more. We do stuff where I show you how to add multiple blanks, you can make multiple copies of blank labels, you can make a full page of the same label, you can add blanks before a batch of labels, lots of different methods. It's about an hour long and I cover a bunch of other stuff, and it's pretty cool.
If you want to learn more about SQL as it pertains to Access, I have SQL seminars available. Part one covers all the basics - select statements. The crazy stuff that we did today with the quotes and all that, that's in part two, where we do append queries and delete queries and union queries, which is something you can't do with the graphical editor.
Of course, come check out my Access Developer lessons. I have lots and lots of stuff, starting from the beginning all the way up. I just finished Level 43 a couple days ago.
If you don't want to go through all this, just get yourself a Dymo label writer. Print them off one at a time that way. I get it. I used to print a lot of labels when I used to ship CDs. I used to ship hundreds of CDs a week and I would always have label sheets left over with like three labels in the middle of them. So that's when I started doing this myself. So, I get it.
I can always tell when I record a video at night, because when I record at night, I tend to go off on more tangents. It's about 11 o'clock at night. Usually, I record in the morning, do my customer service, have my coffee, record my video around lunchtime. But if I record at night, especially after a couple of cups of coffee, they go long. This is the longest one I've done in a while that wasn't an extended cut.
So, that's going to be your long-winded TechHelp video for today. I hope you learned something, I hope you had some fun. I know I did. Even though it's long, I still have a lot of fun doing these. I wouldn't trade my job for anything. Live long and prosper, my friends. I'll see you again soon.
Now, if you really want to learn Access and you haven't tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below; click on it. And did I mention it's completely free? The whole thing. Free, four hours. Go watch it.
A lot of you have told me that you don't have time to sit through a four-hour course. I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster in about 30 minutes. No, I didn't just put the video on fast forward, but I'll put a link to this down below as well.
If you like Level 1, Level 2 is just a dollar. That's it, one dollar. And that's another whole 90-minute course. Level 2 is also free for paid members of any level, including supporters. So if you're a member, go watch Level 2, it's free.
Want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. I do try to read and respond to all of the comments posted below in the comments section, but I only have time to go through them briefly a couple of times a month. Sometimes I get thousands of them. So, send me your question on the TechHelp page and you'll have a better chance of getting it answered.
While you're on my website, be sure to stop by my Access forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Wan, and everybody else who helps on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now, let's talk more about those member perks if you do decide to join as a paid member. There are different levels: Silver, Gold, Platinum, and Diamond.
Silver members get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks. Gold members get all the previous perks, plus access to download the sample databases that I build in my TechHelp videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Answers are never guaranteed, but you do go higher in the list algorithm. And if I like your question, you have a good chance of it being answered. You'll also get one free expert-level class each month after you've finished the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
Finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on 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.
That's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.Quiz Q1. What is the main topic of the video tutorial?
A. How to print a single label anywhere on a sheet using Microsoft Access Reports
B. How to create labels using Microsoft Word
C. How to design custom label templates in Adobe Illustrator
D. How to troubleshoot printer connectivity issues
Q2. Why should you avoid running a sheet of labels through a laser printer more than once?
A. The toner will not stick properly on the second pass
B. The heat can cause the labels to peel off inside the printer, risking expensive damage
C. The printer will jam due to different label thickness
D. Ink will smear, making the labels unreadable
Q3. Which printer types are safe for re-feeding partially used sheets of labels according to the video?
A. Only laser printers
B. Both inkjet and laser printers
C. Inkjet and dot matrix printers
D. Only label writers
Q4. What is the recommended solution if you regularly print just one or two labels at a time?
A. Use a Dymo label writer
B. Always use a full sheet of labels
C. Handwrite the labels
D. Use Avery laser labels exclusively
Q5. In the tutorial, what is the purpose of inserting blank records into the label table?
A. To print labels with more information
B. To skip blank positions and align the printed label on the desired spot on the sheet
C. To prevent errors in the report
D. To enable batch printing only
Q6. What is the function of the LabelT table in the solution?
A. It stores all customer records permanently
B. It temporarily stores specific label data and blank records for printing
C. It stores printer settings and preferences
D. It is used to archive unused labels
Q7. Which Microsoft Access feature/Wizard does the tutorial recommend for initially creating the layout of the labels?
A. Table Design Wizard
B. Form Wizard
C. Report Label Wizard
D. Query Wizard
Q8. What is the main advantage mentioned for using the label wizard when creating label reports?
A. Automatically sets up margins and column widths correctly
B. It can print directly to a Dymo label writer
C. It eliminates all need for coding
D. It creates complex SQL queries automatically
Q9. How does the solution ensure the correct label appears in the intended position on the sheet?
A. Labels are sorted alphabetically by last name
B. Blank records are inserted before the intended label, and sorting by a selected line ensures label placement
C. The report filters on a specific customer ID
D. It uses the sheet position selected by the user at print time
Q10. According to the tutorial, what programming method is necessary for this label solution?
A. Macros only, no VBA required
B. VBA code using variables, loops, and SQL commands
C. Manual changes in datasheet view
D. No programming is required at all
Q11. What is the purpose of using an input box in the VBA code for printing labels?
A. To let the user select the printer
B. To ask the user how many blank labels to skip before printing
C. To enter the label text manually
D. To choose which table to print from
Q12. When adjusting the report to use the label table, which fields should you use in the report controls?
A. FirstName, LastName, City, State
B. Line1, Line2, Line3, Line4 from LabelT
C. Address1, Address2, Country
D. CustomerID, AddressID, ZipCode
Q13. If the user enters a non-numeric value in the input box, what does the code do?
A. Prints the label in the first position anyway
B. Ignores the value and defaults to zero
C. Exits the subroutine without making changes
D. Sets the number of blanks to one
Q14. What happens in the solution before a new label is inserted into LabelT?
A. Existing records in LabelT are deleted using a delete query
B. All tables are compacted and repaired
C. All records are backed up to another table
D. The user approves each record individually
Q15. What is the recommended approach if you would prefer not to write dynamic SQL in VBA string concatenations?
A. Do everything manually
B. Use a saved parameter append query and trigger it from code
C. Switch to a different database system
D. Only use macros for all actions
Q16. What is the main benefit of naming controls (like buttons) in Access forms?
A. Makes them easier to refer to and manage in VBA code
B. It is required by Access to run code
C. It increases database performance
D. The names appear on printed reports
Q17. What type of Access user is this solution best suited for, based on the video?
A. Someone who has never used Access before
B. Beginner-level users comfortable only with forms and tables
C. Users with some knowledge of VBA and queries (developer-level)
D. Users who only use macros, never VBA
Q18. If you want to print the label in position 5 on the sheet, how many blank records should you insert into LabelT?
A. Four blank records
B. Five blank records
C. One blank record
D. No blank records
Q19. What key prerequisite skills or concepts are helpful before following this solution?
A. Using Access macros and ribbon customization
B. Understanding delete queries, append queries, and basics of SQL
C. Knowing how to set up network printers
D. Using Excel lookup functions
Q20. According to the video, what is usually the cause when you get an "Enter Parameter Value" prompt in Access?
A. Syntax error in VBA code
B. Spelling mistake or reference to a field or object that does not exist
C. Network connectivity problem
D. Wrong printer selected in Access
Answers: 1-A; 2-B; 3-C; 4-A; 5-B; 6-B; 7-C; 8-A; 9-B; 10-B; 11-B; 12-B; 13-C; 14-A; 15-B; 16-A; 17-C; 18-A; 19-B; 20-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 focuses on how to print a single label in any position on a sheet of labels using Microsoft Access Reports.
Many of us run into a common issue: we have a partially-used label sheet, and we want to print just one or a few labels in specific spots. If you have label sheets with random blank spaces and want to use them efficiently, there is a straightforward way to leave any number of blank labels before the real label prints. When you hit the Print Label button, you simply specify how many blanks to skip, and Access handles the rest.
This question is a practical one and comes up often, especially in offices where you might print packs of 10 or 15 out of a 30-label Avery 5160 sheet. After flipping label sheets and using more labels, you're left with onesie-twosies that are hard to use. If you want to print a single label and use those odd spaces, today's tutorial will show you how.
Let me add that if you frequently print just one or two labels at a time, you might want to invest in a dedicated label printer like a Dymo label writer. They are economical, fast, and easy to use, especially for sporadic printing. I have a separate video on using Dymo printers with Access. However, for batch jobs (10, 15, or more), or for making use of those leftover labels on standard sheets, this approach is perfect.
A very important note: this technique works well with inkjet and dot-matrix printers but should NEVER be attempted with a laser printer. Laser printers use heat to bond toner to sheets, so running a label sheet through more than once can cause labels to peel off inside the printer. This can damage your printer beyond reasonable repair. I have seen this happen, and replacing or repairing a laser printer is not cheap. So, only try this method with inkjet or dot-matrix printers.
For this process, you'll need some basic VBA skills since there isn't a purely non-programmatic way to achieve this efficiently. You only need about ten lines of VBA code, so it won't get overwhelming, but it does require some knowledge. You should be familiar with VBA basics, delete and append queries, a little SQL, and concepts like variables, the InputBox function, loops, and concatenating strings with quotes. I have free videos on all these topics if you need a refresher.
We will start with the TechHelp free template database, which you can download from my website. Suppose you have a set of customer records, each with necessary address information. The first task is to create a report for mailing labels using the standard label report wizard in Access. The Avery 5160 format (30 labels per page) is a good starting point, but if you use another format, just select the appropriate size.
The wizard handles most of the setup, such as margins, columns, and prototype label content (first and last name, address, city, state, zip, etc.). After the wizard completes, you might need to adjust the report's width if your settings pop up warning messages about data not displaying properly. Always test your labels on regular paper before using an actual label sheet; you can hold the sheet up to a window to check alignment.
Once you have a working prototype, the next task is to enable printing a single label at a specific position. To do this, create a separate table for labels. This table should simply contain an auto-number primary key and four short text fields for each label line. You can input a record manually first to test things out.
Next, update your label report so that it pulls data from this new label table instead of the customer table. The report wizard is helpful because it configures the basic layout, but you'll want to change the detail controls in the report to pull from your new table's fields. For example, set the text boxes to show lines one through four from the label table.
Sometimes, switching the record source of the report leads to "Enter Parameter Value" messages due to hidden sorting or grouping settings that reference old fields. Use the Group and Sort feature to remove any reference to now-missing fields like CustomerID.
To allow Access to print a label at any position, simply add as many blank records to your label table as the number of labels you wish to skip. These blank records can have a space in line one to ensure they are recognized as non-empty records. If you want your label to appear in position four, just ensure there are three blank records ahead of your actual label record.
Ordering is another detail to keep in mind. It's often simplest to sort the labels by one of the lines, typically line one, so that the blanks appear first. If you are in a multi-user environment where record IDs could get out of order, this approach is more reliable.
Now for the programming part. Put a button on your customer form labeled "Print Label." When clicked, this button should (1) ask the user how many blanks to insert, (2) clear out the current label table, (3) add the current customer's information as a record, (4) append the specified number of blank records in front, and (5) open the label report in preview mode.
You will use some basic variable declarations, an InputBox to ask how many blank labels to add, and validation to ensure the user enters a proper number. A For Next loop will handle the addition of each blank record. The code will also use an append query or a deduced SQL string to insert the actual label data and any blanks required.
When you're finished, clicking the Print Label button for a customer allows you to enter the desired position. If you type "3," three blanks are added, followed by the actual label, so you skip right to position four on the sheet. Everything is handled in code behind the button for a smooth workflow.
As an alternative to building the SQL string in code, you can create a saved append query that inserts the correct label information and reference the open form's customer. Sometimes, building a query up front is easier than building complex strings in code, and you can call that query from your VBA button click handler.
If you want to explore further, I encourage you to check out my Mailing Label Seminar, which gets into additional scenarios like printing pages of identical labels, managing label sheets in various ways, and working with batches. For deeper SQL training, I have dedicated seminars and developer courses covering everything from simple select statements to advanced queries.
If this all feels like a lot just to print the odd label here and there, it might be time to get a dedicated Dymo printer. For many years, this was a lifesaver when I needed to print individual shipping labels and hated wasting partial sheets.
Thank you for sticking with me through this detailed walkthrough. If you'd like to learn more or revisit the step-by-step process, you can find a complete video tutorial with all the instructions on my website at the link below.
Live long and prosper, my friends.Topic List Printing a single label in any position on a label sheet
Using VBA to add blank labels before a record
Creating a label table for individual label printing
Modifying the label report to use a custom label table
Using the Access Label Wizard to create reports
Adjusting report sorting and grouping for label position
Identifying and removing unwanted sorting levels in reports
Manually adding blank records to position labels
Dynamically setting the report data source for labels
Building a Print Label button with VBA
Prompting the user for the number of blank labels via InputBox
Validating InputBox responses for numeric input
Deleting records from the label table with VBA
Appending a specific record to the label table with VBA
Building dynamic SQL statements for appending data
Using a For Next loop to insert blank label records
Opening the label report in Print Preview with VBA
Alternative method using append queries for label selection |
