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 312
Get notifications when this page is updated

Intro

In lesson 312, we will work on adding user-friendly enhancements and customizations to our Microsoft Access database, including creating a secondary menu, using events like On Got Focus and On Lost Focus, handling dirty records, and implementing the Trim function. I will show you how to make a form resizable with dynamic height and width settings, store company settings such as a default tax rate in a centralized table, and build a service invoice alongside product invoices. We will also cover report enhancements, tab controls, customizing combo boxes to display company or contact names, and various order form updates.

Transcript

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

Today's lesson is all about user-friendly enhancements to our database. We have gotten a lot of suggestions over the past couple of weeks and months while we are building our database. These are things from customers that should be added to the database, but I have kind of skipped over them because, for one reason or another, I did not want to either flood you with too much information, or it just was not important at the time.

There are some things we are going to do that mostly have been suggestions from students and users like you. But I am not just going to cover things you have seen before. I am going to do some enhancements, but I am going to throw in some new material as well.

For example, we are going to create a secondary menu. Our main menu is getting a little cluttered, so we are going to make a secondary menu. I am going to show you some new tips and tricks, like the On Got Focus and On Lost Focus events. We will show you about dirty records and what those are. We will go over the records, cycle the tab cycles, and we will link some new functions like the Trim function and things like that.

We are going to have some new stuff sprinkled in throughout all these enhancements, so pay attention. I am going to show you how to make the customer form resizable. We have covered resizing forms before, but this will tie it all in with using the inside height and inside width properties. We will give the user a little button that they can click on to dynamically resize the form automatically while they are using it, to show more information or less information.

We are going to have some customizations based on overall database settings that you might not want to hard code into the database. For example, how about your tax rate? You might want to have one default tax rate for the entire database based on your company, the city and state that you happen to be in, maybe the county. And you do not want to hard code that into all your reports and your forms because if it changes, then you have to go to the entire database and change it everywhere.

For example, here in the area county a little while ago, we changed from 8 and a quarter percent to 8 and 3 quarters percent sales tax. So, if that information was hard coded in your database and all your queries and forms and reports, then you would have to go through and change all those. By putting that in the customer settings table or the company settings table, you only have to change that once.

We will create a service invoice. Any invoice that we have built in the previous classes was really designed for selling product information. What if you are a service business? We will make a different invoice with some tweaks and some tricks in that invoice that are different from the other one, so you can print one or the other.

I will show you some cool stuff, some new build events. I will show you how to dynamically create lines, how to draw lines on your report, so you can put a line wherever you want to, whether it is a straight horizontal vertical line or a diagonal line. We will show some more problems with Can Grow and Can Shrink properties.

We will do some customizations to the order form. We will put a tab control on there so we can have built-in, shipped-to, and different tabs. I will also show you how you can take your customer combo, the pick list that we have, and have it show either the company name or the customer's first and last name if a company name does not exist.

That was a big requested feature because we have been building our database based on companies as customers. What happens if there is no company name? I want to be able to see the first and last name in the drop-down box. That is a big one. We will cover that in lesson six.

There are lots and lots of little things we are going to cover, and we will get to them all shortly.

Prerequisite: This class follows Microsoft Access 3.11. I strongly recommend that you take everything from 3.01 at least up to 3.10 before watching this class. If you skipped the 100 and 200 level classes because you know Access already, that is fine, but definitely do not miss 3.01 through 3.10, or excuse me, through 3.11 before taking this class, which is 3.12.

I am still using Access XP and Windows XP. Actually, I am using Windows 2000, but the Windows version does not make a difference. The Access version should be the same. This should be the same from Access 97 on through Access 2003. I have not tested this with 2007 yet. So if anyone has any problems using Access 2007 with these videos, please let me know about it. We will be making the 2007 versions pretty soon.

If you are watching this class, chances are you have taken a class from me before, but I recommend you sit back and watch the lessons one time through, then watch them a second time through following along with the examples. Do not try to apply what you learn immediately.

All the database files are located here: AccessLearningZone.com/students. Of course, you can discuss this class online at AccessLearningZone.com. The shortcut hyperlink to this class is accesslearningzone.com/?GOAC312.

If you are using the Amocrine Video Player, you will find that this page automatically loads up in the player if you are online while you are watching the class.

Do the examples. Do not just steal my database off the website and use that. Go through and build these examples. Follow the videos. You will get more out of the class if you actually build this thing.

Quiz

Q1. Why is the creation of a secondary menu suggested in the video?
A. To organize the main menu and reduce clutter
B. To limit user access to forms
C. To increase the number of records stored
D. To change the database theme

Q2. What are On Got Focus and On Lost Focus events used for in Microsoft Access?
A. Trigger actions when forms open or close
B. Execute code when a control gains or loses focus
C. Validate user input when submitting forms
D. Change the color of the database background

Q3. What is a "dirty record" in Access as mentioned in the video?
A. A record that contains invalid data
B. A record that has been modified but not saved
C. A record that has not been accessed in a while
D. A record that does not contain any data

Q4. Why is it beneficial to store settings like tax rates in a settings table rather than hard coding them into queries and reports?
A. It allows you to update the value in one place
B. It lets you delete records more easily
C. It automatically sends data to customers
D. It reduces the file size of the database

Q5. What enhancement will allow the Customer form to show more or less information dynamically?
A. Adding more fields to the form
B. Making the form resizable using Inside Height and Inside Width properties
C. Printing the form
D. Exporting the form to Excel

