Bill To Ship To 2
By Richard Rost
2 years ago
Copy Bill To to Ship To in MS Access Order Part 2
In this Microsoft Access tutorial, I will show you how to automate copying the Bill To address to the Ship To address using a macro without needing any programming skills. We will explore the use of the SetValue command in the Macro Builder, including how to handle trusted locations. This is part 2.
Members
There 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!
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Copy Bill To Ship To, Microsoft Access tutorial, Access Learning Zone, Access macro, Access SetValue command, create button in Access, Access form design, Access event programming, macro building in Access, Access trusted database, Access packing slip creation.
Intro In this video, we'll continue our Microsoft Access Bill To Ship To series by showing how to create a button on your form that copies the Bill To address fields to the Ship To address fields using a macro. We'll walk through setting up the button, working with the Macro Builder, using the SetValue command, and discussing trusted locations in Access databases. You'll also see tips on naming controls and hear some advice on using tab controls in forms. This is part 2.Transcript 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.Quiz Q1. What is the purpose of the button being created in the video tutorial?
A. To create a new Bill To address
B. To delete an existing Ship To address
C. To copy the Bill To address to the Ship To address
D. To format the address fields
Q2. What setting does the instructor have to change in Access to create macros?
A. Enable Developer Mode
B. Turn off Always Use Event Procedures
C. Increase Macro Security
D. Enable Macro Designer
Q3. Why does the instructor prefer using VBA over macros?
A. VBA does not require any settings changes
B. VBA has a simpler and more flexible interface
C. Macros are less visual
D. Macros are only available in older versions of Access
Q4. Why does the instructor have a "bad taste" for tab controls?
A. They were not available in early versions of Access
B. They still have unresolved bugs
C. They were glitchy when first introduced
D. They are aesthetically unpleasing
Q5. What command is used to set one field's value to another in a macro?
A. CopyValue
B. GetValue
C. SetValue
D. ValueAssign
Q6. What must be done for a database to allow the use of "unsafe" macro commands like SetValue?
A. Enable Auto-Save
B. Disable macro security in Windows
C. Mark the database as trusted and run it from a trusted location
D. Use an administrator account to run the database
Q7. What is the purpose of turning on "Show All Actions" in the macro builder?
A. To view macros shared by other users
B. To access commands that are considered unsafe by default
C. To simplify the macro list
D. To enhance the visual appearance of the macro builder
Q8. What additional resource does the instructor offer for learning more about macros and event programming?
A. A complementary e-book
B. Access to free online forums
C. Six advanced lessons on automation
D. One-on-one coaching sessions
Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-C; 7-B; 8-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 comes to you with a continuation of our Bill To Ship To series. I am Richard Rost, your instructor. This session, which is part two, will focus on how to automatically copy the Bill To address to the Ship To address using Microsoft Access. If you haven't seen the first part, make sure to complete that before proceeding.
In our last session, we laid the foundation by adding the necessary fields to the table and form. Now, we are ready to add a button to our form. I realize many might find the notion of programming intimidating, but after this, you'll see how using VBA can be simpler than relying solely on a macro.
As I concluded the previous session, it occurred to me that you could potentially use a tab interface for this task. While some might prefer using tab controls to switch between Bill To and Ship To sections, I prefer having both visible at once. Personally, past issues with tab controls left me less fond of them, although this is simply my preference.
To begin, we'll create a macro. As a VBA developer, I typically enable a setting in Access that forces the use of Event Procedures, and now I need to adjust this back to demonstrate the macro process properly. If you're familiar with VBA settings, you'll know to go to Options under File, then Object Designers to disable 'Always Use Event Procedures'. This change enables us to use macros.
In Design View on your form, select and place a button. When the wizard appears, cancel it and rename your button. I'll call it 'Copy To Ship To BTN'. Naming your buttons is essential for clarity. Navigate to the Events tab, find the On Click event, and select the option for the Macro Builder. Here, macros provide a straightforward interface, similar to VBA commands.
Our goal is simple: to replicate the Bill To address information into the Ship To fields. We'll use the SetValue command. This command sets one field's value based on another. However, SetValue doesn't appear by default. You'll need to enable 'Show All Actions' because Microsoft considers it unsafe for some reason, requiring the database to be in a trusted location.
Once enabled, locate SetValue in the Macro Builder and specify each field with Ship To settings matched to Bill To, such as Ship To Address to Address, Ship To City to City, and so on, including State, Zip, and Country. Once complete, save and close your macro.
With the macro in place, clicking your newly created button will copy the billing details to the shipping fields automatically. This method of using a macro works well, but tomorrow, I plan to illustrate how to accomplish the same task using VBA, showing its efficiency and simplicity.
For members interested in progressing further, we will also go over creating a packing slip for order shipments. This slip will utilize the shipping address fields. Besides, I offer an extensive series of lessons on macros and event programming that go beyond the basic functions, perfect for automating processes and understanding if-then statements.
For those eager to learn more now, remember that if you're a member, you can access the next part immediately. Join me tomorrow as we continue with VBA programming. You can find a complete video tutorial with step-by-step instructions on all the topics discussed today on my website. Live long and prosper, my friends.Topic List 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 |
