Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 310
Get notifications when this page is updated

Intro

In lesson 310, you will learn how to use conditional formatting in Microsoft Access forms, resize forms using VBA, and set up a company settings table to manage key company info like name, address, and logo from a single place. I will show you how to track payments for orders by building tables and forms to handle partial payments, update invoices, and modify your accounts receivable report. We will cover how to use DSum to total related values and how to read values from subforms, along with other practical database management techniques.

Transcript

Welcome to Microsoft Access 310, brought to you by accesslearningzone.com. I am your instructor, Richard Rost.

Today's class is a great one. We've got a lot of new concepts to cover today.

First, we're going to talk about conditional formatting for your forms, which is changing the format of fields based on the data in them. For example, if you want negative numbers to appear red, I will show you how. Then, we're going to show you how to resize a form using VBA code. One of the commonly asked questions I get is, how do I change the size of my form programmatically? With some VBA source code, we're going to learn how to do that today.

We're going to build a company settings table so that we can take certain things like the company name, address, and phone number, and put them in one spot in the database so we don't have to duplicate that information on different forms and reports. For example, the company address. You can embed it into a table, and then on all the different reports that are in the database that might have your address on the top, you can just read that value, so it changes in the future. Then you don't have to go through and change it in all the reports.

Specifically, we're going to do this with a company logo. We've got that graphic that we have, our PC Re-seal logo that appears on the main menu form. It appears on another form. It appears on a couple different reports like our invoice and more. So if we have to change our logo, we've got to go do it in many different places. We'll see how to do that today.

The major section of today's class is going to deal with tracking payments for our orders. We have a nice order entry system where we can make invoices and quotes. But what do we do when the customer wants to make a partial payment? We have no way of tracking that except by putting a note in the memo field somewhere.

Today, we're going to build a whole section to track payments, which means a new table, a new form, and a whole bunch of new things. We're going to need to be able to put payment information on our order form - the amount due, the amount of payments made. We're going to have to be able to do that on our invoices. We're going to have to fix our accounts receivable report to update that information.

Two new concepts we're going to learn are D-Sum, which is very similar to D-Lookup, where you look up a value in a table. D-Sum is adding up a bunch of values from a table. For example, I'm on an order. I want to see how many payments have been made for this order. I can use D-Sum to sum those all up.

Another major concept is reading a value off of a subform. A lot of people ask me how to do that. How do you get a value off of a subform on a different form? It's a really tricky notation. We'll show you how to do that today.

This class follows Microsoft Access 309. If you haven't taken all of my previous Access classes and if you jumped in midstream, I do strongly recommend you have taken everything from 301 on. That's where you start getting into basic stuff. If you know Access and you've skipped all the 100 and 200 level stuff, that's OK, but definitely start from Access 301.

I'll be using Access XP and Windows XP in this class. Everything in here should work fine with Access 2003 down to about Access 97. I have not tested this stuff with Access 2007 yet, but I can't see it being too much different.

Of course, I strongly recommend you sit back, relax, watch the lesson through, and then watch it a second time following along with the examples. Don't try to apply what you learn immediately. I have a lot of people that instantly take the stuff that they learn and try to put it in their database. Do my sample lessons first. Build the PC Re-sale database. Trust me. You'll get it the second time you do it.

The database files can be found online at accesslearningzone.com/students. That's where all the database files are kept.

You can discuss this class online. Go to accesslearningzone.com?GOAC310. That's the shortcut to get to the discussion for this class.

If you're watching this class in the Amicron video player, which is a standalone program that you can download from my website, you'll automatically see this page loaded up on the right side of the video if you're online while you're watching the class.

As I said, do the examples. You'll benefit the most by doing the examples yourself and not just copying my database.

Quiz

Q1. What is conditional formatting in Microsoft Access forms?
A. Changing the database structure based on queries
B. Restricting user access to certain forms
C. Changing the format of fields based on their data
D. Programming custom macros in VBA

Q2. What is one example of conditional formatting mentioned in the video?
A. Making positive numbers bold
B. Hiding fields based on user permissions
C. Making negative numbers appear in red
D. Automatically resizing fields on data entry

Q3. Which programming language is used to resize forms programmatically in Access?
A. SQL
B. JavaScript
C. VBA
D. Python

Q4. What is the purpose of building a company settings table?
A. To store employee login credentials
B. To centralize company information like name and address for use in forms and reports
C. To track order shipment details
D. To manage customer feedback

Q5. What is a benefit of placing the company logo in a table as described in the lesson?
A. The logo cannot be edited by users
B. The logo updates everywhere it appears when changed in one place
C. The logo appears larger on all forms and reports
D. The logo can only be seen by administrators

