Shipping Label
By Richard Rost
2 years ago
Print Shipping Label for Separate Ship To in MS Access
In this Microsoft Access tutorial, we will learn how to print a shipping label for a customer with a separate ship-to address. I will show you how to add a ship-to address for each customer, create shipping labels, and automate the process without complex programming.
Evelyn from Maplewood, New Jersey (a Platinum Member) asks: I've been using your Invoicing database and it works great, but in addition to sending an invoice, I also have to ship a product. Right now, I'm making shipping labels manually. Is there any way I can just click a button and create a label for a different shipping address from my database?
Prerequisites
Recommended Courses
Up Next
Keywords
TechHelp Access, shipping labels, ship-to address, create shipping labels, non-programming method, additional customer address, bill-to and ship-to, Access design view, customer table, add fields Access, copy address fields, form design Access, Access calculated fields, Access customer form, Access report query, generate shipping labels
Subscribe to Shipping Label
Get notifications when this page is updated
Intro In this video, I will show you how to add a ship-to address to your customer records in Microsoft Access so you can easily print shipping labels separate from invoices. We will update the customer table structure, modify your forms to include the new address fields, and discuss the rule of threes for handling multiple addresses. I will also demonstrate some useful design tips for managing form controls, plus a simple way to copy billing information to the shipping fields. Finally, we will talk about preparing a query to generate the data needed for your shipping labels. This is part 1.Transcript In today's video, we're going to print shipping labels. I already showed you how to print invoices. That's easy. Well, what if you've got to mail an invoice to their office but you've got to ship a product to a different address? So, we'll add a ship-to address for each customer and then we'll make shipping labels and we'll have some fun.
Today's question comes from Evelyn in Maplewood, New Jersey, one of my Platinum members. Evelyn says, "I've been using your invoicing database and it works great, but in addition to sending an invoice I also have to ship a product. Right now I'm making shipping labels manually. Is there any way I can just click a button and create a label for a different shipping address from my database, of course?"
There are lots and lots of different ways to do this. I'm going to show you one method, but I cover a few different ways to do this in my different classes. I'm going to try to show you an easy one that doesn't involve any programming. So, we're going to call this an expert level class. I consider expert to be the middle ground between beginner and developer. All right, you pass the basics, but you're not going to program. Maybe we'll do a little bit of programming, just a couple of extra cool tweaks. For example, copying the bill to address to the ship to address - you can do that with just one little button, a couple lines of code - it's pretty cool. So, maybe I'll show you that at the end of the video. We'll see if you're good little boys and girls.
Now, we do have some other prerequisites. Let's see what else we need here. If you have not watched my invoicing video, go watch it. It's based on the blank template video that I've got, and I show you how to set up invoices. Go watch this because this video is based on that.
You should also know how to make calculated fields both in queries and in forms. You should know how to reference a value on an open form. Okay, so if the customer form is open, you should know how to get the customer. Okay, so if not, go watch this one and a lot of the video on the air function. If you don't know what that is, go watch that one. These are all free videos on my website and my YouTube channel. There might be a couple more, but I'll be sure to throw links to other ones if needed.
All right, so here I am in my TechHelp free template. This is a free database you can get off the website if you want to. If you watched my invoicing database, you know that you've got the customers here. Customers can have orders; orders can have invoices printed. So now, what we need is - okay, we're going to send the invoice, great, but now we got to ship the product, okay?
So, what we're going to do is, for each customer, we're going to add a second address. This will be their bill-to address. We'll add an optional ship-to address. Now, I personally have something that I call a rule of threes. Okay, if you have up to three of anything, it's okay to keep it in a single table. If it's never going to be more than three, like phone numbers, addresses, that kind of stuff - you got three, and it's never going to be more than three - fine, you can put three address fields in the customer table. Bill to, ship to, whatever else you might want. Phone numbers - you got phone, you got fax, you got cell, all right? If you ever need more than three, you ever think possibly in the future you might need more than three, then it should be in a second related table, like contacts for example, like orders. We don't know how many orders this customer may place. They may place one; they may place hopefully thousands. So, you'd never put order information in the customer table. You'd never put contact information like this in the customer table. Some people put it in notes eventually, and there are problems, right?
The rule of three: I wouldn't put all this contact information, because old addresses are the same way. Do you ever think you're ever possibly going to need more than three addresses for your customers? It happens. I had one client years ago that needed to have possibly five, six, ten addresses for a company. So, that's fine. Build your database so that it meets your needs. Phone numbers - you might need more than multiple phone numbers. That's totally up to you. But for this example, we're going to keep it simple. We're going to have a billing address and a shipping address, and that's all this business will ever need.
So, the first step is just to add the fields to the table. We're going to go to the customer table, design view. Now, I've already got all the address fields in here that I need. They're right there. See how I did that? Take your mouse, come over here in the margin, right-click, drag it down and select all of those guys, copy, control-C. Now, we're going to come down to the bottom, click down here like that, select that row and hit paste, control-V. Boom, there's your address field. Now, I'm just going to add "ship to" in front of each of these. Ship to, right? I'm going to go shift, home, that selects where the cursor is to the beginning of the line, that's an old word trick. Copy, then I'll come right here and go paste, paste, paste, paste. All right, now let's select these guys and move them up under the other address. Now, once you've got them selected like that, click and drag anywhere inside this block here and drag it up under country and drop it right there. See how I did that? Watch that again; review the... See, that's the great thing is, in the classroom, I have to show it again. You guys can just rewind the video. So watch that again if you didn't get it right.
I cover a lot of this moving stuff around in my Access beginner series. Now, do you have to rename these ones "bill to"? You can if you want; you don't have to. If you don't have any code - if you don't have any VBA code, any programming in your database - Access is really forgiving as far as if you rename a field, it should rename anywhere else this field is used: in queries, forms, and reports. It should. Doesn't always, but it should. But do you have to rename it? No, you don't have to rename it if you don't want to. You can leave it just like this. You'll just remember that this is the bill-to address; this is the ship-to address.
Now, the way we're going to program this is, we're only going to use the ship-to address if it's different from the primary billing address. You don't have to copy it if you don't want to. We'll use a little if function to say, "Hey, if the ship-to address is blank, if it's null, use the regular address." All right. So, we'll get to that in a bit.
All right. So, let's go ahead and save this, and we'll close it. And now, we've got to add it to our customer form. All right, now, here's the billing stuff. Let's go to design view, and I'm just going to do this. Watch this. I'm just going to copy these guys. Copy, paste. See that? Control-C, Control-V. Copy, paste. Now, I'm going to move these down just a bit. Well, you can move them down, you can put a box around them, you can separate it however you want to. Let's just change these guys' color a little bit. That's what I like. I'm gonna select these ones, and let's maybe make those like a light purple, just like that. And then we'll take these ones, and then we'll make these ones maybe like a light - and this is something to separate. This will be billing address, like that. Maybe move that. And this will be shipping address, or whatever you want to call it, like that. You want to put boxes around this stuff, you can. You want to separate them out a little bit more, give a little bit more space between them, however you want to look at it. They're your Legos; you put them together however you want.
Now, of course, I've got to move this down so it matches. Little stuff like that bugs me. Okay, looking good. All right, so now we've got to just change these fields. All right, so we go over here. The control source is going to be ship to address. That's address. The control source and the name are the same. All right, do the same with these guys. Ship to address, we're going to make this the ship-to city. Copy and then paste. All right? Same thing with state. You don't want "text 34." Just trust me on this, because if you make any functions or whatever, or any formulas or later on VBA programming that relates to that, you don't want to have "x = text 35" in your code. What is "text 35"? All right. Country, country, copy. And notice if you click over here, it selects that. You can click up here and then select the text, or you can just click on the name there and paste it in. A student taught me that one. I didn't know that one. All right, save that, close it, open it. All right, looking pretty good.
All right, next up, before we make the label, I want to make a query that gives me just the data that I need for the label. Now, we did the same thing with the orders. If you look for invoices, all right, underneath this invoice, there is this guy called the order invoice queue. What does order invoice queue do? Well, order invoice queue gives me all the information I need with the order information, the order detail information, and the customer information so I can generate the invoice. Okay, and in here it's got a criteria that says, "Just show me the order that's currently displayed." The forms, order, F, order ID. So, what we can do is, if I want to print a shipping label, we can just print the shipping label right here from the customer form, right? I click "print a shipping label." I want to generate one label for this customer and have that be the source for the report.
All right, and we will build that query and the label in the next video in part two. Yeah, I know the slide says "tune in tomorrow." I stole it from Batman. It says, "Tune in tomorrow. Same bat time, same bat channel." I know today is Friday. It's Friday the 21st of June 2024. So, we will do this on Monday, Monday the 24th. But if you're a member, you can watch it right now. Because members get access to all of the videos as soon as they're released and I'm going to record it in just a few minutes.
But that's going to be your TechHelp video for today, part 1. I hope you learned something. Live long and prosper, my friends. I'll see you Monday for part 2.
TOPICS:
Adding a ship-to address field
Using if function to switch addresses
Modifying customer table structure
Adjusting customer form layout
Copying fields within design view
Renaming form controls
Creating a query for shipping label data
COMMERCIAL:
In today's video from Access Learning Zone, I will show you how to print shipping labels for your invoicing database. We will start by adding a ship-to address for each customer and then design custom shipping labels. First, I'll walk you through updating the customer table, adding necessary fields, and including them in your forms. We will make it easy, with minimal coding, but with some neat tricks like copying the bill-to address to the ship-to address with a single button click. You'll learn how to manage multiple addresses using the rule of threes and ensure smooth database operation. Find the complete video on my YouTube channel and my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the primary purpose of today's tutorial video?
A. To set up customer accounts in the database
B. To print shipping labels for products
C. To create new customer invoices
D. To automate email responses
Q2. What specific problem does Evelyn, the Platinum member, need help with?
A. Manually creating shipping labels for different addresses
B. Calculating the total cost of shipping
C. Recording customer feedback on shipping
D. Verifying the shipping carriers for each product
Q3. What does the "rule of threes" refer to in the context of database design?
A. Up to three users can access the database simultaneously
B. Limiting the number of address fields in a single table to three
C. Each customer can place up to three orders at a time
D. Shipping addresses must be verified three times
Q4. Why might you use a second related table in a database?
A. To minimize data redundancy when more than three pieces of related information are needed
B. To allow more than three users to access the data at once
C. To store multimedia content like images and videos
D. To enhance the visual appearance of data entry forms
Q5. What is the recommended method for differentiating between the billing and shipping addresses in the customers table?
A. Use the same fields for both addresses
B. Adding a prefix like "ship to" for shipping address fields
C. Only using a single address field and changing it as needed
D. Keeping all address information in a separate notes field
Q6. What should you do if the ship-to address is left blank?
A. Send the product to the billing address by default
B. Ignore the record and move to the next one
C. Flag the record as incomplete and ask the user for clarification
D. Generate an error message and terminate the operation
Q7. When customizing the customer form to add shipping address fields, what is recommended for clarity?
A. Position billing and shipping addresses closely together without separation
B. Use different colors or separations to differentiate between billing and shipping addresses
C. Place shipping address fields at the end of all other fields
D. Design the form with no visual distinction for different types of addresses
Q8. Why is it advisable to avoid naming form controls with generic names like "text35"?
A. It makes the form load slower
B. It is difficult to identify relevant fields in functions and programming
C. It confuses database relationships
D. It is not permitted in Access
Q9. What type of function or criteria is used to print a shipping label from a customer form?
A. A calculation function for total cost
B. A selection criteria to show only the current customer’s details
C. A counting function to tally the number of products
D. A sorting function to list all products by date
Q10. How can members access the next part of the tutorial video?
A. By visiting the website the following day
B. By waiting until Monday, June 24th
C. By purchasing a separate course
D. By accessing the content immediately as they have member privileges
Answers: 1-B; 2-A; 3-B; 4-A; 5-B; 6-A; 7-B; 8-B; 9-B; 10-D
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone is focused on creating shipping labels for your invoicing database. Previously, I explained how to print invoices, which is fairly straightforward. Now, I want to handle a common scenario where you may need to mail an invoice to one address but ship products to another location. To solve this, we're going to add a separate ship-to address for each customer and then create shipping labels in Microsoft Access, keeping the process as simple as possible.
Our lesson is inspired by a question from a member who uses the invoicing database and currently creates shipping labels manually. The request was to automate the process and print shipping labels directly from the database, especially when the shipping address is different from the billing address.
There are numerous ways to manage shipping addresses in Access. Here, I will cover one straightforward method, suitable for users who have moved beyond the basics but may not be experienced with programming. However, I will share a quick programming tip for copying a bill-to address to a ship-to address with a button, which involves just a few lines of code.
Before you get started, make sure you've watched my invoicing and blank template videos. These tutorials form the base of today's lesson. You should also know how to create calculated fields in both queries and forms and how to reference values on an open form, such as accessing the current customer from the customer form. If any of those skills are new to you, I have free resources on my website and YouTube channel to fill in the gaps.
Let's move to the technical part. In the free TechHelp template database, customers are linked to orders, and orders are linked to invoices. Now, we're adding the need to ship products, and that requires capturing a second address for each customer. This is where my rule of threes comes in. If you only ever need up to three items (such as addresses or phone numbers), it is acceptable to store them directly in the customer table as separate fields. More than three, and you should consider a related table for that information. For our purposes, we're only using two - billing and shipping addresses - so we can keep both in the customer table.
The first step is to add the ship-to address fields to your customer table. Simply open the customer table in Design View. If you already have fields for the billing address (address, city, state, zip, country), select them, copy them, and paste them at the bottom of the fields list. Prefix each new field with "ship to" so you can easily tell them apart. Using tricks from Word, you can quickly rename the fields by using keyboard shortcuts to select and copy field names.
You may ask whether the original billing address fields need to be renamed to "bill to." This is only necessary if you want the extra clarity. As long as your database does not include references in VBA code that rely on those field names, Access is tolerant of renamed fields and will update references in most places. The important thing is to know which address is which.
Our approach is to use the ship-to address only when it differs from the billing address. If the ship-to fields are left blank, the system will fall back on the regular address using an if function in queries and reports. This keeps the workflow simple and efficient for users who do not always use two addresses.
Once the fields are added, save and close the table, then edit your customer form to add the new ship-to fields. Copy the existing billing address fields on your form, paste them, and adjust their layout as you like. Change the control sources to point to the proper ship-to fields. If you want, you can use color or boxes to visually distinguish the billing from the shipping address. Give them clear labels and arrange them in a way that is comfortable for your users.
Be sure to update the control sources for each new text box so they display the proper shipping address data. Avoid using default control names like "Text34" for future clarity and maintainability. Rename them to something meaningful.
After updating the customer form, the next step is to create a query that retrieves just the data needed for the shipping label. Following the model used for invoices, build a query that lets you generate a label for the customer currently displayed in the form. You want the query to bring together all the necessary fields so your report (the shipping label) has everything it needs.
This concludes part one of our shipping label lesson. In the next lesson, I will show you how to build the shipping label itself and integrate it with your invoicing system. Members who want to watch right away can access the next part immediately. For everyone else, the next video will be available soon.
To review, here are the main concepts covered today: how to add a ship-to address field, how to use an if function to select between billing and shipping addresses, how to modify the customer table structure, how to adjust the customer form layout, how to copy and rename fields in design view, and how to set up a query to support shipping labels.
If you'd like to see step-by-step demonstrations of everything discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends.Topic List Adding ship-to address fields to customer table
Applying the rule of three for address fields
Copying address fields within table design view
Positioning and organizing address fields in the table
Including ship-to address fields on customer form
Visually distinguishing billing and shipping addresses on form
Editing control sources for new form fields
Renaming and managing form controls for addresses
Adjusting customer form layout and spacing
Creating a query for shipping label data source |
