Introduction

Welcome! Form Controls, Locking, Macros

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

Welcome to Microsoft Access Advanced Level 3. In this course we will focus on using macros to add more functionality to your Access database, particularly for controlling and modifying forms and their objects. We will talk about changing control properties, like foreground and background colors, making fields grow or shrink on focus, and setting visibility, height, and width. We will also discuss how to lock certain fields and require a password to mark orders unpaid. This course builds on the material covered in the previous beginner, expert, and advanced series lessons.

Navigation

Prev: A03
Next: Lesson 01

Keywords

Access Advanced, macros, forms, control properties, change foreground color, change background color, form events, lock form controls, require password, mark order unpaid, visible property, height property, width property, got focus, lost focus, enlarge f

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

This is the third lesson in the Advanced Series that focuses mostly on macros. Macros allow us to add functionality to our database that you normally would not have otherwise. You can use macros or you can use Visual Basic programming.

Today we are going to learn how to control more of the forms and objects on our forms with our macro code. We will learn how to lock certain things on our forms. Require a password, for example, if someone is going to mark an order unpaid, things like that.

I strongly recommend that you take Access Advanced Level 2 before this class, and Level 1, of course, if you have never used macros before. And also my entire beginner and expert level series that all builds up to this. It is the database that we have in today's class. We have been building it since Beginner Level 1.

So if you do not know anything about forms and reports and all that good stuff, then I recommend the beginner and expert series before. There are 9 beginner lessons, and 32, I think, expert lessons. This is the third advanced class. There will be developer lessons after this that will cover Visual Basic programming and other things developers have to know, like how to secure and distribute your database.

This class was recorded using Microsoft Access 2016. Everything covered today pretty much should work with 2007 and 2013. If you find something that does not, please let me know.

The sample database for this class can be found at accesslearningzone.com/databases.

Now let's take a preview of what we are going to cover in today's class.

In lesson 1, we are going to start learning how to change control properties using our macros. For example, learn how to change the foreground and background color of different controls on a form when certain events happen.

In lesson 2, we are continuing with more control properties when we learn about the visible, height, width properties, on got focus, on lost focus, changing the background color of a form. One of my favorite things is making a particular field get larger when you click on it. The description field, for example, when we click on it, I want to blow it up so I have lots of room to view or type in that field. When I leave it, when I tab off or click off it, I want it to get smaller again. So we are going to cover that in this lesson.

In lesson 3, we are going to learn how to lock paid orders. If someone clicks on the paid button, it will mark the order paid, but if they unclick it, they will have to enter a password. Only then, if the password is correct, will the order be marked unpaid.

Thank you.

Quiz

Q1. What is the primary topic of this Microsoft Access Advanced Level 3 lesson?
A. Learning how to design tables
B. Working with queries and reports
C. Using macros to control form functionality
D. Importing data from Excel

Q2. What are macros primarily used for in Microsoft Access as discussed in this lesson?
A. Backing up your database
B. Adding functionality to the database that is not otherwise available
C. Encrypting the database
D. Generating SQL code automatically

Q3. Why should students take Access Advanced Level 2 before this class?
A. Level 2 covers database security in detail
B. Level 2 introduces macros, which are essential for Level 3
C. Level 2 covers pivot tables, which are required in Level 3
D. Level 2 is focused on hardware requirements for Access

Q4. What additional knowledge is recommended before taking this class?
A. C# programming
B. Beginner and expert series on forms and reports
C. Advanced networking in Windows
D. Database theory and ER diagrams only

Q5. Which version of Microsoft Access was used to record this lesson?
A. Access 2003
B. Access 2013
C. Access 2016
D. Access 2019

Q6. What is one example of a feature you will learn to implement with macros in this class?
A. Creating mail merge documents
B. Changing the foreground and background color of controls when certain events happen
C. Automatically exporting queries to PDF
D. Designing SQL server integration packages

Q7. What technique will you learn for the description field on a form?
A. Adding barcode scanning functionality
B. Making the field larger when clicked and smaller when focus is lost
C. Encrypting the field contents
D. Automatically sending email on field change

Q8. What security measure is taught for marking orders as unpaid?
A. Sending a notification to the manager
B. Allowing anyone to edit orders
C. Requiring a password to mark an order unpaid
D. Only allowing changes during certain hours

Q9. If a feature does not work in your version of Access, what does the instructor recommend?
A. Upgrading immediately
B. Contacting him to let him know
C. Ignoring the problem
D. Reinstalling Access

Answers: 1-C; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B; 8-C; 9-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 the third lesson in the Advanced Microsoft Access Series, and it focuses primarily on macros. I'm Richard Rost, and in this course, we will explore how macros can provide additional functionality to your Access databases that you wouldn't normally have. Macros are a great way to enhance what your database can do, and they're often used as an alternative to Visual Basic programming.

Today, our goal is to learn how to use macro code to control forms and the objects on those forms in more advanced ways. For example, I'll show you how to lock specific controls and even require a password for certain actions, like marking an order as unpaid.

