Storing Duplicate Data 3
By Richard Rost
6 months ago
Store Historical Customer Address for Accuracy Part 3
In this Microsoft Access tutorial I will show you how to add customer address and notes data to an order form by updating a combo box to include additional fields and using After Update events and DLookup, along with tips for handling large databases and ensuring data integrity when modifying queries. This is part 3.
Members
In the extended cut, we will create a product list box that lets you pick multiple products and add them to the order using a multi-select list box and a button on the form. I will show you how to set this up so you and your users can easily select several popular products and add them to an order at once.
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, Storing Duplicate Data, After Update event, combo box address autofill, customer selection auto-fill, copying customer address to order, hide key column combo, combo box column properties, LFQ query, form design combo box, modifying query fields, updating tab order, DLookup for address, using NZ function, copying customer notes, copying fields to order form, product list box multi-select, adding products to order, denormalizing database, normalization basics
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today is part three of my Storing Duplicate Data series. If you haven't watched parts one and two, go watch those and then come on back.
So, we figured out how we can have it so we can add an order from the customer form and have this data put in there automatically for us. Now we need to be able to add an order from the main menu, pick a customer, and have their address put in here. To do that, we're going to use an After Update event.
If you don't know what an After Update event is, go watch this video. I'm going to explain it briefly, but this video will talk more about it. There are a couple different ways you can do this. One of the easiest ways is to simply load that address information in here into this combo box and then you can drop it right in here very easily using the column properties.
Let me show you that method first. Here we have a customer combo, right? Where's the data? Right here. It's based on the customer LFQ. Let's take a peek at it. Customer LFQ, customer ID, and then the LF field ordered by LF so we get last name, first name. That's what the LFQ does for all of our customers. We've got our default value here as well.
We're going to redo this combo box and include all of the address data in there. We'll go back to form design and find the wizard. You can do this by hand if you're comfortable enough with these combo boxes and all that stuff. This is one of those wizards I don't mind using. It's a good wizard.
Find the values with table or query. My LFQ is this guy. Next. Oh, wait a minute. We have to add those fields into the query first. I jumped ahead. Delete, find the LFQ, here it is. Design View.
Let's bring in the fields we want, which is address through country. Click, drag. For those who don't know, I clicked on the first one, held down the shift key, clicked on the last one, let the shift key go, and then you can click and drag and drop them all down here. Same stuff, just add those fields. Save it.
People always ask me if it is smart to go back and modify queries. Be careful if you go back and modify an older query. Usually, adding fields to it is not a problem. You can always add more stuff – make sure you don't have duplicate names. You just want to be careful if you go back to an old query and delete fields or change things because you don't know what other stuff may rely upon this. You could check the dependencies and all that stuff. I have separate videos on all that.
Form Design, one more try here. Here we go. Drop down the combo box, drop it there. Let's go to the queries, customer LFQ. Now, we're going to bring in that first visible field and you can bring in these other fields too, to have those in the different columns in the combo box.
What do you want to sort by? LF. What do you want to see? Here's the thing: remember, there's no box here to hide the key column because this is based on a query, so we have to hide that field. This is up to you if you want to leave this stuff visible. What I usually do is I leave address visible and maybe state. Then hide the rest of it. This way, if you drop the box down and you have two John Smiths, you could say, I use John Smith from 9 Division Street or 8833 Canton. You can pick the person you want more easily.
Storing it in CustomerID. Store that value in CustomerID in the order field. What label would you like? Customer. We're done.
Let me grab a little Format Painter, click, click. Resize, move around, all that good stuff, and yes, neatness matters. Let's replace that default value, replace the name customer combo. Where's the default value? Right there: =Forms!CustomerF!CustomerID. Now it's back exactly the way it was before.
Wait a second, I see I made a mistake. That's one of the reasons I love this little new thing they added in Access 2007 or whenever. I accidentally changed the Control Source to customer combo, which doesn't exist, and it's yelling at me. I do this all the time. This should remain CustomerID. That's the bound column. The name is customer combo.
That's one of my mistakes. I leave stuff like this in the video because if I make that mistake, chances are you're going to make the same mistake. Just be aware of what happened.
Something else you might want to do real quick while we're at it is just change your tab order, so you can put that customer combo where you want it, like after Description. And of course all those address fields too. Let's move these guys up here like that. Hit OK.
Now for the good stuff. Let's save it, close it. Let's open up a customer form and open this up now.
We don't have any default values in here, but this still works. That's what I want to make sure. Open it up from here now. Pick a customer and you can see there's that data. You can make this box wider if you want to so you can see it all. When you pick one of these, you want this information in the box to go into these fields.
How do we do that? We're going to use an After Update event for this guy. This doesn't happen until you pick a customer and create a record. Events, After Update.
Now, in that combo box, you have multiple columns. Sometimes I write them out in here just to keep my wits about me. Column 0 is the first column. That's the bound column. That's the CustomerID. We'll do columns.
Column 1 is the visible column. In this case, it's last name, first name. So column 2 starts your address data. Column 3 is city, and so on. You can do that here. We'll do 4 equal state, for example.
City, state, zip, and 6 is country. Knowing that information, this is what throws people: everybody forgets that column 0 is that first hidden column.
Now we know where this data is. We can very easily set that in our fields. We can say Address (which is a field on the order form) equals customer combo.column(2).
Just copy and paste: Address, City, State, Zip, Country. Here's City, State, Zip, and Country. We've got 2, 3, 4, 5, 6.
Save it. Debug Compile once in a while. And yes, that's actually on a T-shirt now.
Close it, close it, close it. Open it, we're on a new record, pick a customer, James Kirk. Boom! All the information's copied into here. Isn't that great?
There's one more thing you might want to do because you can't put all the stuff that you want into a combo box. To be honest, this works fine for small databases. But if you've got 200,000 customers, you might not want to load all of this data into the combo box. It'll slow things down, especially if you're running over a network.
One field is okay, but if you start getting 10 fields in here, it's rows and columns basically. If your database gets really big, this might get slow. Another method you can use is to use DLookup at the time that the order or customer is picked. That might actually run faster in certain circumstances.
This sometimes is easier to do. It all depends on which one you prefer. Also, with DLookup you can look up things that you can't put into a combo box, like long text.
Let's say, for example, you've got notes on your customers, like this. Let's say you want to copy these notes over to the notes for the order. In that case, what you can do is come down here and write: Notes = DLookup("Notes", "CustomerT", "CustomerID=" & CustomerCombo). In case this returns a null, you want to handle that with NZ. Null to zero, and we'll make it an empty string.
So, that will look up the Notes field from the CustomerT table where the CustomerID equals the customer you just picked. That's in CustomerCombo. If that's null, it'll return an empty string.
If you want to learn more about DLookup, go watch this video. I cover the NZ function in this video as well. And now, Debug, Compile, close it, save it, close it.
Open it, right-click, and I'll pick Geddy Lee. Now you get his address and all of his notes copied to the order.
So, that's pretty cool stuff. Continuing on with this, as I mentioned before, in the Invoicing Extended Cut I've covered that, but I've also covered how to pick a product from a product list and add that to the order. There's a little more involved, a little bit more coding, but that's what I cover in the Extended Cut.
The same logic applies. You've got a product table now where you've got unit cost, markup, unit price. If this changes, it doesn't change all of this, because this information is copied to the order.
Check that out in the Extended Cut of my original invoicing video.
Members, I've got a special bonus for you, too. This is something you have been asking me for a while. We're going to create a product list box that you can have all of your popular products on. We're going to use a multi-select list box, so you can say, okay, these are all my popular products – I want that, that, that, and that. Add it to the order, boom!
See that? That's pretty cool too. Product list box. I'm going to make a button on here to open it from there. That's what we're going to do in the Extended Cut for today for you guys.
So there, I made a slide, so I've got to use the slide, right? In the Extended Cut, we're going to make a product list box, where you can pick the product you want, hit the button, and it goes over into the order like this.
Silver members and up get access to all of my Extended Cut videos, not just this one – all of them. There are hundreds of them by now.
So what are you waiting for? Join today, click on that blue Join button, and you become part of the family. Everybody gets some free training classes.
And also, don't forget to get your Access Learning Zone swag at the merch store. I have all kinds of cool new stuff. I have this stuff and hats and you name it – you'll find the links down below. Check it out. I even have a little bunny rabbit here.
That's going to do it. That's your little three-part series on why and how you should store duplicate data in your database. I know I tell beginners not to do that, but there comes a time when you have to do that. It's like normalization. You can learn the basics of normalization, but sometimes it's beneficial to denormalize your database.
I've got a whole separate video on that. Look it up, do a Google search for it, or search on my site.
That's going to do it. That's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time, and members, I'll see you in the Extended Cut.
TOPICS:
Using the After Update event to copy customer data
Including address fields in a combo box
Modifying a query to add address fields
Setting up a customer combo box to show multiple columns
Hiding key columns in a combo box
Adjusting tab order for new combo box and fields
Copying column values from a combo box to form fields
Using DLookup to retrieve additional customer data
Handling null values with the NZ function when copying data
Copying customer notes to the order form using DLookup
COMMERCIAL:
In today's video, we are continuing with part three of the Storing Duplicate Data series. I will show you how to add an order directly from the main menu, pick a customer, and automatically have their address details filled in using an After Update event. You will see how to modify your customer combo box to include address fields, fine-tune your form's tab order, and handle situations where you want to pull in customer notes with DLookup and the NZ function. I will also talk about when this method works best and when you might want to consider other options for larger databases. In the Extended Cut, we will create a product list box with a multi-select feature so you can easily add multiple products to an order. You will 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 primary reason for using an After Update event in the tutorial's context?
A. To automatically update the form layout when a new customer is added
B. To copy customer address fields to the order form after selecting a customer
C. To save changes to the database automatically every time a field is changed
D. To validate user input when the form is closed
Q2. What is the main function of the customer combo box on the order form?
A. To allow selection of multiple customers simultaneously
B. To display customer notes as tooltips
C. To pick a customer and retrieve their address and related information for the order
D. To update the list of available products
Q3. In Access, what does the combo box Bound Column property typically represent in this example?
A. The visible column, usually customer name
B. The CustomerID field, often hidden but used for storing selected value
C. The last field in the query, usually country
D. The default value for new records
Q4. When modifying an existing query to add address fields, what is a recommended caution mentioned in the video?
A. Never add new fields to old queries
B. Deleting or changing existing fields might impact other database objects that depend on the query
C. It is always safe to rename queries as long as fields remain the same
D. Queries cannot be edited once forms use them
Q5. Why might loading many fields into a combo box be a problem in a large database?
A. It makes the combo box unsearchable
B. It prevents users from selecting records
C. It can slow down performance, especially with a large number of rows and columns
D. It disables the After Update event
Q6. What is the purpose of using DLookup in the scenario described in the video?
A. To enforce referential integrity between customer and order tables
B. To look up and copy additional information (like notes) not included in the combo box
C. To update all related records in bulk
D. To automatically filter the customer list
Q7. In the context described, what is a benefit of using NZ with DLookup?
A. It converts all values to zero regardless of result
B. It produces an error if the value is null
C. It ensures that a null result from DLookup is handled gracefully with an empty string or default
D. It only works with numeric data types
Q8. Which event is used to transfer selected customer information from the combo box to the order form fields?
A. Before Insert
B. On Click
C. After Update
D. On Delete
Q9. According to the video, what should you do if you want to check whether other objects depend on your query before modifying it?
A. Check dependencies via Access tools
B. Delete and recreate all related forms
C. Use DLookup for all queries
D. Never modify queries
Q10. When mapping combo box columns, what is the index of the first address-related field if column 0 is CustomerID and column 1 is customer name?
A. 2
B. 0
C. 1
D. 3
Q11. What is a good reason to enable multi-select in a product list box, as shown in the Extended Cut?
A. To allow choosing multiple popular products to add to an order at once
B. To sort products alphabetically
C. To filter out discontinued products
D. To enforce single product selection
Q12. Why might you intentionally store duplicate address data in an order table, despite common normalization advice?
A. To improve performance by avoiding lookups at print or query time, especially for historical accuracy
B. To minimize file size
C. To prevent data entry errors in customers
D. To maintain strict normalization rules
Q13. What does the Bound Column property in the combo box refer to in the tutorial example?
A. The On Click event setting for the combo box
B. The column whose value is stored in the related field (CustomerID)
C. The visible name of the combo box
D. The notes field in the customer table
Q14. What should you do if you want the data in the combo box to be dynamic and include newly added fields?
A. Rebuild the combo box from scratch each time
B. Update the underlying query to include the needed fields
C. Use a static list only
D. Change the Record Source to a random table
Q15. What is a drawback to using a combo box for all customer data if the customer table becomes very large?
A. Users will be unable to select any value
B. The combo box will only display 10 records at a time
C. It may significantly slow down the form's performance
D. Combo boxes are not compatible with queries
Answers: 1-B; 2-C; 3-B; 4-B; 5-C; 6-B; 7-C; 8-C; 9-A; 10-A; 11-A; 12-A; 13-B; 14-B; 15-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 builds on the Storing Duplicate Data series in Microsoft Access. This is part three, so if you have not seen the first two parts, I recommend reviewing those sections to get the foundation in place.
So far, we have learned how to add an order from the customer form with the relevant customer data being automatically filled in. The next step is to handle situations where you want to add an order directly from the main menu, select a customer, and then have their address details automatically populated. To achieve this, I will demonstrate the use of an After Update event.
If you are new to the concept of After Update events, you may benefit from reviewing my dedicated tutorial on that subject. Here, I will provide a brief explanation, but it is helpful to have a deeper understanding of how After Update works if you have never encountered it before.
There are a few different ways to approach this problem. One straightforward method is to include all the necessary address fields as columns in the customer combo box. This allows you to select a customer from the list and have their details available across the additional columns using the combo box's column properties.
Let me walk you through this approach. Suppose you have a combo box set up for customers, sourcing its data from a query called CustomerLFQ. If you examine this query, you will see that it includes the CustomerID and a concatenated last name and first name (LF), sorted accordingly. This setup lets you display your customer list in a way that is easy to scan.
To enable address autofill via the combo box, you will need to modify your query to include all the address-related fields, such as address, city, state, zip code, and country. You can do this in Design View by dragging all the desired fields from address to country into your query's grid. For those who may be new to Access, you can select multiple fields by holding the Shift key as you click. Once added, save the changes.
A common question I get from students is whether it's safe to go back and modify existing queries. In general, adding fields is usually not a problem, but use caution if you need to delete fields or modify existing structures, as other forms or objects might rely on those details. You can always check dependencies in Access to help with this.
Now, let's return to the form design and create a new combo box, basing it on the revised CustomerLFQ query. You may also want to choose which fields will be displayed when the combo box drops down. For example, keeping the address and state fields visible helps when you have multiple customers with the same name, allowing you to distinguish between them more easily.
Be sure to store the selected value in the CustomerID field for the order. It is helpful to update the label to something user-friendly, such as "Customer." Remember to keep your forms tidy by using the Format Painter and organizing controls for clarity and neatness. Reset the default value to reference any existing forms if necessary. Be cautious when updating the Control Source property and double-check that the bound column matches the underlying data.
It can also be beneficial to review and adjust the form's tab order, ensuring that users can move through the form efficiently and logically.
Once this setup is complete, you can open your order entry form, select a customer from the combo box, and see the extra address information displayed in the drop-down list. When you make a selection, you want that address information to populate the order's address fields automatically.
To accomplish this, you will use the After Update event for the combo box. In this context, each column in the combo box corresponds to a particular field. The first column (column 0) is typically the bound field, like CustomerID, followed by columns for the displayed name, address, city, state, and so forth. It can be helpful to jot down which column corresponds to which field to minimize errors.
With this knowledge, you can then use the After Update event to set the order's address fields by referencing the appropriate columns of the combo box. For example, you set the address field using the value from column 2, city from column 3, and so on up to country. Save your changes and remember to compile your code to check for any errors.
Now, when you create a new order, select a customer such as James Kirk, and all the customer's address details are immediately copied into the form fields for that order.
However, keep in mind that this approach has limitations, especially with very large databases. Loading too many fields into the combo box can slow things down, particularly in larger systems or on a network. If you have hundreds of thousands of customers, it may be better to look up the relevant data at the time of selection, rather than loading everything into the combo box up front.
In such cases, you might consider using the DLookup function. Instead of relying on extra columns in your combo box, you use DLookup to fetch values. This is also useful for retrieving data that cannot be easily represented in a combo box, such as long text notes. For example, if you want to copy notes from the customer's record into the order, you can use DLookup to fetch the Notes field from the customer table based on the selected CustomerID. To handle potential null values, wrap the DLookup in the NZ function, which will return an empty string when necessary.
If you are interested in learning more about DLookup or the NZ function, I have detailed tutorials on both topics that you may find helpful.
After implementing your preferred method, be sure to close and reopen your forms to test the functionality. When you pick a customer, all corresponding information is copied as intended.
For those interested in further expanding these concepts, I discuss in my Invoicing Extended Cut how to pick a product from a product list and add it to an order. This involves similar logic and additional coding, but the idea remains consistent–information from related tables is stored on the order when making a selection. This ensures that if product pricing changes later, your previous orders remain consistent.
Members have access to additional material, including a guide on creating a product list box with multi-select functionality. This allows you to display your most popular products in a list, select multiple items, and add them to an order with a single action. In today's Extended Cut, I walk through building this list box control and adding a button to the form for streamlined product selection.
If you are a Silver member or above, you can access all Extended Cut videos, and there are hundreds available. New members are always welcome and everyone receives free training classes when they join.
Finally, don't forget to check out the merchandise at the Access Learning Zone store, where you can find all sorts of branded gear and other fun items.
This wraps up my three-part series on when and why you should store duplicate data in your database. While I typically advise Access beginners against it, there are times when denormalization is necessary or beneficial. If you want to learn more about normalization and its exceptions, I have a separate, dedicated video on that as well.
You will 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
Using the After Update event to copy customer data
Including address fields in a combo box
Modifying a query to add address fields
Setting up a customer combo box to show multiple columns
Hiding key columns in a combo box
Adjusting tab order for new combo box and fields
Copying column values from a combo box to form fields
Using DLookup to retrieve additional customer data
Handling null values with the NZ function when copying data
Copying customer notes to the order form using DLookup
|
