Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Multiple Addresses 2 < Multiple Addresses | Multiple Addresses 3 >
Back to Multiple Addresses 2    Comments List
Transcript Pinned    Upload Images   Link   Email  
Richard Rost              
7 months ago
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 relationships

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Multiple Addresses 2.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 11/10/2024 9:17:22 AM. PLT: 1s