Pick Address 2
By Richard Rost
25 days ago
Pick from Multiple Addresses for an Order - Part 2
In this Microsoft Access tutorial, I'll show you how to set up your order form so you can pick an address from your customer's address list and apply it to an order. We'll learn how to limit address selections to just those belonging to the current customer, adjust the SQL row source, use the After Update event to refresh the address list, and fix issues in your order invoice query so addresses display correctly. This is part 2.
Members
There 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!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, customer address list, order form, combo box, limit address list by customer, SQL row source, WHERE condition, After Update event, requery combo box, Design View properties, OrderInvoiceQ, left join, address table, Expression fields, order entry system, update order address
Subscribe to Pick Address 2
Get notifications when this page is updated
Transcript
Today's part two is about how to pick an address from your customer's address list and put it on an order. If you haven't watched yesterday's video, part one, go watch that first, then come on back.
Are you ready? Here we go.
Alright. So in yesterday's video, we got our customer form, our order form, and our combo box. Now we need to limit this list to just addresses for this customer. Let's take a peek at the SQL in this box.
Go to Data here. Here's the row source. Let's press Shift+F2 so we can see this clearly. Now let's clean this up. Since we're only getting data from one query, we don't need all this extra AddressWithTypeQ stuff. Get rid of that. We don't need the brackets because we're good little Access developers, and you learned with me that I don't use spaces in my field names, so we don't need those brackets. You really don't need that semicolon at the end, so let's clean that up. Let's move the FROM down and the ORDER BY down. It just makes it easier to read.
So there's our SELECT, those two fields, FROM that query, ORDER BY this. Now we need to throw in a WHERE condition, so right after the FROM but before the ORDER BY, we're going to put WHERE in there. We want the customer that's currently on the order, and that's in the customer combo box. This is the address combo box. All we have to say in here is WHERE CustomerID equals CustomerCombo. Like that. Oh, Combos. Now I'm hungry.
Hit OK, and then we're going to close it, save it, close it, and open it. Now drop this box. Look at that. There's just that customer's orders.
Let's take another customer. Let's go to James Kirk. Open it up, drop that down, and there's his. Parkside, Riverside, Somewhere Avenue. Those are his.
So now that's working, but there's one more little thing we need to do. Remember I said at the beginning of part one that there's one little teeny tiny line of code that we're going to need? Well, if you change who this customer is, you need to update that list right there.
We need to put something in this After Update event for this combo box that makes this guy requery itself. It's really easy. It's one line of code.
Go to Design View. Open up the properties for the customer combo box. Go to Events. Find the After Update event and hit the builder button.
If you haven't watched my previous VBA videos, I'm pretty sure that was a prerequisite for, I think, part five of the previous series. But go watch Intro to VBA. You'll find a link on that video. Also watch the After Update event video if you want more information on that.
Right in here, all you have to do is put one line of code. It's literally: AddressCombo.Requery
That's all you need. What that says is when this combo box has its value changed, right after update, then requery the address combo box, which means to take a look at the new customer combo. It's going to download all those addresses into this box.
That's it. Requery the address list when the customer combo changes.
Debug compile once in a while. Then close it, close it, save it, open it.
So we've got James Kirk in here. Let's see. There's James Kirk's. Now if I change this to get E Lee, there's his, Toronto. If I change it to William Riker, there's his. So that's working now.
Let's go back to an actual order. Let's go back to here.
Now, the next place we have to put it is in the actual order itself.
If I hit Invoice now, I get my customer T, that address. Why is that? What does "Enter Parameter Value" mean? I have a whole separate video on "Enter Parameter Value."
This basically means that whatever you've got going on here, a query or whatever underneath this report, is looking for a value that doesn't exist anymore. So hit Cancel.
Let's see what's going on here. Open up the OrderInvoiceQ. That's what the invoice is based on. If you don't remember, go to the Order Invoice. That's the report in Design View. It needs a field called Address, City, State, Zip, all that stuff.
Whenever you can't figure out "Enter Parameter Value," just work backwards from the thing you're trying to open.
Everything looks fine here. Go to Data. OrderInvoiceQ. Let's analyze this. You can open it right from here, but I don't like doing that. I don't like opening stuff from inside of other stuff. I'll close that and go back to the OrderInvoiceQ. Design View.
Let's see here. Oh, look at all this. Oh, Expression1, Expression2. Whenever you see Expression something and you're not expecting it, what happened was we actually, in the last series, deleted the address fields from the customer table because we took them out of there and put them in the address table. So city, state, zip, country, all that stuff no longer exists. We're going to delete those. But we need them for the invoice.
So where are we going to get them from now? Now they're in the address table. Our order has an address ID. Guess what? AddressT, meet the query.
We're also going to make sure we make this a left join, because remember what this does. If we have an order where we didn't pick an address, we still want to see the order. It just won't have an address on it. If you leave this as a double join, as an inner join, then you have to have a record in each table.
Now that we've got that, we can add this stuff back to the query. Save it, run it if you want to see. Oh, there's my--oh, wait, these are blank. Maybe I didn't pick one for this order. Let's see. This is order one. Close it. Yes, see, exactly, I didn't pick one.
So let's pick the Daniels address at the invoice, and now it works. There's the Daniels invoice. And if I change to the One Street Buffalo, it updated to the One Street Buffalo address.
That's it. That's all you have to do. It's not that complicated. The tough part is just getting the list of addresses for that customer in this combo box. Then you can pick an address and just update your order so it's got the right address in it.
Not that hard.
Now, this is mostly the kind of stuff that I spend my entire Expert series of classes going over. I have 32 levels of Expert classes. A level is at least an hour long, most of them are longer--90 minutes, two hours, some of them. This covers all the more advanced than beginner stuff, but not quite developer stuff. You don't need programming for any of this stuff, with the exception of maybe the occasional one-line code or a tiny macro if you want to automate something, like changing that combo box that we did today.
But by and large, 99 percent of this material requires no programming, but it's all the more advanced stuff. I don't want to say advanced, because I also have an Advanced series. It's all about macros and events. Expert is kind of sandwiched in between there.
But check it out--tons of stuff on my website. I go through examples like this. It's mostly about order entry and running a small business and that kind of stuff. Here's the order entry system. Functions and sharing data, you name it. You can find all the outlines on my website. Check them out.
That's going to do it for today, folks. That's your TechHelp video. I hope this helped you out. Live long and prosper, my friends. I will see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select All to receive notifications whenever I post a new video.
Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that Show More link right there. YouTube is pretty good about hiding that, but it's there. Just look for it.
If you have not yet tried my free Access Level 1 course, check it out. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, and all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below that you can click on, and did I mention it's completely free? If you like Level 1, Level 2 is just $1. That's it. It's free for members of my YouTube channel at any level.
Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there are hundreds of them by now. They also get one free Beginner class each month, and yes, those are from my full courses.
Gold members get the previous perks, plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my Code Vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free Expert class every month after completing the Beginner series.
Platinum members get all of the previous perks, plus they get all of my Beginner courses, all of them from every subject. You get one free Advanced or Developer class every month after finishing the Expert series. You can become a Diamond Sponsor and have your name listed on the Sponsor page on my website.
So 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 next time.
TOPICS:
Cleaning up SQL Row Source for address combo box
Adding a WHERE condition to filter addresses by customer
Connecting combo box filter to selected customer
Testing filtered address list for different customers
Adding VBA code to requery address combo after customer change
Setting up After Update event for customer combo box
Updating address combo box based on customer selection
Troubleshooting "Enter Parameter Value" error in reports
Analyzing underlying queries for missing address fields
Modifying OrderInvoiceQ to retrieve address from address table
Adjusting query joins to allow orders with missing addresses
Adding address fields back to the invoice query
Selecting an address for an order and reflecting it on invoice
Verifying address updates on order and invoice forms
COMMERCIAL:
In today's video, we're continuing with part two on picking a customer's address for an order in Microsoft Access. I will show you how to set up the combo box to only display addresses for the selected customer, clean up your SQL row source, and add a simple line of VBA code to update the address list when you pick a different customer. You'll also learn how to adjust your queries so invoices use the correct address information, even after moving address fields from the customer table. 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 main purpose of limiting the address list in the combo box on the order form?
A. To show all addresses from the database
B. To display only addresses for the selected customer
C. To allow manual entry of addresses by the user
D. To combine shipping and billing addresses automatically
Q2. Which SQL clause is used to ensure the combo box only pulls addresses for the active customer?
A. GROUP BY
B. HAVING
C. WHERE
D. DISTINCT
Q3. What triggers the update of the address combo box when a different customer is selected?
A. The On Click event of the address combo box
B. The After Update event of the customer combo box
C. Manual refresh by clicking a button
D. The form's On Load event
Q4. What line of code is used to refresh the address combo box after selecting a new customer?
A. AddressCombo.RefreshList
B. CustomerCombo.UpdateList
C. AddressCombo.Requery
D. AddressCombo.Reset
Q5. Why is it necessary to use a left join between the orders and address tables when building the invoice query?
A. To exclude orders without an address
B. To only include matched addresses and orders
C. To ensure orders without a selected address still appear in the results
D. To combine all addresses for all customers on each order
Q6. What does the "Enter Parameter Value" prompt usually indicate in Access?
A. The database needs to be repaired
B. The query or report is missing a field or value it expects
C. A macro failed to run
D. There is a required table missing in the query
Q7. What should you do if a query is referencing a field like City, State, or Zip but those fields have been moved from the customer table to the address table?
A. Delete the fields from the invoice
B. Ignore the error since Access fixes it automatically
C. Update the query to pull those fields from the address table
D. Remove all address data from the database
Q8. What is the advantage of cleaning up the SQL and removing unnecessary brackets and semicolons when editing the row source of the combo box?
A. It makes the SQL run faster in Access
B. It improves readability and reduces confusion
C. It ensures only numeric data is displayed
D. It allows you to add more tables more easily
Q9. What happens if you do not requery the address combo box after changing the customer selection?
A. The combo box will automatically update correctly
B. The address combo box might show addresses for the previous customer
C. The order will be deleted
D. The form will close unexpectedly
Q10. For which purpose would you most likely need a small amount of code or a macro when building an order entry system in Access, according to the video?
A. To perform basic data entry
B. To create tables and relationships
C. To automate refreshing controls based on user actions
D. To import data from Excel
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 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 will guide you through how to select an address from a customer's address list and assign it to an order in your Microsoft Access database. If you have not already watched the previous tutorial where we set up the customer form, order form, and combo box, I recommend going through that first to make sure you are up to speed.
Now, building on yesterday's work, we want to make sure that the address combo box on our order form only displays addresses for the currently selected customer. To achieve this, examine the SQL statement behind the combo box's row source. Once you open up the SQL view, you might notice some extra references or unnecessary formatting, like brackets or a semicolon at the end. Since we are dealing with a single query for addresses, streamline the SQL by removing redundant parts and organizing the SELECT, FROM, and ORDER BY clauses so it reads easily.
The crucial step is to insert a WHERE condition immediately after the FROM clause and before ORDER BY. This ensures that the addresses listed are filtered by the current customer. All you need is a simple criteria specifying that the CustomerID matches the value in the customer combo box. After applying and saving these changes, you will find that the address combo box now only shows addresses associated with the currently selected customer.
To confirm this, select different customers on the form and observe that only their addresses appear in the list. This makes it much easier to assign the correct address to an order.
However, there is another important step. When you change the customer on the form, the address combo box needs to refresh itself so it shows addresses for the new customer. To handle this, add code to the After Update event of the customer combo box. Open the properties for the customer combo box in Design View, move to the Events tab, and edit the After Update event. Add a single line of VBA code to requery the address combo box. This will refresh the list of addresses every time a different customer is selected.
After implementing this, always remember to compile your code to catch any possible errors. Save and test your form by switching between different customers and observing the address options update accordingly.
We still need to address one more detail. When generating an invoice for an order, you may encounter an "Enter Parameter Value" error. This usually happens if the underlying query for your invoice report is looking for fields that are no longer available, often due to changes in where address information is stored. If you have moved address fields out of the customer table and into a separate address table, make sure that the order's query links to the address table correctly.
In the design for your invoice query, join the address table using the AddressID from the order record. It is best to use a left join here, so that even orders that do not have an assigned address will still show up on reports, though their address fields will be blank. Then, add the address fields back into the query from the address table. After saving and running the query, you should see the correct address details for each order, provided an address has been assigned.
Now, when you go back to your order form and assign an address, the invoice pulls the correct address from the address table and displays it on the report without issues.
The main challenge here is ensuring that the address combo box is limited to the selected customer's addresses and that everything updates smoothly as you interact with the form. The final touches involve making sure the invoice process works seamlessly with your revised table structure.
This kind of work is central to the topics covered in my Expert series of Access classes. These classes focus on more advanced features that go beyond beginner topics but do not always require extensive programming. Most of the tasks involve making your Access applications easier to use and more capable, often by using macros and event-driven actions. There are many resources available on my site that outline these topics, with courses focusing on order entry systems, sharing data, and practical business tasks.
That covers today's tutorial. For a complete video walk-through, including all the step-by-step instructions outlined here, visit my website using the link below. Live long and prosper, my friends.
Topic List
Cleaning up SQL Row Source for address combo box
Adding a WHERE condition to filter addresses by customer
Connecting combo box filter to selected customer
Testing filtered address list for different customers
Adding VBA code to requery address combo after customer change
Setting up After Update event for customer combo box
Updating address combo box based on customer selection
Troubleshooting "Enter Parameter Value" error in reports
Analyzing underlying queries for missing address fields
Modifying OrderInvoiceQ to retrieve address from address table
Adjusting query joins to allow orders with missing addresses
Adding address fields back to the invoice query
Selecting an address for an order and reflecting it on invoice
Verifying address updates on order and invoice forms
Article
If you want to let users pick one of a customer's addresses for an order in Microsoft Access, you can set it up so that the address combo box on your order form only shows addresses for the selected customer. Here is how you can do that, step by step, and make sure your forms, queries, and reports always display the right information.
Suppose you have a customer table and a separate address table linked by CustomerID. On your order form, you have a combo box where you pick the customer, and another combo box where you want to display just the addresses for that customer. The first step is to set the row source for the address combo box so that it limits the list to addresses belonging only to the customer selected in the customer combo box.
Open the properties window for the address combo box on your order form. Go to the Data tab and examine the Row Source property. You will likely see a SQL SELECT statement there. Clean this up by removing unnecessary table or query name qualifiers and brackets if your field names do not contain spaces. For example, you want something like this:
SELECT AddressID, Address
FROM AddressT
ORDER BY Address;
To make sure the address combo only shows addresses for the current customer, add a WHERE condition so it looks up the selected customer from the customer combo box. If your customer combo box is named CustomerCombo and your address table has a CustomerID field, modify the SQL as follows:
SELECT AddressID, Address
FROM AddressT
WHERE CustomerID = [CustomerCombo]
ORDER BY Address;
Now, when you open the form and select a customer, the address combo box will only show addresses belonging to that customer. Test it by selecting different customers and opening the address dropdown; you should see only their addresses.
However, when you change the customer selection, the address combo box does not automatically refresh to show the correct addresses for the new customer. To fix this, you need to use a simple VBA procedure that updates, or requeries, the address combo box whenever the selected customer changes.
Switch to Design View on your order form. Select the customer combo box, open its property sheet, and go to the Events tab. Find the After Update event, and click the builder button to bring up the VBA code window. Add this single line of code:
AddressCombo.Requery
Replace AddressCombo with the correct name of your address combo box if it is different. This line tells Access to reload the address combo's list whenever you change the customer. Now, whenever you pick a new customer, the address dropdown will update instantly to only show their addresses.
It is a good habit to also occasionally use Debug > Compile while editing VBA, to catch any typos or errors early.
Now, let's talk about the invoice or report associated with your order. Sometimes, when you try to generate an invoice or report, you get a message like "Enter Parameter Value." This often happens because the underlying query is asking for a field that no longer exists—in this case, perhaps you moved address fields like city, state, or zip out of the customer table and into the address table, but did not update your queries and reports to match.
If you see the "Enter Parameter Value" prompt, check the query your invoice report uses. Open your invoice report in Design View and look at the Record Source property to see which query drives it—for instance, it might be called OrderInvoiceQ. Open that query in Design View. If you see placeholder fields like Expression1 because city, state, zip, or other address information was removed from the customer table but your query is still referencing them, you need to remove these fields and add the address table back into the query.
Join the address table to your order table in the query using AddressID. To make sure you still see orders with no selected addresses, make this a left join from the order table to the address table (right-click on the join line and choose the appropriate join option). Add the proper address fields—address, city, state, zip, etc.—from the address table to the query. Save and close the query.
Now, when you pick an address for an order and run the invoice, the correct address is displayed. If you change the address selection on the order, then run the invoice report again, it will automatically pull the updated address information.
This setup is efficient and avoids confusion—you can easily see just the addresses belonging to the chosen customer, and your invoices always print with the right ship-to information. The trickiest part is filtering the address combo box by customer and then keeping forms and queries in sync, which is solved by properly linking your combo box's row source SQL and using the simple one-line VBA requery.
As you can see, this process does not require complex programming—just a clear understanding of how relationships and forms work in Access, and a touch of VBA for automation. This type of address lookup and selection is a core topic in Access order entry systems and is essential for anyone building small business databases. Try these steps in your own database and watch how much smoother order entry becomes when addresses, customers, and invoices are all linked together and always up to date.
|