If you're new to macros, I highly recommend you complete Access Advanced Level 1 and Level 2 before starting this lesson. It's also best if you've already worked through my entire beginner and expert series. Each of these courses builds on the previous lessons, and all use the same sample database, which we've been developing since Beginner Level 1. If you're not yet comfortable with forms, reports, or other Access basics, start with the beginner and expert classes. There are 9 beginner lessons and about 32 expert lessons. This is the third class at the advanced level. We'll also have developer courses soon, which will address topics like Visual Basic programming, security, and distributing your database.

This specific class was recorded with Microsoft Access 2016, but almost all the material covered here should work just fine with Access 2007 and 2013. If you run into any issues with an earlier version, please let me know.

You can download the sample database for today's class at accesslearningzone.com/databases.

Let me give you an overview of what we'll be covering today.

In lesson 1, we'll start by learning how to use macros to change control properties. You'll see how to alter the foreground and background color of different controls on a form in response to certain events.

Lesson 2 continues our look at control properties. Here, I will cover visibility, as well as adjusting the height and width of controls. We'll discuss using events like On Got Focus and On Lost Focus, and you'll see how to change the form's background color. One feature I like is making a field expand when you click on it: for example, we can have the description field grow larger for ease of editing and then shrink back down when you leave it.

In lesson 3, we will set up a way to lock paid orders. If a user tries to unmark an order as paid, the system will prompt for a password. Only when the correct password is entered will the order be marked as unpaid.

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

Changing control properties with macros
Setting foreground and background color of controls
Controlling visibility of form controls
Adjusting control height and width with macros
Handling On Got Focus and On Lost Focus events
Expanding a field when clicked for easier editing
Shrinking a field after losing focus
Locking paid orders using macros
Requiring a password to unmark orders as unpaid

Article

Welcome to this guide on advanced macros in Microsoft Access. This tutorial will help you take your Access databases to the next level by controlling various aspects of your forms and adding extra functionality using macros. If you already have a foundation in Access and have worked with forms, reports, and basic macros, you are ready to get started.

Macros in Access are a powerful way to automate actions and respond to user interactions without needing to write Visual Basic code. They allow you to modify the properties of form controls, implement user security such as password protection, and generally make your database more user friendly and robust.

The first thing you will learn is how to change the properties of form controls using macros. For example, you might want to highlight a text box by changing its background color when it receives focus, or return it to the normal color when focus is lost. You could also change the foreground color or adjust the height and width of a control based on certain events.

Suppose you have a Description field on a form that is sometimes too small for comfortable editing. You can use macros to make this field grow when it is clicked or receives focus, allowing the user to see and work with more text. When the user clicks or tabs away, the field can shrink back to its original size. This is accomplished by setting the On Got Focus event of the field to run a macro that increases the Height and possibly the Width properties, and the On Lost Focus event to run another macro that restores the original dimensions.

Another useful macro technique is working with the Visible property. You can hide or show controls dynamically based on user actions. For example, certain buttons can appear only when specific conditions are met, improving the usability and visual clarity of your forms.

A practical scenario for using macros is enforcing security, such as locking records. For instance, consider an order entry database where orders have a Paid checkbox. Once an order is marked as paid, you probably want to prevent unauthorized changes. You could set up a macro so that when the Paid box is checked, the rest of the order fields become locked. If someone tries to uncheck the Paid box to mark it unpaid, the macro can prompt for a password. Only if the correct password is entered will the record be unlocked.

To create this functionality, start by adding a macro to the After Update event of the Paid checkbox. The macro should check the box's value. If it is checked (meaning the order is paid), use the SetProperty macro action to set the Locked property for relevant controls to Yes. If it is unchecked, you can use the InputBox macro action to prompt the user for a password. If the user enters the correct password, unlock the controls by setting the Locked property back to No. If not, leave the controls locked and possibly show a message box indicating the password was incorrect.

You can also implement similar password protection using Visual Basic for Applications if you want additional customization, but macros offer a no-code way to add this functionality.

Here is an example of simple VBA code for password protection if you want even more control:

Private Sub chkPaid_BeforeUpdate(Cancel As Integer)
If Me.chkPaid = False Then
Dim strInput As String
strInput = InputBox("Please enter the password to mark this order unpaid:")
If strInput <> "YourPasswordHere" Then
MsgBox "Incorrect password. The order will remain paid.", vbExclamation
Me.chkPaid = True
Cancel = True
End If
End If
End Sub

Replace "YourPasswordHere" with your actual password. This code goes in the form's code window. If you stick with macros, use the InputBox and SetProperty actions as described to achieve similar behavior without needing VBA.

By learning to work with control properties such as ForeColor, BackColor, Height, Width, and Visible, you can make your forms much more interactive and visually appealing. Combining these techniques with event macros tied to Got Focus, Lost Focus, and After Update events lets you create a dynamic, user-friendly interface that enforces rules and streamlines data entry.

These advanced macro concepts will help you get more out of Microsoft Access by automating tasks, improving form usability, and adding important business rules to your applications. Practice these techniques in your own databases to see the difference macros can make. With a solid understanding of macros, you will be well prepared to move on to more advanced topics like Visual Basic programming, database security, and distribution strategies.