Introduction

Welcome! Macros: Billing & Shipping List Box

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Advanced Level 5. In this course we will focus on advanced Microsoft Access topics, primarily working with macros to automate tasks and add database functionality. We will add separate billing and shipping addresses to customers and orders, implement these changes on order forms and invoices, and discuss how to move items between two list boxes using macros and SQL code. We will also talk about using buttons and double clicks to set employee status, and wrap up with miscellaneous topics including the AutoExec macro, macro error handling, and the macro design tab in the ribbon.

Navigation

Prev: A05
Next: Lesson 01

Keywords

Access Advanced, macros, macro events, automate database tasks, drag and drop list boxes, move items between list boxes, billing and shipping address, order shipping address, AutoExec macro, macro error handling, macro design tab, active inactive employee

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Advanced Level 5 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

This is the Advanced Series of our classes. The Advanced Series focuses primarily on macros, which allow you to easily automate tasks and add functionality to your database.

This class follows Access Advanced Level 4. I recommend you finish Levels 1 through 4 before taking this class, as well as my 9 beginner levels and my expert levels 1 through 32. The beginner series covers the fundamentals. The expert series goes into a lot more detail.

This is the Advanced Series, covering macros and events, and of course, the developer series is coming up next. Next, we will focus on Visual Basic programming.

Today's class was recorded using Microsoft Access 2016. I believe everything covered should work fine with Access 2007 through 2013. If you find something that is different, please let me know. Most of these commands have not changed much since 2007. If you are still using Access 2003 or earlier, it is time to upgrade. But if you are still on a legacy system, I do have classes for Access 2003 as well, which go into details. Check my website for those.

The sample database for today's class can be found at this page: 599cd.com/databases. Of course, I recommend you build the database yourself, starting with beginner level 1 and working your way through. That is the best way to learn, by doing all of this yourself. But if you have not done it, if you jumped in midstream and you want to download my copy of the database, you can download it from this page. You will need your course password and then you can get it right from there.

Now, let's take a look at what is covered in today's class.

In lesson 1, we are going to learn how to add a separate billing address and shipping address to our customers and our orders, so we can track where things were shipped at the time the order was placed.

In lesson 2, we are continuing with the separate shipping address. In this lesson, we are going to add the same fields to our order table so we can track where each order was shipped to at the time the order was placed, and we will put those fields on our invoice.

Now, Microsoft Access by itself does not support Click and Drag or Drag and Drop, but in this lesson I am going to show you a technique that is pretty simple where you can move items between two different list boxes. We will do an example of active employees and inactive employees, but you can use this for pretty much any two list boxes you want, where you can click on an item in one or double click on it and then have it move to the other box.

We will use this to set active employees or inactive employees. You can either use buttons in the middle or you can double click and it will jump from one list to the next. A little tiny bit of SQL code and one macro command.

In lesson 4, we are going to talk about a couple of miscellaneous topics that I have had on my list to cover that really do not make a full lesson on their own. We are going to talk about the AutoExec macro, some error handling in your macros, and we will go over the macro design tab in the ribbon.

Thanks for watching.

Quiz

Q1. What is the primary focus of the Access Advanced Level 5 class?
A. Advanced SQL querying
B. Macros and automation
C. Report formatting
D. Database security

Q2. Which levels are recommended to complete before starting Advanced Level 5?
A. Only Advanced Levels 1 to 4
B. Beginner Levels 1-9, Expert Levels 1-32, and Advanced Levels 1-4
C. Only the beginner series
D. Only Expert Levels 1-10

Q3. What will students learn in Lesson 1 of this class?
A. How to create relationship diagrams
B. Adding separate billing and shipping addresses for customers and orders
C. How to build queries using formulas
D. Setting up user permissions on forms

Q4. What does Lesson 2 cover regarding orders?
A. Adding payment processing fields
B. Adding shipping address fields to the order table and invoice
C. Incorporating inventory tracking
D. Creating product discounts

Q5. What feature does Microsoft Access not support natively, according to the instructor?
A. Database replication
B. Click and Drag or Drag and Drop
C. Report generation
D. Primary key enforcement

Q6. How does the instructor suggest moving items between two list boxes?
A. Using VBA only
B. Using SQL queries and a macro command
C. Using the import/export wizard
D. By copying and pasting between tables manually

Q7. What example does the class use to demonstrate moving items between two list boxes?
A. Product categories
B. Active employees and inactive employees
C. Shipping zones
D. Invoice line items

Q8. How can items be moved between the two list boxes in the lesson's example?
A. By sending an email
B. Only by copying and pasting
C. By using middle buttons or by double clicking an item
D. Using drag and drop with the mouse

Q9. Which of the following topics is discussed in Lesson 4?
A. Creating customized reports from scratch
B. AutoExec macros, error handling in macros, and macro design tab in the ribbon
C. Integrating Access with Excel
D. Encrypting the database

Q10. For which versions of Microsoft Access should these lessons work according to Richard Rost?
A. Only Access 2016
B. Access 2007 through 2016
C. Access 2007 and earlier
D. Only Access 2019

Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B

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 video from Access Learning Zone is part of my Microsoft Access Advanced Series, Level 5. I am Richard Rost, your instructor for this course. In this Advanced Series, we are focusing on macros, which are powerful tools in Access that allow you to automate routine tasks and enhance the functionality of your database.