Q6. What problem is solved by customizing the customer combo (pick list) to show the first and last name if the company name does not exist?
A. Enabling sorting by order date
B. Improving readability when a company name is missing
C. Limiting the number of records displayed
D. Allowing multi-select options

Q7. Why is it NOT recommended to hard code database-wide settings, like tax rates, in multiple reports and forms?
A. Access does not allow hard coding
B. Changes would have to be made everywhere individually
C. It increases processing speed
D. It enhances data encryption

Q8. What does the instructor recommend doing before following along with the examples in the video?
A. Purchase additional software
B. Watch the lessons all the way through once
C. Install Access 2007
D. Print out the transcript

Q9. Which version(s) of Microsoft Access does the video state should be compatible with the course material?
A. Only Access 2007
B. Access 97 through Access 2003
C. Only Access 2013
D. Only Access for Mac

Q10. What is the instructor's advice regarding the database files provided for the course?
A. Only view the files and do not use them
B. Steal the databases directly for your own use
C. Build the examples yourself following the videos for maximum learning
D. Share the files online

Answers: 1-A; 2-B; 3-B; 4-A; 5-B; 6-B; 7-B; 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 is all about making our Microsoft Access database more user-friendly. Throughout the process of building this database, I have received numerous suggestions from students and users on improvements and features that would make things easier day to day. Some of these items I deliberately held off on, either to avoid overwhelming you with details or simply because they were not needed at that stage.

In this lesson, I am going to address many of these suggestions. I will not just repeat things we have covered before - I will also be introducing some brand new concepts and improvements.

A good example of an enhancement we will tackle is the creation of a secondary menu. Our main menu has become somewhat cluttered, so it makes sense to split off some options and organize things better. Along the way, I will introduce you to useful form events like On Got Focus and On Lost Focus. We will take a close look at what dirty records are and how Access keeps track of changes that need to be saved. I will also show you how to control record navigation, manage tab order cycling, and use functions like Trim to clean up data as you go.

We've discussed resizing forms before, but for this lesson, I will show you how to let users resize a form dynamically while they are using it. This involves working with the inside height and width properties, and I will give you a handy button that lets the form expand or contract, which can make working with data much more comfortable.

Another important customization involves moving away from hard-coded settings. For example, if you want a default tax rate for your company, hard-coding that value into each form, query, and report means you would have to update every single object if the rate ever changed. By instead keeping settings like tax rate in a dedicated table, you only have to update that value once.

I will illustrate this with a local example: in my county, the sales tax recently changed from 8.25 percent to 8.75 percent. Had that rate been hard-coded, every object using it would need to be updated. With a company settings table, you just change it in one spot.

We will also create a service invoice. The invoices we have made in previous lessons were designed for selling products, but many of you also need to bill for services. We will build a separate invoice form and add new techniques and formatting unique to service businesses. You will then have the option to print either a product or service invoice as needed.

There will be new tricks involving form and report design, too. I will show you how to dynamically draw lines on your reports, whether you need straight, vertical, or diagonal lines. We will also look at some issues that can occur with the Can Grow and Can Shrink report properties, and how you can resolve them.

The order form will get some enhancements as well. I am going to demonstrate how to use a tab control, so your form can display different tabs for billing, shipping, and other views. I will also respond to a frequent request: modifying the customer combo box so that, if a customer does not have a company name, you can still see their first and last name in the drop-down list. Currently, the system expects a company name, but many users want to be able to view personal customer names instead. We will address that in detail in lesson six.

There are a lot of individual enhancements and small tweaks we will be making throughout the class, all designed to improve the database and make your life a little easier.

If you are following along, this class is a direct continuation from Microsoft Access 3.11. I highly recommend that you have completed at least lessons 3.01 through 3.11 before beginning this course. If you are already familiar with the basics of Access and skipped the earlier 100 and 200 level classes, that is fine, but lessons 3.01 through 3.11 contain essential material for what we are doing here in 3.12.

For reference, I am working with Access XP (sometimes called Access 2002) and Windows XP, although I am actually running Windows 2000 at the moment. The version of Windows will not impact anything we are covering; the important point is which version of Access you are using. Everything taught here should apply equally from Access 97 up through Access 2003. I have not yet tested anything in Access 2007, so if you encounter issues using that version, please let me know. I plan to release specific 2007 content soon.

My best advice for taking this course: watch the lessons all the way through once to get a sense of the material, and only then go back and work through the exercises step by step as you follow along. Do not feel you have to apply each new concept immediately.

All the database files you will need are available on my website, AccessLearningZone.com, in the student section. You can also use the web forums there to discuss any of these classes. The direct link to this particular class is accesslearningzone.com ?GOAC312.

If you are using the Amocrine Video Player, this page will load up automatically within the player as you watch, provided you have an internet connection.

One last recommendation: do not simply download my finished database files and skip the work. The experience is much more meaningful if you actually build the examples yourself as you watch and follow along.

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

Creating a secondary menu in Access
Using On Got Focus and On Lost Focus events
Understanding and handling dirty records
Customizing tab cycle behavior in forms
Using the Trim function in Access forms
Making the customer form resizable
Using inside height and inside width properties
Adding a dynamic resize button to a form
Managing default tax rate in a settings table
Creating and using a company settings table
Designing a service invoice for service businesses
Adding dynamic lines to reports
Drawing horizontal, vertical, and diagonal lines on reports
Working with Can Grow and Can Shrink properties in reports
Customizing the order form with a tab control
Displaying company name or first and last name in combo box if company missing