Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today is part two of my Bill To Ship To video series, where I'm going to teach you how to copy the Bill To address to the Ship To address.

All right, this is part two. If you haven't watched part one yet, you know what to do - go watch part one, then come on back.

All right, yesterday we got everything set up. We got our fields added to the table. We got the fields to add to the form. Now we just have to make our little button. We're going to start off by making the button with the macro. Because I know a lot of you are scared of programming, which you shouldn't be. You're going to see after this how it's easier. I think it's easier to do in VBA than it is to write the macro. That's just my opinion.

And one thing that I was thinking of in the last video, after I finished it of course, was could you use a tab interface to do this? Yeah, you could. Personally, I like to see these on the screen at the same time. A tab control is where you can click Bill To and then Ship To and switch between them. They're okay. I just, I don't know, I got a bad taste in my mouth for the tab control because when they first came out years ago, they were glitchy. They didn't work well. So, I didn't use them. I don't like them. But that's just me.

If you're wondering what I'm talking about, it's these little guys right? Contact info, address, more info. That kind of stuff. It's okay for stuff you don't need to see all the time, but I'm not a big fan. Anyhow, let's make a macro. Now since I am a VBA developer, I have a certain setting that I've changed in Access that I have to go and change back now so I can actually make macros. In fact, one of the things I teach in my VBA lessons is how to turn this setting on.

So I have to go into File and then Options. If you're a VBA person that has done this, you'll remember this: go to Object Designers. And then I have to turn off Always Use Event Procedures. If you have that box checked on, that means that you won't get the option to make a macro. You'll get that little builder box button. You'll see what I'm talking about. It'll always just throw you into the VBA editor, which is what I want. But to teach you macros, I have to turn that back off.

Okay, so now I can right-click Design View. I can go to my form design, grab a button, drop the button anywhere down here. All right, the wizard comes up. We're going to cancel the wizard. We're going to change the caption here to Copy Bill To Ship To. Like that. That sounds good. Copy To Ship To. And we'll put it between these two in a minute.

All right, let's open the button's properties up. And we're going to change the name of the button. Always name your buttons. We'll call it Copy To Ship To BTN for button. Give it a good name. Okay, now go to the Events tab, find On Click, and click the dot, dot, dot button. This is the builder that I turn off normally. Because I don't want to use the macro builder or the expression builder. I always want to go into the code builder. But today we're going to use the macro builder. So pick Macro Builder and hit OK. That brings up this guy. This is the Macro Builder.

And a Macro Builder is pretty much the same thing as VBA code. It's just it gives you a list of options you can pick from. And these are pretty much the same as the VBA command you can use. It's just kind of packaged in a nice pretty interface. Now, what we want to do is - let me close this for a minute - we want to take this address and copy it to this Ship To address field. That's all we want to do. Then do the same thing with city, then state, and then zip. We're going to take a value, and we're going to copy its value down here.

All right, we're going to use a macro command called SetValue. We're going to set the value of one field equal to the value of another field. All right, let's go back in the Macro Builder, right-click this, and go to Build Event. Macro Builder, that's the same thing as clicking on that dot, dot, dot.

Okay, so we want the SetValue command. So come in here and scroll down the list, and let's see here. Um, yeah, the list is really long. Let me move this so you can see the whole thing. Okay, I decided down because the list is gigantic. But it's actually not that gigantic because what we want isn't on here. SetValue isn't on here. Why? For some reason, Microsoft deems that it's not a safe command. I don't see how SetValue can be an unsafe command. But in order to see it, you have to turn this Show All Actions on.

All right, this will only work in databases that have been marked trusted. In other words, the user has to be running them out of a trusted location. I have a whole separate video on that. You will run into this with any database that you have VBA code in it or macros that use unsafe commands. So go watch this if you want to learn more about it. You basically, if you're going to distribute this database, your user has to create a trusted location. That's a pain, but it's for safety.

But the real issue is I don't understand why Microsoft made SetValue an unsafe command. So turn Show All Actions on. Click on that. Now when you open up this box, you'll find SetValue in here. Where is it? There it is right there. SetValue. Okay.

What is the item that we're setting? We're going to set the Ship To Address. There it is right there. You can click on it or double click on it, and then we're going to set that equal to Address and then double click on that. And that's it. That's all you got to do for that field.

All right, next one, same thing. SetValue. And then just do them all. Ship To City to... Let me double click on it, and then we're going to set it equal to City. There you go, and double click. All right, SetValue. Same thing here. Ship To State. Set it equal to State. Okay, and we just do all five of them. So one more SetValue, where are you?

We got Ship To Zip and set that equal to Zip. Last one, Ship To Country. Oh, SetValue, the SetValue. See, one of the reasons I prefer VBA code is because you can copy and paste this, and it's easier to see hot and run. You mentioned this is just clunky, I think. Ship To Country, and then Country. Not County, Country.

Okay, so there's our five fields. That's all we're going to do with this macro. We're going to save it. Close it. We're going to close this. Close it. Save changes. Yes.

All right, now we're ready. Click this. Click the Copy To Ship To. Boom, and there you go. That will copy all these fields down to here. Go to somebody else. Click the button, and there you go. If you want to make changes, you can. We're actually literally copying the values from these fields into these fields. You can make changes once you've done that.

Now that will work just fine. If you want to stick with the macro, stick with the macro. But if you want to see how easy it is to do that with VBA, come back for tomorrow's video. So you know the drill. Tune in tomorrow, same bat time, same bat channel. We'll do the same thing with VBA this time, though, and you'll see how simple and easy it is.

And of course, if you're a member, you can watch it right now. And we're also going to make a packing slip because we have the invoice, right? Because the whole point of this is we go into orders, right? When we generate our invoice for this customer, the invoice has the billing address on it. Well, we want to put a packing slip in so we know where to ship this thing to, right? So we'll make a packing slip also. That'll get this address instead.

If you want to learn more about macros and event programming, I have six advanced lessons. These are more than expert. They're more than just basic functions and stuff, but they're not quite to developer level. So if you want to learn how to do some simple automations, if-then statements, you know that kind of stuff, these six lessons cover all of that.

Okay, and in fact, I go over this exact example and in a little more detail too. Let me do things like moving items between list boxes, macro error handling, all kinds of stuff. All right, so check these out. These are my advanced classes. I'll put a link down below. And that will be covered tomorrow in part three.

So, there's your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part three.

TOPICS:
Copying Bill To address to Ship To address  
Creating a button with a macro in Access  
Changing button properties in Access  
Using the Macro Builder in Access  
Introduction to the SetValue command  
Handling trusted locations in Access databases  
Using macros for setting field values  
Alternative to macros with VBA  
Creating a packing slip in Access  
Managing tab controls in Access forms

COMMERCIAL:
In today's video, we're continuing our Bill To Ship To series, showing you how to seamlessly copy the Bill To address to the Ship To address in Access. First, we'll set up our form and add a button to trigger a macro. If you're new to programming, don't worry, we'll start with macros before moving on to VBA in the next video. You'll learn to safely use the SetValue command, despite Microsoft's restrictions, allowing trusted database operations. Stick around to see just how easy this process can be, and tomorrow, we'll explore doing the same thing with VBA. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.