This particular class builds on concepts covered in Access Advanced Level 4. Before jumping in, I highly recommend that you complete Levels 1 through 4 of the Advanced Series, as well as the nine Beginner levels and Expert levels 1 through 32. The Beginner Series covers the basic building blocks of Access, while the Expert Series takes you much deeper into the program. You will get the most out of this course if you already have that background knowledge.

This Advanced Series centers around topics like macros and events. Up next, we will move into the Developer Series, where the focus will shift toward Visual Basic programming.

The content in this video was recorded using Microsoft Access 2016. However, the techniques and features I cover should work in Access versions 2007 through 2013 as well, since most of these commands have not changed since 2007. If you notice any differences in your version, I welcome your feedback. If you are still using Access 2003 or older, it really is time to upgrade, but I also offer training for Access 2003 on my website if you are still working with older systems.

You can find the sample database used in this course at 599cd.com/databases. While I always encourage you to create your own database step by step, starting with Beginner Level 1, you do have the option to download my version if you want to compare your work or if you joined these lessons at a later stage. You will need your course password to access the database files from that page.

In today's lesson, here's what we are covering:

First, we are going to add separate billing and shipping address fields to both customers and orders. This lets us track exactly where shipments were sent when each order was placed, which is especially important if an order goes to a different address than the customer's default one.

Next, we continue working with the separate shipping address by adding these new fields to the order table, ensuring every order records the shipping address at the time it was created. We will also place these fields on the invoice so you can see the shipping details in your printed documents.

Microsoft Access does not include built-in drag and drop support, but I will show you a simple method to move items between two list boxes. For this example, we will use a list of active and inactive employees. With a combination of some small SQL changes and a single macro, you will be able to click or double click to move items from one list to another. This technique can be easily adapted for other scenarios where you need to shift data between two lists.

Finally, we will cover a few miscellaneous but important topics related to macros. These include creating an AutoExec macro that runs automatically when your database starts, handling errors in your macros, and exploring the options available to you on the macro design tab in the ribbon.

You can 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

Adding separate billing and shipping addresses to customers
Tracking shipping addresses per order
Adding shipping address fields to order table
Placing shipping address fields on invoices
Moving items between two list boxes with macros
Setting active and inactive employees using list boxes
Using buttons and double-click to move listbox items
Basic SQL code for listbox item movement
Using the AutoExec macro
Error handling in macros
Overview of the macro design tab in the ribbon

Article

Welcome to this advanced Microsoft Access tutorial. In this lesson, we will focus on using macros to automate tasks and add valuable functionality to your database. Before diving into these advanced techniques, make sure you are familiar with the basics of Access, including working with tables, queries, forms, and reports. Knowledge from beginner and expert levels is beneficial here as we will be building upon those foundations.

One useful feature for any database that handles customer orders is the ability to save separate billing and shipping addresses. This allows you to track exactly where an order was shipped, even if a customer's main address changes later on. To get started, you will need to modify your Customers table by adding fields for both billing and shipping addresses. Common fields include BillingStreet, BillingCity, BillingState, BillingZip, ShippingStreet, ShippingCity, ShippingState, and ShippingZip. Incorporate these fields into your Orders table as well so that when an order is placed, you can save the shipping address as it was at that time. This historical tracking is important for accurate records and reporting.

After updating your tables, make sure to add the corresponding controls to your data entry forms. For example, on your Customer form, add text boxes for the billing and shipping fields, binding each to the correct field in your table. On your Order form or invoice, include the shipping address fields so users can see or print the delivery address associated with that specific order. Always remember to test your forms to verify data is being saved and displayed correctly.

Access does not have built-in drag and drop support, but you can create an interface that feels similar for moving items between two list boxes. This feature can be helpful, for instance, if you want to manage active versus inactive employees. Picture two list boxes on a form, one showing active employees and another showing inactive employees. To move an employee from one category to another, you can select an item and click a button, or even double-click the name to move it. Behind the scenes, set up a Yes/No field such as IsActive in your Employees table. The Row Source of your list boxes should refer to queries that filter employees based on their active status. Use a macro or a bit of VBA code to change the IsActive field and then requery the list boxes so the change is reflected visually.

Here is an example of how you might handle moving an employee:

Suppose you have a button labeled Mark Inactive. Its OnClick event could use a macro with the SetValue action. The macro would set [Forms]![YourForm]![IsActive] to False for the selected record. Then it would requery both list boxes. If you prefer VBA, your code could look like this:

Me!IsActive = False
Me.ActiveEmployeesList.Requery
Me.InactiveEmployeesList.Requery

Similarly, you would use True to reactivate an employee.

Now, let us look at several other useful macro techniques. One is the AutoExec macro. This is a special macro in Access that runs automatically whenever your database opens. To create it, simply create a new macro and save it with the name AutoExec. Use this macro to perform startup tasks such as opening your main menu form, setting application properties, or checking for missing data.

Error handling is also important when working with macros. Access macros provide an OnError action. By adding this as your first macro action, you can tell Access what to do if any errors occur during macro execution. For example, you can display a custom message or log the error to a table for review. On the macro design tab in the ribbon, you will find other useful macro actions, including those to run code, set warnings, or manage objects in your database.

In summary, using macros in Access allows you to automate repetitive tasks, manage data more efficiently, and enhance the overall functionality of your database. Whether you are tracking shipping details, managing user lists, or customizing startup behavior, these tools will help you build a more robust application. Remember to experiment with these features in a sample database before using them in your production files so you are comfortable with their operation and potential outcomes.