Q6. What major functionality is added to the order entry system in this lesson?
A. Automated inventory tracking
B. Barcode scanning capabilities
C. Tracking payments, including partial payments, for orders
D. User authentication system

Q7. What tool can be used to sum payment amounts for a given order as taught in the lesson?
A. D-Lookup
B. D-Max
C. D-Sum
D. D-Average

Q8. What is described as a tricky topic, often requested by students, that is covered in this lesson?
A. Exporting tables to Excel
B. Reading a value off of a subform from another form
C. Filtering records with queries
D. Creating navigation buttons

Q9. What version of Access does the instructor mention he is using for this course?
A. Access 2019
B. Access XP
C. Access 2010
D. Access 365

Q10. What is the recommended approach for getting the most out of the course material?
A. Immediately apply all steps to your primary database
B. Only watch the lesson once and take notes
C. Watch the lesson through, then repeat it while following along with examples
D. Skip sample lessons and use your own data

Answers: 1-C; 2-C; 3-C; 4-B; 5-B; 6-C; 7-C; 8-B; 9-B; 10-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 video from Access Learning Zone focuses on several important concepts for improving your Microsoft Access databases.

We begin by exploring conditional formatting for forms. This allows you to change the appearance of form fields based on their data values. For instance, if you want negative numbers to automatically display in red, I'll show you exactly how to set that up. It's an effective way to make sure important information stands out as users interact with your forms.

Next, I'll show you how to programmatically resize a form using VBA. Many students ask how they can change the dimensions of a form with code, rather than manually adjusting them during design time. I'll walk you through the process of using VBA to control your form's size so your interface can adapt dynamically as needed.

Another key topic in this class is organizing company settings within a dedicated table. If your database currently has company details such as name, address, and phone number duplicated in several places, this will show you how to store that information in a single central location. This means, for example, that if your company address changes, you only need to update it in one spot, and all your forms and reports will automatically display the new information.

We will also cover how to manage and update your company logo. If your logo appears in multiple forms and reports, changing it everywhere can be a hassle. By storing it in one location in your database, you only need to update it once, and it will be reflected throughout your application.

The central section of today's lesson deals with tracking payments on orders. Previously, our database design allowed for creating invoices and quotes, but there was no way to record partial payments. People would just jot down payments in a memo or note field, which is less than ideal. Now, we'll build an entire payment tracking system. This includes creating a new payments table and form, integrating payment information into the order form, and updating the invoices and accounts receivable reports accordingly. You'll be able to record each payment received against an order, keep accurate totals, and update all your documents to reflect outstanding balances and amounts paid.

We'll introduce two new technical skills in this lesson. The first is DSum, which functions much like DLookup but instead of retrieving a single value, it sums up values in a table that meet your criteria. For example, if you want to show the total of all payments made against a particular order, DSum lets you do that quickly and easily.

The second skill involves referencing values from a subform. This is a common question from students: how to get data from a subform on another form. The notation can be complex, but I will demonstrate the correct method for reading these values, making your forms much more interactive and capable.

This class builds on topics taught in Microsoft Access 309. If you haven't already completed my Access classes from 301 onward, I highly recommend starting there. Those classes cover the fundamentals you'll need to get the most out of these tutorials. If you're comfortable with Access and have skipped the 100 and 200-level classes, it's still important to begin with Access 301 to build a strong base.

For this class, I'm using Access XP and Windows XP, but all the lessons will work just as well with Access 2003 going back to Access 97. While I haven't tested everything in Access 2007, I don't anticipate any significant differences.

My advice is to watch the entire lesson once to get an overview, and then go through it again while following along with the examples step-by-step. Avoid immediately trying to apply concepts to your own database until you've practiced with the sample exercises. You'll gain a much deeper understanding by building the sample PC Resale database I provide.

The database files are available for download at accesslearningzone.com/students. If you want to participate in class discussions, visit accesslearningzone.com?GOAC310 to join the conversation.

If you use the Amicron video player to watch these lessons, relevant web pages will automatically load alongside the video for your convenience.

Remember, you'll benefit most by working through the examples yourself rather than just copying the completed database.

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

Conditional formatting for form fields
Using VBA code to resize forms
Building a company settings table
Storing and using a company logo in the database
Centralizing company information for reports
Tracking payments for orders
Creating a payments table
Creating a payments form
Displaying payment information on order forms
Updating invoice forms with payment info
Updating accounts receivable reports
Using DSum to sum payment data
Reading values from a subform on another form