Shipping Label 2
By Richard Rost
2 years ago
Print Shipping Label for Separate Ship To, Part 2
In this Microsoft Access tutorial, I will show you how to print a shipping label for a customer with a separate ship to address. We will cover creating a query, using the mailing label wizard, and implementing VBA to ensure your label includes the most up-to-date information. This is part 2.
Members
In the extended cut, we will cover copying the shipping address to the order to track where an order was shipped even if the customer's address changes later. This will ensure accurate shipping records and is useful for handling special one-time shipping addresses. We'll learn how to do this without coding.
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
Up Next
Keywords
TechHelp Access, ship-to address, creating shipping labels, automating shipping labels, Microsoft Access query design, conditional address fields, mailing label wizard, Avery 5168 label, landscape label formatting, form-driven queries, VBA for Access, refreshing data in Access, Access button to print label, embedded macros in Access
Intro In this video, we continue building shipping labels in Microsoft Access by creating a query to select either the regular or ship-to address, depending on the data entered. I'll show you how to handle null values, add city, state, zip, and country to the query, and use the Label Wizard to generate a shipping label report. We'll adjust the layout for landscape printing, add a return address, and set up a button on the customer form to print the label. I'll also demonstrate some basic VBA to refresh form data before printing and copy the billing address to the shipping address if needed. This is part 2.Transcript Today's part 2 of my shipping label series where I'm teaching how to make shipping labels in your Access database. If you haven't watched part 1 yet, go watch part 1. Get out of here. Go watch it and then come on back.
So in the last video on Friday we have our customer table. We added the ship to fields right there. We put it on the customer form. Now, I want to make a button eventually that says, print me a shipping label. But first, or next, however you look at it, we're going to make a query that just has this label in it. Okay? So let's create query design. And I'm going to bring in my customer T. Now, in this query, we only need the fields that we need for the label. So we're going to start off, of course, with Customer ID. First name and last name should always be the same.
Now, here's the thing. I want to look at the ship to address. If there's something there, I'm going to use the ship to address, all the fields, city, state, whatever. If this is null, then I want to use the regular address. OK, so here's how I'm going to do it. Close this thing. I'm going to click here. I'm going to zoom in so you can see it for your pleasure. We're going to make a new field. Let's call it x address, or whatever you want to call it. I don't care. Colon. If the ship to address is null, then this guy is going to be just the address. Otherwise, use the ship to address. If this guy's null, is null, then use the regular address. Otherwise, we're going to use the ship to address. Now copy this whole thing because we're going to kindly reuse it and hit OK. Let's just save this real quick, shipping label queue, and let's see what we got.
Now, we didn't put any data in here. So right now, there's no shipping address. If I open up the query, I should see that. Right? And while I'm thinking about it, I only want to print the shipping label for the current customer. Right? Whoever is open. So in the customer ID field here we're gonna say equals forms customer F customer ID. That's why I want you to watch those prerequisite videos before the last class. Now if I do happen to put something here, 101 Main Street, Buffalo, New York, 14222. Okay, now remember it's dirty. So if I run this now, this is what's still in the table. Okay, because this guy is dirty. So when we have our little button here, we're gonna make sure we have to have a refresh of some kind in there. But for this, I could just move off the record and back to it or open and close the form. But if I open it now, look at that, it's got that address in there because ship to address is not null.
And if you want to learn more about null, go watch this video. It talks about null, is null, the is null function, all that different stuff. Usually you know this by the time you get to the if function and all that. But if not, go on. See, I know what order I cover stuff in in my classes. I don't know what order you've learned stuff in. If this was one of my classes, I'd already know we covered null before we covered if. But, okay. So go watch this if you don't know what null is. That's the trouble with, like, I've got my course and then I do these TechHelp videos and I try to remember the prerequisites, but I don't always get them all. So, okay. Anyway.
Now we can pretty much do the same thing with the other fields. All right, this is why I want you to copy this. Then you can do it again. Copy this to your clipboard. OK, now the next field is city. So we're going to call it X city. But I'm still going to look at the ship to address. I want to just make sure that if the ship to address is null or not, use all of the fields from the ship to. So city or ship to city. Just like that. Okay. We'll do the same thing with state. X state. All right. State and state. Two more. Zip. I used to edit stuff like this out of my videos, but then people would complain and be like, well, you didn't do zip code, I missed it. Well, I said I'm going to do it off camera, so now I do everything on camera. I don't care, you can watch. Grab a Snickers bar. All right, country and country, okay. I would rather have a few people bored watching me do something repetitively than lose one person who's like what happened. All right, save it, run it, and there we go. There's the whole ship to address whether or not something's in that first field is what determines it. Now I've got my query that I can use to build my report.
All right, so close this, but leave the form open because remember this query we're getting our data from needs this form to be open, so we're going to use the mailing label wizard. It's a good wizard. I like it. It's going to be on the create tool. Now make sure you click on "shipping label q" first because it gets its data source from whatever you've got selected. So select "order t" and hit the button that's going to base it on "order t." So click "shipping label q," that's where our data's coming from. Click "labels." Now for a shipping label, I like the Avery 5168 on an 8.5 sheet, 8.5 by 11 sheet of paper. You get four of them on a page. It's like basically a quarter page label. All right, hit next. You could jack up your font size, so this is going to be a bigger label. I jacked it up to like 18, 16, whatever, that's fine. Next, build your prototype label. So it's going to be first name, space, last name. Did I put a space in there? Space, last name, Enter. Address, Enter. City, space. State, space. Zip, Enter. Country. I covered this in Access Beginner Level 1, if you've never seen this before. Next.
And that's a free course. What do you want to sort by? Doesn't matter. We only got one label on this report. Next. What would you like to name the report? Well, we're going to name it "Shipping Label R." And sure, I'll see the labels as they look when they're finished. All right. Finish it up. Boom. There you go. There's your label. Now, you can jazz this up a little bit if you want. All right, you got some room to play with. One thing I might like to do is make it landscape instead. All right, landscape looks a little better. And then in here, you could take this guy. Now since we're landscape, let's see what our margins are. Go to the page setup. All right, our margins are, okay, so we got half inch, half inch, half inch, basically half inch around, so an inch around the page. So if we're going landscape that means that this is eight and a half by eleven sheet of paper so there's the edge there's the whole paper there it is with the margins I like to come in like a tiny little bit more that far. Okay so that's the whole page but we're doing two across so I really can only come out to five.
All right so go a little bit shy of five right about there that should do it so that's how big that can be that way. Now this is going to be 8.5 by 11. So 8.5 divided by 2. We got 7 with the margins. So 3 and 3 quarters. All right, 8.5. So it'd be 7.5 divided by 2. 3 and 3 quarters. Yeah, that's right. Okay. So this guy can come out to about maybe there. Maybe. So that's the size of your label if we're going to landscape. So now you can take this and center it right about here. Maybe down a little bit to the right because you want your return address up here right? So I'll grab report design, grab a label, drop it here. All right, and we'll put you know Spock's Emporium, Emporium 101 somewhere, Galaxy, far away, whatever. That's your return address. On the shipping label. All right, so save that, close it, and now we'll preview it. Where are you? Right down here. Shipping label, R, right-click, print preview, and okay, all right, this comes up occasionally. Some data may not be displayed. That's because we were playing with stuff manually you got this just a little bit too wide so come in here design view make this guy just one little tiny bit smaller and I bet you that goes away. Print preview. Yep. Oh still there still there let's see. Design view a little bit in more. Save it. Right click print preview one more time. All right there we go third time's a charm see there's your label. Now you're probably saying to yourself, self, this is just one label on a page, you can fit four of these bad boys on here. Yeah, you can. And I've got other lessons that show how to put multiple labels on a page and all kinds of stuff. I can show you how to do it with a Dymo label printer. I'll go over some other videos I've got at the end of this one. But for now, our goal was just to print the one label and there it is.
All right, now we got to print this guy from the form. So I'm going to put a little button right here just to do that. All right, let's go to design view and we're going to go to command button, drop it here. We're going to go report operations, preview report, next. I want the shipping label R. I'm going to go text, shipping label. Next, it wants a name for the button. I'm going to call it "print shipping label button," BTN. And then hit finish. Someone's beaming in. Hold on. There. All right. Now we can close this, save changes. And I just had access lock up on me. There was a, there's one of the recent releases of access introduced some kind of a weird bug and even my really simple databases like these have been locking up. So I'm waiting for the next update. Anyhow, I had to restart. So back into here, here's my shipping label, click the button, and there it is.
Now the problem that we have at this point is if I do make any changes here like this right if I click the button it doesn't have that update in it so we have to put a refresh in this button now this is where I said there's a little bonus material I'm gonna we got a little little bonus extra here for you alright you're ready for some bonus material now this is the kind of extra little stuff I usually throw in the extended cuts but I'm feeling generous today so we're gonna do a little bit of VBA stuff. Now, if you've never programmed VBA before, I know I said this was not going to be a lesson that required VBA, and it doesn't. We're done unless you want to see the extra cool stuff. All right, if you've never done any VBA before, go watch this. It'll get you started in about 20 minutes.
Now I'm going to just get rid of this button here because when you use the wizard, it creates something called an embedded macro, and I try not to work with those. I'm not a big fan of embedded macros. So I'm gonna grab a new button, I'm gonna drop it down here, cancel the wizard. This will be my shipping label button, which I will, well, that made it too big, shipping label. Which I will open up its properties and also call it shipping label button. Now I'm gonna right-click and go to build event, I know it's off the screen here. Let me move this up so you can see it. Right-click and build event. There it is. That's going to open up your VBA code window. There it is. Boom! Look at that. And inside here, first I'm going to say me.refresh. That refreshes the current form. It saves the record to the table. So now I can open the report and it'll have the most up-to-date information in it. And that command is do command DOCMD.open report. What's the report name? Shipping label R, comma. How do you want to see it? I want to preview it on the screen. So it's AC view preview. It gives you a list right there. And that's it. That's all you need.
See? That was actually even easier and faster than running the button wizard. Once you learn the commands. And really, there's only a handful of commands you have to learn, like a period to the student open form same thing this is a little condition that says with the customers the current customer it's not hard to set up a hard folks it just takes a little getting used to so close this close this save changes yes bring the video window back down and now if I come in here and make this a bunch of Z's and hit shipping label, you can see the Z's are now in there because this record was saved first. How nice that is. Get rid of it and hit the shipping label. Perfect. That's kind of the same thing that I showed in the orders when we did the invoices. Same thing has to happen because if you don't save this, it won't update.
Another little bonus I figured I'd throw in there if you want to copy the billing address to the shipping address field you can do that with a little bit of code too. Let's say that you know James Kirk he's got he's in Riverside Iowa same zip code but his office is on a different street. So we want a different street. So you want to copy this and just make a minor change to it. Well how do you do that? This is nice and easy. We're gonna try to copy this button too. Ready, copy, paste. All right, and I'll just slide this up here. Now we're gonna say copy, copy, I can't spell today, copy bill address like that. Copy, copy Bill's address, get it? Bill Shatner? Ha ha, ha ha. All right. All right, so copy. Why do I keep putting two Ps in my copy? Copy, Bill, Button. Good. Burn. Okay. It's time for a break. I've been recording too long today. All right. Ready? Right-click, Build Event. Comes back into the Code Builder. Now, right in here, I'm going to say, ship to address equals address. That's it. Right? Ship to city equals city.
Ship to state equals state. Ship to zip equals zip. Ship to country equals country. That's it folks and yeah, there's a lot more advanced stuff in here. You could put an "are you sure?" You can put checks to make sure that these values aren't all there's all kinds of stuff you can do, but that's the basics of how to copy one field to another one. It's that simple. Save it, close it, save it, close it, open it, and let's go. I'll just do this one here. Copy billing address. Oh, there you go. If there's something in here, right, test it. Oh, there you go. OK, come over here, click the button, boom, and give me a shipping address. OK, isn't that special?
Now one thing I do want to mention is here. We are only printing one shipping label for this customer. However, we are not saving where the order was shipped to and that could be important because if this customer moves and they said hey I didn't get the order that you shipped a month ago right and since then you've updated their address in here well guess what you don't know where it was shipped right so you really want to copy this shipping address on to the order so you know where this order was shipped or if you got a special one-time thing like maybe someone's sending a gift right like on Amazon you can send a gift and they want to send it to a different address but it's not their normal shipping address. So in that case you have to copy this address to the order so you know where this order was shipped and how do you do that? Well we'll cover that in the extended cut for the members. Silver members and up get access to all my extended cut videos. Gold members can download these databases and get access to my code vault. And I'm actually going to show you a way that you can do this without coding.We're going to probably do a little bit of coding too, just you know, it's fun. But you can do this without programming. It is possible.
Now, before I let you go, I want to mention a couple of other things. First, if you use Dymo label writers, I have one and it's fantastic. They're great for just printing out like I got to ship one thing or I got to mail one thing. So I've got a separate video to teach you how to use that with Access. Not very hard. This is a free video.
I also want to mention that I have a mailing label seminar where I cover all kinds of extra stuff and how to do crazy things with mailing labels. If you do a lot of shipping and mailing and stuff like that, check this seminar out. It's about an hour long. I teach you how to insert blank fields at the top. So if you got like a sheet with one label missing on it, you can skip that one and print over it. You can make X copies of a label. So if you want to make 10 copies of a particular label, you can, like return address labels, that kind of thing. All kinds of stuff covered in this seminar. Check it out. I'll put a link to it down below.
So that's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time and members, I will see you in the extended cut.
TOPICS:
Creating a shipping labels query in Access
Using conditional logic to choose addresses
Adding city, state, zip, and country to the label query
Creating a mailing label using the Label Wizard
Adjusting label layout to landscape format
Adding return address to the label
Creating a button to print the shipping label from the form
Embedding VBA code to refresh form data before printing
Copying billing address to shipping address via VBA
Importance of saving the shipping address with the order
Customizing label printing for different needs (Dymo label writers)
Overview of additional mailing label seminar content
COMMERCIAL:
In today's video from Access Learning Zone, we dive into part 2 of our shipping label series. If you missed part 1, go watch it first; I'll wait. We start by creating a query for the shipping label, looking at whether to use the regular or ship-to address. I explain how to handle null values and build our query fields for city, state, zip, and country. Once that's ready, we use the mailing label wizard to set up our labels and create a button to print them from the form. There's a little bonus VBA code to update your records before printing.
You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the first step in creating a query for a shipping label in Access?
A. Adding a "Print" button to the form
B. Designing a report layout
C. Creating the query design and bringing in the Customer table
D. Entering customer data
Q2. How does the query decide which address to use for the shipping label?
A. It always uses the home address.
B. It randomly selects an address.
C. It uses the "Ship To" address if it's not null, otherwise it uses the regular address.
D. It asks the user each time which address to use.
Q3. Which function is used in VBA to refresh the form data before generating the report?
A. DoCmd.Refresh
B. Me.Refresh
C. Form.Refresh
D. RefreshForm
Q4. What should you make sure is selected before starting the mailing label wizard?
A. Customer Form
B. Orders Table
C. The data source query ("shipping label q")
D. The labels report
Q5. When configuring the label layout, which Avery template is recommended for shipping labels?
A. Avery 5160
B. Avery 5162
C. Avery 5168
D. Avery 5150
Q6. How many labels fit on a single 8.5 by 11 sheet using the Avery 5168 template?
A. 2
B. 4
C. 6
D. 8
Q7. Which VBA command is used to open the shipping label report for preview?
A. DoCmd.OpenReport "ShippingLabelReport"
B. Report.Open "ShippingLabelR"
C. DOCMD.OPENREPORT "ShippingLabel", acViewPreview
D. DoCmd.OpenReport "ShippingLabelR", acViewPreview
Q8. What VBA code copies the billing address to the shipping address fields?
A. CopyAddress "Billing", "Shipping"
B. ShipToAddress = Address
C. Me.CopyAddressFunction
D. BillAddress.Copy(ShipToAddress)
Q9. Why might you need to copy the shipping address onto the order?
A. To save memory in the database
B. To keep a historical record of where the order was shipped
C. To make the user interface simpler
D. To prevent the customer from changing their address
Q10. What should you do if the generated label is too wide and some data might not be displayed?
A. Increase the font size
B. Open the form again
C. Adjust the margins and resize the label in Design View
D. Restart Access
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-B; 7-D; 8-B; 9-B; 10-C
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 continues my series on creating shipping labels in Microsoft Access. This is part two, so if you have not seen the first video yet, I recommend going back and watching part one to get caught up on the foundational steps.
Previously, we built a customer table and added fields for the shipping address. We also placed these shipping fields on our customer form. In this lesson, I am going to show you how to prepare a query that gathers only the necessary fields for the shipping label and builds the logic that decides when to use the regular address or the dedicated ship-to address for each customer.
To start, I create a query based on the customer table. For the purpose of generating shipping labels, we only need to include the fields relevant to the label, such as Customer ID, first name, last name, and the various address components. The core challenge here is determining which address to show: if the ship-to address exists, we use it. If it is empty, we fall back on the customer's main address.
This logic is easily handled in the query by setting up calculated fields. For each address part (address, city, state, zip, and country), I create a new field that checks if the ship-to version is null. If it is, the query will use the main address; if not, it uses the ship-to value. You can repeat this logic for each component, copying and adapting the expression for city, state, zip, and country.
Initially, you may not see data if there are no values entered yet for those fields. Remember, when you are previewing the label, you likely want to display information for just the currently open customer. You can set a criteria in the query so it matches the Customer ID currently open on your customer form. This is why those earlier lessons where we set up forms and learned how to reference form fields are so useful.
If you want to practice with this setup, you can fill in a ship-to address for a sample customer, and then just make sure you save or refresh the record before running the query again. This ensures that the most current data appears in your label output.
For those who might not have a lot of experience working with null values in Access, I recommend reviewing my lesson focused on null handling and the IsNull function. This will help clarify how Access determines if a field is empty, something that's critical when working with conditional logic in queries like this.
Once the query is ready, we move on to designing the actual label. Access provides a built-in Mailing Label Wizard, which is a great tool for this purpose. With the query selected, you can launch the wizard and choose a suitable label format (for example, the Avery 5168 template, which gives four sizable labels on a standard sheet). The wizard then lets you pick which fields to display and in what order—typically first name, last name, address, city, state, zip, and country—all formatted for the label.
I usually like to increase the font size to make the shipping label more readable, and you can customize the layout further by adjusting orientation to landscape and tweaking margins as needed. There is even room to add a return address at the top of the label. I walk through how to align and size your controls so everything fits nicely, whether you have one or several labels on a page.
Perhaps you want a button on your customer form that prints the shipping label directly. You can do this by adding a button and configuring it to open the shipping label report in print preview mode. When you use the built-in command button wizard, Access will make an embedded macro, but I recommend replacing this with VBA code for more reliability and flexibility.
To do this, remove the macro and create a new button. In the button's VBA event code, you'll want to refresh the form so any edits are saved to the table before printing and then open the report, choosing the Preview view so you can see the label before sending it to the printer. Just a couple of lines of code make this a very efficient setup.
Another handy task is copying the billing address over to the shipping address fields if they are often the same but with minor changes. You can automate this using another button and a few more lines of VBA that simply assigns each billing address field to its corresponding ship-to field. It is a straightforward shortcut that saves time for data entry, and of course you can build on this with more advanced error checking and features as you see fit.
One important detail as you develop your shipping label solution is remembering that address information can change over time. If you only store shipping information at the customer level, you might lose track of where a particular order was sent once addresses are updated. The best way to ensure you always have a record of where an order was shipped is to store the address used at the time of shipping directly with each order record. In today's extended cut, we will cover this topic in detail, including how to implement this both with and without programming.
Before wrapping up, I want to mention a couple of related resources. If you use Dymo label printers, I have a separate free video dedicated to printing labels directly from Access to a Dymo printer. That's a great option if you need more flexibility or single-label printing. Additionally, I offer a comprehensive mailing label seminar that goes much further, including handling sheets with missing labels, printing multiple copies of a label, and more advanced label management. This seminar is an hour long and covers a range of practical labeling solutions if you need something beyond the basics.
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 Creating a query for shipping labels in Access
Using IIf and IsNull to select shipping or regular address
Adding concatenated fields for city, state, zip, and country
Filtering the query to only show the current customer
Building shipping label reports with the Label Wizard
Configuring label size and selecting Avery 5168 format
Adjusting the report layout to landscape orientation
Customizing label margins and dimensions
Inserting a return address in the label design
Creating a button on the customer form to print shipping labels
Replacing embedded macro button with VBA for printing
Using Me.Refresh in VBA to save changes before printing
Using DoCmd.OpenReport to preview the label report
Copying billing address fields to shipping address fields with VBA
Explaining importance of recording the shipping address at order time |
