Pick Address
By Richard Rost
26 days ago
Select From Multiple Customer Addresses for an Order In this Microsoft Access tutorial, I will show you how to select from multiple customer addresses when creating an order, using a simple query and a combo box on your order form. We will build a query to join customers with their addresses, create a formatted combo box that displays address details, and add a field to your order table to store the selected address. This is an expert-level lesson. This is part 1. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, multiple customer addresses, order form, combo box, cascading combo box, address query, address type, address ID, foreign key, customer ID, order table, join tables, shipping address, billing address, address selection, query design, calculated field, cross reference table, bound field
Subscribe to Pick Address
Get notifications when this page is updated
Transcript
In today's video, I'm going to show you how to pick from multiple customer addresses for an order in Microsoft Access.
In previous videos, I showed you how to make it so each customer could have multiple addresses, but we really did not do anything with that data. So, in today's video, we are going to see how we can pick one of those addresses to ship an order to, and we will put it on the order form as well.
Now, this is an expert level video, but I put a little star there. Why the star? Well, most of this, 99 percent of this, we can do just without any coding. But we will need one line of VBA code later to do something. Just one line, and I will show you right where to put it. Aside from that, everything else is expert.
Today's video is to help out one of my Gold Member students, Liam. He is trying to pick an address because customers could have multiple addresses, and you want to be able to pick the address for a specific order. We have gone back and forth on the forums here, and other students like Raymond and Kevin have tried to help him. Sometimes it is easier for me to show how to do it in a video.
I mentioned the name. I have a video that shows you how to store multiple addresses for a customer. So this is that video. It is a five-part series. Go watch this before you start watching today's video because I am going to be using the database from this video for today's video. Go watch all five of these if you have not yet. There is a link. I will put a link down below you can click on.
Part five, the last one, has a finished database, and this is what I am going to use. If you are not a Gold Member, then you are going to have to build this database yourself following along with these five videos.
The benefit of being a Gold Member - Liam, I know you are a Gold Member - go ahead and download this guy if you have not already. Do it with my database first so we are on the same page. Then, once you see how it is done, try to apply it to your database.
Here is the database download. I am going to drag this up to my desktop. I am a little cluttered, I have a bunch of stuff on here. This is only one tiny piece of it.
Here is that database. We have the customer form. I scaled it down and removed some stuff like the order button we are going to put back today. Here is where you can have multiple addresses for a customer. There is me, there is James Kirk, I think those are only a couple.
Now, the first thing we are going to need is a query, so we can join together the customer with their address. When we get to our order form, I am going to have the customer selection here, then I am going to have a cascading combo box below it where you can pick an address. You only want to see a list of addresses for this customer.
We now have to take the address and bring in the customer ID so we can show that in a combo box on here. Let's create a new query. Create query design. My query window is really big. Let's make that smaller.
There we go. Sometimes when I do stuff off camera, my monitor is a lot bigger.
Bring in the address - that is all of our addresses in the database. As a little review, here are all the addresses for all of the customers, every address in the system.
Then we have a many to many junction table - this guy - that brings them together. Here are all the addresses by address ID, here is the customer that owns that address, and then there is the address type, whether it is billing or shipping. This is because you can have multiple customers at the same address. You might have a husband and wife; they each have their own customer record, but they share an address, and that is why we did it that way.
Now, bring in the cross reference table there. That relationship should get made. Now, we are going to bring in the address type table so we know what kind of address it is. The purpose of this is so that we can create a list of options in our combo box.
Let's build that accordingly. Start off by bringing in all the address information, and then bring in the customer ID because we need that so we can show just the addresses for this customer. We are going to need the description, so we can see what we got. There it is. There is the address information, the customer ID, and then the description.
Let's put this in a way that is friendly to see in the combo box. Let's make it so that you have the customer up already, and we want to show his or her addresses. So let's show the address, the city, and then the description, which will be like billing or shipping.
We can close this for now.
I am going to zoom in on this box. We are going to make a calculated field here called full description. This will be the address, comma, space, the city, space, and then inside parentheses we will put the description, and close that up like this.
Let's see what that looks like. There it is. You have the address, city, and then the billing or shipping address. You can see that, and we will see this in the combo box on the order form.
If you want to limit this to just a list of shipping addresses, you can do that here in this query. Say where the address type ID has to be two, or whatever, if you only want to see shipping.
Sometimes, from my experience, you might only have one address for a customer. They might not have a separate billing and shipping address, so you might want to be able to just pick from either one of them. That is up to you. Or you can make it even more complicated and say, well, if they have a shipping address, show the shipping address; if not, then show the billing. It is all up to you, but I am just going to show both of them for now.
Let's save this. We will call this the address with type Q.
Now we are going to make an order and we want to be able to pick that, so we need to put the combo box on it. In the last series, I deleted the order button because I figured we would not need it. Now we are going to add it back again.
Here is the thing - the code is probably still in here. If I go to the code behind that form - yes, the button's code is still in there. So all you have to do is add a new button named order BTN and it should just get that code again. Watch this.
I have a button over here. Copy, paste, drag it down here, and we will make this Orders or whatever. Make it bigger so we can actually see what is in there.
Name it order BTN just like the last button was, and then right click, build event, and it will get that code back again. Contact button, you are gone for good. Save it, close it.
Let's open up the orders.
We are back where we were. Now I want to put a combo box under here to pick an address for the order, but I am picking that address from the query we made, and we are going to limit it to just this customer's orders.
Now, we need a place to save that data. When I pick that address, I need to save that information somewhere. I want to save it in the order, because every order could go to a different address. You might be ordering something for your home address or your office address or whatever. So we need a place to store the data. We are going to store it in the order table.
We are going to add an address ID in here. That will be a number of type long integer. It is a foreign key, and I like to keep all my IDs together up top.
Save it, close it, now open it.
Let's go back into here. Let's make room for that combo box. Slide this subform down. There we go.
We are going to use the wizard to create an address combo box. Let's start off by just getting a combo box that has all of the addresses in it. Then, we will add a WHERE condition later to say we only want to see the ones for just this customer.
Go to form design, find the combo box wizard. It is right there. Combo box wizard is a good wizard. I do not mind him.
We are going to get the value from a table or query - we are using queries - and it is the one we just made, that address with type Q.
What do we want to see in the box? What do we need in the box? We need an address ID because that is what we are actually storing in this field, and then for the visible field, all I need is the full description. Bring that over.
We are storing that field, showing that field. That is how combo boxes work.
Next. Sort it - does not matter. We are going to sort it in our SQL statement, but that is fine. Next.
This is what it looks like. Remember, we do not get the little checkbox here because this is based on a query. So we have to take this and make column count zero. Then, make this as big as you need so you can see everything that is in there, maybe a little bigger. Remember, this is how big the box is when it is open.
Next. Which is going to be the bound field? That is the address ID, and we are going to store that in the address ID for the order. The order gets an address ID. Next.
Label it "Address." Finish. There it is. Use the format painter, slide that guy up under there, over like this.
Save it, close it, open it, and let's take a peek.
It is working so far, but I am seeing all of the addresses. Next, I need to limit this list to just addresses for this customer - this customer back here.
How do we do that? We will talk about that in tomorrow's video.
Before I forget, one last thing - we are going to open this guy's properties. We are going to name this address combo. It will come in handy later.
Save that, close it, close it.
Now I will give you this slide. Tune in tomorrow, same bat time, same bat channel. Or if you are a member, you can watch it right now because I am going to keep recording as soon as I am done here. So Liam, you can keep watching with part two.
One of the benefits of being a member at any level is you can watch videos as soon as I finish with and post them. You do not have to wait for me to go public.
That is going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I will see you tomorrow for part two.
TOPICS:
Creating a query to join customers with their addresses
Adding the address type to the query
Building a combo box to pick an address on the order form
Formatting address information for display in a combo box
Adding a calculated field for a full address description
Saving chosen address information to the order table
Adding the foreign key AddressID to the order table
Using the combo box wizard to set up the address picker
Binding the address combo box to the AddressID field
Customizing the combo box display width and appearance
Renaming form controls for clarity and future use
COMMERCIAL:
In today's video, we're going to learn how to let users pick from multiple customer addresses for an order in Microsoft Access. I'll show you how to set up your database so each customer can have several addresses, then create a query to combine customers with their addresses, and set up a combo box on the order form to pick just the right address for shipping. We'll even add a small bit of VBA code to finish the job, but most of the process needs no coding at all. 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 purpose of the tutorial video?
A. To show how to allow each customer to have multiple addresses in Microsoft Access
B. To demonstrate how to select a customer address for an order in Microsoft Access
C. To explain how to create a report of customer addresses in Microsoft Access
D. To teach how to email customers using Microsoft Access
Q2. What prerequisite is recommended before watching this video?
A. Reading the Microsoft Access official documentation
B. Watching a five-part video series on handling multiple addresses per customer
C. Installing the latest service pack for Access
D. Having basic knowledge of VBA programming
Q3. Why is there a need for a many-to-many junction table in the database?
A. To allow multiple customers to have the same address and one customer to have multiple addresses
B. To support multiple users editing addresses at the same time
C. To enable synchronization with external databases
D. To allow for time-stamped changes to addresses
Q4. In the context of the video, what is the purpose of creating a specific query joining customers and addresses?
A. To display a report of all customers
B. To allow selection of a customer's address for an order, limited to only that customer
C. To count the number of addresses per customer for analytics
D. To find customers with missing addresses
Q5. What field is calculated and formatted to display in the combo box for selecting an address?
A. The customer's name and phone number
B. The address, city, and address type description
C. The address and ZIP code only
D. Only the address ID
Q6. Why might you want to display both billing and shipping addresses in the address selection combo box?
A. Because every order must have both addresses
B. Because some customers only have one address for both billing and shipping
C. To ensure every order goes to a shipping address only
D. Because Access requires it
Q7. When adding the address selection to the order form, where is the selected address information stored?
A. In the customer table
B. In the address table
C. In the order table
D. Only in a temporary variable
Q8. What wizard is recommended for creating the address selection combo box on the order form?
A. Form wizard
B. Table wizard
C. Combo box wizard
D. Subform wizard
Q9. What field should be the bound field for the combo box (saved into the order record)?
A. Customer ID
B. Full description
C. Address ID
D. Address type
Q10. What was mentioned as a tip regarding showing only the current customer's addresses in the combo box?
A. Filter them in VBA during record save
B. Use a WHERE condition in the combo box's row source
C. Allow showing addresses of all customers for faster selection
D. Use separate combo boxes for billing and shipping addresses
Q11. What step was recommended if you want to only show shipping addresses in the combo box?
A. Change the combo box bound field
B. Add a filter in the query to limit to shipping address type
C. Hide the billing column at runtime
D. Manually delete non-shipping addresses from the database
Q12. Where is the foreign key for the address stored in the orders table?
A. It is not stored anywhere
B. In a new field called Address ID in the orders table
C. In a text field called Address Description
D. In the customer table as a list
Q13. What is the advantage of being a Gold Member as discussed in the video?
A. Ability to send emails from Access directly
B. Access to download the finished example database and watch videos immediately
C. Automatic coding of queries
D. Guarantee of weekly personal support sessions
Q14. What is necessary after creating the combo box, to help future steps in the process?
A. Name the combo box control appropriately
B. Add an unbound text box next to it
C. Include all address fields in the display
D. Make the combo box read-only
Q15. What area of Microsoft Access does the one line of VBA code apply to in the video's solution?
A. Setting up the main menu form
B. Configuring the combo box to filter addresses to the selected customer
C. Sorting the order list alphabetically
D. Exporting records to Excel
Answers: 1-B; 2-B; 3-A; 4-B; 5-B; 6-B; 7-C; 8-C; 9-C; 10-B; 11-B; 12-B; 13-B; 14-A; 15-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 select from multiple customer addresses for an order in Microsoft Access.
In earlier lessons I covered how to set up a system where each customer can have multiple addresses in your database, but up until now we have not actually used that information for order processing. In this session, I will guide you through the process of allowing the user to pick one of those addresses for an order and display it on the order form.
This lesson is geared toward expert users, but with a caveat. Ninety-nine percent of the work can be accomplished using Access's built-in tools, without writing any programming code. However, there is a single line of VBA code required towards the end, and I will clearly explain where and how to use it.
The impetus for this video was a request from one of my Gold Member students who needed to select an address when placing an order, since his customers could have several possible addresses. Some of you have already tried giving him advice, but it's often easier to see the procedure demonstrated step by step.
To get the most out of this tutorial, I strongly recommend you review my previous video series on storing multiple addresses per customer. That series is five parts long, and the database I created there will be the foundation for the techniques I show today. If you have not already gone through those five videos, you should do so now. If you are a Gold Member you can download the finished database and follow along directly; otherwise, you will need to create your own as you move through those earlier lessons.
I am using the completed version of that customer-addresses database today. The Customers form in this database allows for several addresses per customer. For instance, I have myself as a customer and I have James Kirk in there too, each with more than one address.
The first step in making address selection possible is to build a query that brings together each customer's data with their addresses. On the orders form, I want to have a combo box below the customer selection field, so once you choose a customer, you will only see a list of addresses that belong to that customer.
To make this happen, I need to build a query that can serve as the record source for the address combo box. This query will include all of the address records in the database. As a refresher, the system uses a junction table to link customers to addresses, which makes it possible for more than one customer to share the same address. This is useful for situations such as a husband and wife who each have customer accounts but live at the same address.
By bringing in the junction table information and the address type, I can create a list of available addresses for a given customer, including details such as whether the address is for billing or shipping purposes.
The query should pull in details like the address, city, and description (billing or shipping) to make it easy for users to recognize each address in the combo box. To create a user-friendly display, I build a calculated field that combines the street address, city, and the address type in parentheses. This format will make it clear in the combo box which address is which.
If you only want to display certain types of addresses, such as shipping addresses, you can filter the results in the query. Some businesses may only need to see one type, while others may want to give the user a choice of any available address. You might even want to add extra logic, for example, defaulting to the shipping address if one exists, otherwise showing the billing address. For now, I will keep it simple and display both types.
I save this query as "Address With Type Q" so I can use it for the combo box on the order form.
Next, I return to the order processing form. In the last series of videos, I had removed the order button, but I will be adding it back now. Fortunately, the code behind the old button is still available, so if you simply create a new button with the same name ("order BTN"), the necessary code will automatically be tied to it.
Once the order form is ready, it is time to add the new address selector. Before doing so, I need a place to store which address is chosen for each order. This requires adding an AddressID field to the Orders table, set as a long integer. This field functions as a foreign key referencing the selected address.
After updating the table, I add a new combo box to the order form using the wizard. Initially, this combo box will display all addresses. The next step will be to restrict it to show only addresses for the customer currently selected. For the combo box, I use "Address With Type Q" as the data source and configure it to display only the formatted address description. The bound field is AddressID, which links back to the Orders table. I label the combo box "Address" and position it neatly on the form.
With this setup, the combo box currently lists every address in the database. The next job will be to limit the list to those that belong to the selected customer. I will cover how to do this in the next video.
Before wrapping up, I rename the combo box control to "address combo" which will make referencing it easier in future steps.
For now, the work in this lesson sets up the basic foundation: users can now pick from a list of all possible addresses when entering an order. In my next video, I will show you how to filter the list so it displays only the addresses associated with the selected customer.
If you are a member, you can continue with the next lesson right away. Otherwise, stay tuned for the next public release.
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 to join customers with their addresses
Adding the address type to the query
Building a combo box to pick an address on the order form
Formatting address information for display in a combo box
Adding a calculated field for a full address description
Saving chosen address information to the order table
Adding the foreign key AddressID to the order table
Using the combo box wizard to set up the address picker
Binding the address combo box to the AddressID field
Customizing the combo box display width and appearance
Renaming form controls for clarity and future use
Article
If you are managing customers in Microsoft Access and each customer can have multiple addresses, it is important to set up your database so that you can easily select the correct address for each order. This is especially useful for scenarios where a customer might place an order to be shipped to a different location, like a home or office address. In this tutorial, I will walk you through the process of configuring your database and forms in Microsoft Access so you can choose from multiple addresses for each customer when creating a new order.
First, ensure your database is structured so that each customer can have several addresses. This is typically accomplished with three tables: one for customers, one for addresses, and a junction table that connects customers to their addresses with an address type (for example, billing or shipping). This allows flexibility, such as sharing an address between different customers if needed.
Begin by building a query to join together the customer information with their addresses. Open the query designer, add your address table (which lists every address in your system), and then add your junction table that links each address to a customer along with the type of address. Optionally, also include a table or lookup for the address type description, such as "Billing" or "Shipping". This will let you assemble all the necessary fields to display to end users.
Next, add the fields you need to your query: include the address details, the customer ID (so you know which customer each address belongs to), and the address type description. To display information more clearly in the combo box later, create a calculated field called FullDescription. In the query design grid's next available column, enter the following expression:
FullDescription: [Address] & ", " & [City] & " (" & [Description] & ")"
This will let users see at a glance the full address, city, and type of address when picking from the list.
You may prefer to limit the addresses shown to just shipping addresses, or to include all available addresses for the customer. You can handle this by adding a filter in your query, for example by specifying that the AddressTypeID must match a certain value. However, in many real-world cases, customers might not have a separate billing and shipping address, so it is usually best to show all addresses unless you specifically want to filter by type.
Save the query with a descriptive name, such as AddressWithTypeQ.
Now, turn your attention to the order entry form. Each order should be able to store which address it is shipping to, so you will need to add an AddressID field to your Orders table. Set its data type to Number (Long Integer), as this will be a foreign key linking to the address table.
Open the order form in design view and choose where you want the address selection to appear. Usually, you would place this as a combo box right below or near the customer selection. If space is limited, move other controls down to create room.
Add a combo box to the form using the Combo Box Wizard. When prompted, select the option to use a table or query as the source, and select the query you built earlier (AddressWithTypeQ). For the combo box fields, include the AddressID (which you will store in the Orders table) and the FullDescription field (which is what the user will see in the dropdown). Hide the AddressID column by setting its column width to zero, so users only see the readable address information. Adjust the display width of the combo box as needed to ensure all address details are visible.
Bind this combo box to the AddressID field in your Orders table. This way, whenever an order is created or edited, the selected address gets saved correctly on the order record.
At this point, if you test out your form by creating or editing an order, you will notice that the address combo box shows all addresses in the system. To filter the list to show only addresses that belong to the currently selected customer, you will need to add a WHERE clause to the combo box's Row Source property. This will involve a small VBA step: set the Row Source property dynamically based on the current customer. Name your combo box something appropriate, like AddressCombo, so it is easy to refer to in code.
That covers the process of creating a system where you can pick an address from multiple possible entries when placing an order in Microsoft Access. You have set up your tables properly, created a joined query for readable address selection, added the combo box to the order form, and prepared it for further filtering so that only addresses for the selected customer show up. If you want to take it further, you can look into adding VBA code to requery the combo box when the customer changes, ensuring that the address choices are always relevant to the current customer's selection. For now, you have the structure in place to pick from multiple addresses for an order and save that choice efficiently in your database.
|
