Multiple Addresses 2
By Richard Rost
2 years ago
Storing Multiple Customer Addresses in Access, Part 2 In this Microsoft Access tutorial, I will show you how to manage multiple addresses for a customer by using separate tables and establishing key relationships. You'll learn how to design an operational structure to differentiate between address types like billing and shipping, and incorporate combo boxes for dynamic data interaction. This is part 2 of our series. MembersThere is no extended cut, but here is the database 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!
PrerequisitesLinksUp Next
Keywords TechHelp Access, storing multiple addresses, address table setup, customer form customization, address type table, relational combo box, foreign key management, database design, combo box tool, address form layout, entity relationship, tab order customization, list items edit form, many-to-many relationship, data entry optimization, debug compile, VBA troubleshooting, Access design tips, user interface customization, address type combo, multi-address handling
Intro In this video, we continue our exploration of managing multiple addresses in Microsoft Access. You'll learn how to create an address type table to categorize addresses like billing, shipping, or office, add a foreign key to your address table, and set up a combo box to select address types on your forms. I'll show you tips for form design, setting tab order, and keeping your tables organized. We'll also cover how to handle a common VBA error and discuss best practices for managing shared addresses and preparing for many-to-many relationships. This is part 2.Transcript Today we're back at it with multiple addresses. This is part two. That means if you haven't watched part one yet, go watch part one and come on back. I'll wait for you. Alright, so yesterday we took the address field that we had in the customer table and we added an address table.
Alright, so now if we go to our customer form, you can see here's that customer's address. But how do you know which one's which? You got physical, billing, shipping, home, office, PO, whatever. So, to store that information, we'll make another table to store our address type, and then we'll store an ID in the address table to store that type. We wouldn't type that in here like billing or shipping or whatever. No, no, no, no, no, no, no. So, we're going to make another table. Table design, we'll call it address type ID. That's your auto number. OK. And then a description or whatever other information you want to put in there. You could also put stuff in here like, is this a mailing address? In other words, do you want their flyer to go to this address, that kind of thing.
So, if you do have a customer who's got three different addresses, where do you want to send the flyer to? Or if it's a situation where it's seasonal, maybe you want to send a copy of it here and a copy of it there. So that's up to you. Anything you want to put about this type of address, you can put in here.
All right, we'll save this as our address type table. OK. And now what we'll need to do is take address type ID and stick that in the address key. All right, so go to address key, design view, come down here, pasted it. Oh, I didn't copy it. Address type ID, that's a number of type long integers, a foreign key. It points to an auto number in a different table. Now, just a style thing for myself, I like to keep all the IDs up at the top of the table. That's just a RIC thing. That's a me thing. Right? Primary key goes first, then any foreign keys go next, and then all the supplemental data goes after that. Ok?
All right. Save it. Close it. Now how do we use that field? We're going to add it in here as a combo box. Right? So let's go to our address form. We'll design that puppy. Oh, I said puppy. My dog just looked at me. All right, so we're going to slide all this stuff over, like, about as far as you think you're going to need it to be. Oh, this is a cheesy, a cheap label. So you can put it here, and we can go in here, and we can put type, like that. See, there are some benefits to using one big label up here. I don't always do it, but sometimes I do it, like now.
All right, we'll grab our little combo box tool, this dude right there, we'll drop it in here. We're going to get the values from a table or query. This is that relational combo box video I told you to watch before part one. All right, where are you getting your list of values from? The address type table. Bring over both fields. We are going to sort it by description. Ok, we don't have any data in there. Yeah, that's good. This is why I like to put data in there first. I forgot that step, and you can see why it's important to put some sample data in. Even if it's customers, you put like Joe Smith in there because when you do things like this, now I can't see my columns. The key column is hidden. That's the ID, of course. We want that hidden. Description will go here, alright, like you're billing, shipping, whatever.
All right, now once the user picks that, we're going to store that field in the address type ID in the address table. The field we just created. What label do you want? Doesn't matter. We're going to delete it anyway. One more thing I am going to do that I wish the wizard did is give this box a name, because the wizard doesn't give a name sometimes. Address type combo. I like to call them combos. You could call it ID if you want to. That just tells me later on it's a combo. All right, there's the label that comes with it, delete that, we're going to slide this guy up in here. It's just slightly unsized, that's another one of my little pet peeves, so we're going to select everything, right-click, you're going to go size to grid, that'll make sure everybody snaps to the exact same size. And since we just added this field, it's going to be last in the tab order, so we're going to fix our tab order. Click here, go to tab order, hit auto. That should fix everything in the correct order. And now we're all set.
Bring that bottom back up. Save it, close it, open it, and there you go. Now you can see we've got to make this a little bit wider now too. That happens. You can make your address field smaller if you want to. Ok, yes, I know there's a layout view. I really hate layout view probably because when it first came out, I've had nothing but problems with it. You can come in here and switch to layout view, and then with layout view, you can make this bigger. It's easier to see the size of it in here. But honestly, I've had so many problems with layout view over the years. I've had so many problems with layout view, I stopped using it. When you have a lot of VB code in your form especially, I've had that mess up. Now, I don't know if it's gotten better. I'm assuming it probably has. But for me personally, I tend to stay away from layout view. That's just me. All right.
And now we can come in here and we can pick... Oh, I don't have any data in here, do I? All right. Close that down. Open up the address type. And in here, we got physical, we got office, we got PO box, whatever types of descriptions for addresses you want to have in there. That's completely up to you. Now, this is the case where if you want to have a list items edit form, you can. We're going to do this in a bit. We're going to create one for our addresses when we make it menu to menu. But if you want to throw a simple one together right for now so the user can change these, sometimes you want the users to be able to modify these, sometimes you don't. Some of these combo boxes you want to leave it so the admin or at least someone who knows what they're doing, a manager, is the one that makes the final decision on that. An address, no, that's a user thing. You want your users to easily be able to add new addresses. But address type, I don't know, that's up to you. Me personally, I'd stick away from it. I wouldn't put a list items edit form here. That's just me. Unless you're the only one that uses the database, you want to make it easier for yourself. Just put a button somewhere. That opens up a form, it's linked to that. That's how I would do it. So there's maybe their office address, maybe this is their PO box, whatever. Go to the next person, whatever this address is, physical. One somewhere, Avenue, blah blah blah blah blah, whatever. All right, now the next thing we're going to talk about is the many-to-many relationship.
What if, let's say this 6900 Daniels Parkway. Let's say that you've got multiple customers who share that address. They're separate customer records, okay, but you want to be able to have them share a same address. You want to know it's the same address for data entry. Say another one of these customers and it just pulls up the same address for a different customer. May come over here, it's a typing in all, all, all. That's interesting. I've seen this one before. This is all the weird errors, right? The link master fields property setting is produces return without goes up, return without goes up. I don't have any subroutines, whatever. If you get a weird error message like that and you can't explain it, go into your VB editor. If you've never used the VB editor before, that's okay. Watch my intro to VBA video. I'll give you a link to it in a second. Just come in here and go to debug compile. Trust me, debug compile, shut it down, and then open it back up again. All right, now watch. Come in here, type something in, and look. That error went away. That's one of those weird.errors that shows up. In fact, I'm going to probably make a video about it, but I can't recreate it. So now that I've just recreated it, I might steal this video and make a separate video about it. Because it only shows up randomly. It only shows up here and there. The return without go sub error, especially when you haven't done any VB coding. Return without go sub is basically a, it's a VB programming thing. Okay. But that's how you fix it. And sometimes just closing your database and restarting it fixes it. But a lot of times you have to compile the database and then it fixes it.
So, yeah, I know I said we wouldn't need any VBA and we don't. This is just a weird error and I'm kind of glad it came up. I've been wanting this error to pop up in a video for a while. But go watch this if you want to learn more about VBA. And go watch this video if you want to learn more about that debug compile. And it's helpful if you get weird error messages like that sometimes. But anyway, back to the database. That's what we're going to cover in the next video.
Alright, if we go back to our slides, we got one customer who's got four addresses. And we got another customer who's maybe related to this one, and she shares an address. But she also has another address of her own. And that's why we need to make this a three table setup, a many to many setup. So if you haven't watched the many to many video yet, watch that before part three. And of course, tune in tomorrow, same bad time, same bad channel for part three. Members, you can watch right now. I know I said after yesterday's video you can watch right now, but I kind of took a break and then didn't get back to it. I'm a day late on that one. But I am going to record part three right now because I just had my coffee so I'm good as you can probably tell. Alright, so for the rest of you, that's going to be your TechHelp video for today. Hope you learned something. Live long and prosper. I'll see you tomorrow for part three.
TOPICS:
Designing and linking multiple database tables
Creating and utilizing an address type table
Using auto number data types
Adding foreign keys to database tables
Table design practices
Implementing and configuring a combo box
Binding combo box to database fields
Database form design adjustments
Troubleshooting and fixing database errors
Debugging in VBA
Setting up a data entry system for address management
Configuring the tab order in forms
Understanding many-to-many database relationshipsQuiz Q1. What is the primary purpose of creating an address type table in the database?
A. To store different types of products
B. To link customers directly to their addresses without types
C. To store different types of addresses (e.g., billing, shipping)
D. To increase the complexity of the database design
Q2. What kind of data type is "address type ID" in the address type table?
A. Text
B. Date/Time
C. Auto Number
D. Currency
Q3. In the context of this database setup, what does a foreign key represent?
A. A unique identifier used only within the same table
B. A primary key from another table used to link two tables together
C. A random value with no specific meaning
D. A calculation based on other fields in the database
Q4. Why should sample data be entered into the address type table before using a combo box in a form?
A. To increase the size of the database
B. Because it is required by database laws
C. To allow visual verification of the linked columns during design
D. To link all addresses immediately
Q5. Why might you want to send flyers to more than one address per customer?
A. Because customers do not have fixed addresses
B. Because it is mandatory in database management
C. If the customer has seasonal addresses or multiple relevant locations
D. To increase postage and handling costs
Q6. When would you use the layout view for modifying a form according to the tutorial?
A. Always, as it is the most reliable view
B. Never, as it is prone to causing problems with VB code
C. Only when you are entering new data into the database
D. When creating new tables only
Q7. What does the return without go sub error suggest, as mentioned in the tutorial?
A. The database is corrupt
B. There is a data type compatibility issue
C. There might be an issue with VBA code that needs debugging
D. The user has too many windows open in the database
Answers: 1-C; 2-C; 3-B; 4-C; 5-C; 6-B; 7-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 covers part two of setting up multiple addresses for customers in your Microsoft Access database. If you have not yet reviewed part one, I recommend starting there so that you are up to speed with the changes to the customer table and the creation of the address table.
To recap, so far we have moved the address field out of the customer table and established a separate address table. Now, when you view the customer form, you will see that each address is stored, but it may not be clear what type of address each one is. Customers can have several addresses for different purposes, such as physical, billing, shipping, office, or PO box. To keep track of which address is which, it is important to set up another table to store the types of addresses, like billing or shipping. This way you avoid typing those labels repeatedly, reducing errors and improving consistency.
In my table design, I add an AddressTypeID field, using the auto number data type. Alongside this, I include a description field and any additional fields you might find helpful. For example, you might want a checkbox to indicate whether it is a mailing address, or perhaps additional information to clarify usage for seasonal mailings or other situations.
After saving this as the AddressType table, the next step is to add the AddressTypeID as a field in your address table. I suggest arranging the table so that all the ID fields, including foreign keys and the primary key, are placed at the top, with other data fields following after. This is my own preference, but I find it keeps things neat and logical.
Once that is set up, you incorporate AddressTypeID into your address entry form as a combo box. In design view, I make space for the combo box, label it as "Type," and use the combo box tool to add this field. The combo box is set up to pull its values from the AddressType table, displaying the description field while storing the corresponding ID. Although the wizard does not always name the combo box field, I make a point to do so, calling it something clear like AddressTypeCombo for easy reference later.
After tweaking the layout, including resizing controls and adjusting tab order to ensure a smooth user experience, you can save, close, and reopen the form. You might need to adjust widths or move fields around; I personally avoid layout view as it has caused issues for me, particularly with forms that include VBA code, though others might have a different experience.
Adding address types to the AddressType table, such as physical, office, or PO box, is then a matter of personal choice. In some databases, you might want users to manage this list themselves, but in other cases, you might restrict editing to admins or knowledgeable managers for better control. I recommend using a dedicated button to open a management form for address types, rather than allowing direct edits through the combo box unless you are the sole user and want this added convenience.
Soon, the need for a more complex relationship may arise. Suppose two customers need to share a single address, or a customer has multiple addresses for different purposes. This is where a many-to-many relationship comes in, established through a junction table. If you want to see more on this, watch my many-to-many relationship video before tackling part three.
During form development you might encounter an odd error, such as "Return without GoSub," especially if you have not written any VBA code. If that happens, you should open the VBA editor, use the debug and compile feature, then close and restart the database. This troubleshooting tip is handy and prevents hours of frustration, as sometimes merely compiling the database resolves these obscure issues.
In the next session I will cover the actual setup for many-to-many relationships to allow for customers sharing addresses. That will round out the approach to flexible address management in your Access database.
For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Creating an address type table
Adding an AddressTypeID foreign key to the address table
Designing the AddressType table structure
Populating sample data in the AddressType table
Adding a combo box for address type selection on forms
Configuring the combo box to use the address type table
Sorting combo box items by description
Hiding key columns in combo box display
Naming controls for easier form management
Aligning and resizing controls on a form
Setting tab order after adding new controls
Switching between design view and layout view for forms
Handling display width issues for form controls
Manually adding and editing address type records
Discussing user permissions for editing address types
Demonstrating shared addresses across multiple customers
Explaining the need for a many-to-many setup with addresses
Fixing the "Return without GoSub" error
Using the VBA editor to compile and resolve form errors |
