Introduction

Welcome! Password Forms & Print Labels

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

Welcome to Microsoft Access Advanced Level 4. In this course we will focus on macros and events to automate tasks and add functionality to your database, building on skills from previous beginner, expert, and advanced levels. We will discuss requiring a password to open forms, creating a full sheet of customer labels using different methods, working with table data directly and form-based label printing, and introducing build events for customizing printed reports. We will also talk about macro groups and submacros, noting some changes in Access 2016, and I will offer suggestions on software versions and working with the sample database.

Navigation

Prev: A04
Next: Lesson 01

Keywords

Access Advanced, macros, events, password form, user authentication, automate tasks, main menu buttons, customer labels, label printing, macro loop, submacros, macro groups, printable invoice, printable report, query, build events, report sections

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

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

This is the Advanced Series of Classes, which follows my beginner and expert level classes. The series focuses on macros and events, which allow you to automate tasks and add functionality to your database. This class follows Access Advanced Level 3.

I strongly recommend you take all the beginner and advanced one through three classes before starting this one. There are 9 beginner levels, 32 experts as of right now, 3 advanced levels, this is level 4, and soon we'll start the developer classes, which will focus on VBA programming.

This class was recorded using Access 2016. Pretty much everything covered should work. Some things have changed. The macro groups and submacros, which will be covered in level 5, have changed. I believe 2016 was the first year they changed, for example, macro groups are now called submacros. But most everything else should still work. However, if you're using one of these older versions, I do strongly recommend you upgrade to Access 2016.

I personally suggest the Office subscription, which is relatively inexpensive, and you constantly have the most current updated version of Access. It is automatically installed, you get updates, and it is much better than purchasing older versions of the software. If you have not upgraded yet, now it is time to upgrade.

Of course, I recommend you build the database that I build in class, but if you want to download the sample database, you can find it here at AccessLinux.com. Or there is a shorter URL for you. I still keep the 599 CD name around. It is how the company started. That was our pricing gimmick. It was 599 for a beginner lesson. The more advanced lessons cost more. But the URL is nice and short. It is a 5 letter domain name, so I have kept it.

Let's talk about what is covered in today's class. In lesson 1, we are going to learn something that people ask me all the time: how to force the user to type in a password to open a form. We have buttons on our main menu, and we do not want the user to be able to use those buttons unless they know a password. That is what we will cover in this lesson.

In lessons 2 and 3, we are going to learn how to make a full sheet of customer labels. In other words, you click on one button and you get a full sheet of labels for just the current customer. I am going to show you two different methods. One does not require macros, and it is relatively easy to set up. Then, in the next lesson, lesson 3, we'll do the same thing again, but with a macro loop.

In lesson 3, I am going to show you two more techniques for creating a full sheet of customer labels. First, I am going to show you how to open the table directly and to send the keys - the keystrokes of the label text - right into the table. It is not the best method, but it is a cool technique and you might use it from time to time. The second method I am going to show you is how to create a form, a label form like you see on the screen right there. Then we will open it up and run a macro loop to say, how many labels of this customer would you like? I want 7 of them. OK, boom, boom, boom, boom, there go 7 of them. You can go in there and modify them and manipulate them and play with them and stuff.

In lesson 4, we are going to see a new method for setting the quote versus invoice text on our printable invoice, our printable report. In an earlier class, we showed you how to do this by simply using a query. There is nothing wrong with that method. However, in this lesson, I want to start introducing you to build events in the detail and different sections of printed reports. This will be kind of like an intro to that.

In lesson 5, we are going to learn about macro groups and submacros.

Quiz

Q1. What is the main focus of Microsoft Access Advanced Level 4?
A. Macros and events to automate tasks and add functionality
B. Query design and basic table structure
C. Learning VBA programming only
D. Importing data from Excel

Q2. Which version of Access was this class recorded in?
A. Access 2019
B. Access 2016
C. Access 2013
D. Access 2007

Q3. What is recommended regarding Access versions for this course?
A. Use Access 2003 for compatibility
B. Using older versions is better than upgrading
C. Upgrade to Access 2016 or use Office subscription
D. Stick with Access 2013 permanently

Q4. What does the instructor suggest about the Office subscription for Access?
A. It is expensive and rarely updated
B. It provides infrequent updates
C. It is inexpensive and keeps Access current automatically
D. It only works with Mac computers

Q5. What is the primary topic covered in Lesson 1?
A. Creating calculated queries
B. Forcing the user to type a password to open a form
C. Designing macros for data import
D. Setting up table relationships

Q6. What is taught regarding customer labels in lessons 2 and 3?
A. How to make customer mailing lists in Excel
B. How to make a full sheet of labels for just the current customer
C. Generating labels using only SQL code
D. Importing label templates from Word

Q7. Which method is NOT used in the lessons for generating labels?
A. A method not requiring macros
B. Macro loop to create multiple labels
C. Using mail merge within Access
D. Sending keystrokes directly to a table

Q8. What is introduced in lesson 4 about printed reports?
A. Writing VBA code for all report sections
B. Setting text using a query only
C. Using build events in detail and other sections of reports
D. Printing only tables, not reports

Q9. What will lesson 5 cover?
A. The use of SQL within forms
B. Macro groups and submacros
C. VBA functions and modules
D. Database backup and restoration

Q10. Where can students download the sample database?
A. Microsoft.com/Access
B. Office.com/download
C. AccessLinux.com
D. SampleDatabase.com

Answers: 1-A; 2-B; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 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 part of my Advanced Microsoft Access series, specifically Level 4, and focuses on macros and events to help you automate tasks and enhance your database's functionality. This course builds on the beginner and expert series, and it is important to have completed all 9 beginner levels and the first three advanced classes before starting with this one. As of now, there are 32 expert classes available, and as we move forward, we will also be starting the developer classes that cover VBA programming.

The content for this course was recorded using Access 2016. The vast majority of the techniques should work in later versions. However, some features have changed over the years. For example, macro groups are now called submacros, and this will be discussed in Level 5. Since Access 2016 introduced some of these changes, I highly recommend upgrading if you are using an older version. Personally, I suggest the Office subscription because it is affordable and ensures you always have the latest version of Access with updates automatically installed. That is a better option than continuing with outdated software, so if you have not upgraded yet, this is a good time.

I always encourage students to build the database along with me as you go through the lessons. If you prefer, you can also download my sample database from AccessLearningZone.com. You might come across a shorter URL, 599CD.com, which I have retained from the early days of the company because of its convenience and history.

Here's what you can expect from today's class:

In lesson 1, we tackle a common question about how to require users to enter a password before they can open a form. If your database has buttons on the main menu that you want to protect, I will show you how to ensure only those with the correct password can use those functions.

Lessons 2 and 3 will be all about creating a full sheet of labels for a single customer. You will learn two methods for doing this. The first does not involve macros and is relatively simple to set up. Then, we will look at a more advanced way using a macro loop to automate the generation of the labels.

Lesson 3 continues with two additional techniques for producing a full sheet of customer labels. One method involves sending keystrokes directly into the table, which is not the usual way to do things but can be useful in special situations. The other method creates a form specifically for labels, where you can choose the number of labels to print for a customer. This approach gives you more flexibility to modify or manipulate the labels before printing.

In lesson 4, you will learn a new method for setting the quote or invoice text on a printed invoice report. Earlier, we addressed this by using a query, but this time we will explore how to use build events for various sections of a printed report. This lesson serves as an introduction to more event-driven techniques in reports.

Finally, lesson 5 will introduce you to macro groups and submacros, which will be key as you develop more sophisticated and organized automation in your databases.

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

Password protect a form with a prompt
Restrict button access to users with passwords
Create a full sheet of labels for one customer
Generate labels without using macros
Generate labels using a macro loop
Input desired label quantity for a customer
Send label keystrokes directly into a table
Create a label form for custom label entry
Use macro loops to produce multiple labels
Set quote vs invoice text in reports with build events
Use events in report sections for dynamic text
Introduction to macro groups and submacros

Article

Welcome to this advanced Microsoft Access tutorial where we will delve into automating tasks and adding more advanced functionality to your databases. This guide assumes you already have a solid grasp of Access fundamentals, as well as experience with basic and intermediate features. Our focus will be on macros and events that empower you to streamline repetitive tasks and customize the user experience.

One of the most common requests is adding a password prompt before allowing users to open a particular form. You might have a main menu in your database with buttons that open sensitive forms, and you want to restrict access. To achieve this, you can set up a macro or some simple event code that triggers when the button is clicked. A straightforward way is to use an InputBox to prompt the user for a password and check whether it matches the expected value before opening the form.

For example, using VBA, you might attach code like the following to the button's OnClick event:

Private Sub btnOpenProtectedForm_Click()
Dim strInput As String
strInput = InputBox("Please enter the password to continue:", "Password Required")
If strInput = "YourPasswordHere" Then
DoCmd.OpenForm "ProtectedFormName"
Else
MsgBox "Incorrect password. Access denied.", vbExclamation
End If
End Sub

Replace 'YourPasswordHere' with your actual password and 'ProtectedFormName' with the name of the form you want to protect. This approach enables basic security and can be extended with additional logic.

Another useful scenario is printing a full sheet of labels for a single customer. Sometimes you need to print a whole label sheet for just one customer, not your entire customer list. There are a couple of ways to do this in Access. First, you could create a query that selects only the current customer, then base your label report on that query. You can set up a button that applies a filter so the report opens with just the records you need. This is a simple method and does not require macros.

A more advanced method involves using a macro loop to generate multiple labels for one customer. You might want to prompt the user for how many labels are needed and then generate that number. For example, you can use a loop in VBA to add the required number of records to a temporary table, then print the labels from that table. Here is a basic outline:

Private Sub btnPrintCustomerLabels_Click()
Dim i As Integer
Dim LabelCount As Integer
Dim CustomerID As Long
CustomerID = Me.CustomerID ' Assuming a CustomerID field on your form
LabelCount = InputBox("How many labels for this customer?", "Number of Labels", 1)
For i = 1 To LabelCount
CurrentDb.Execute "INSERT INTO LabelTemp (CustomerID) VALUES (" & CustomerID & ");"
Next i
DoCmd.OpenReport "CustomerLabels", acViewPreview
CurrentDb.Execute "DELETE FROM LabelTemp;"
End Sub

In this code, replace 'LabelTemp' with the name of your temporary label table, and 'CustomerLabels' with your label report name. This method gives you flexibility, letting the user decide how many labels to print. After printing, the temporary records are automatically cleared out.

Another fun trick involves sending keystrokes directly into an open table using VBA. While not the preferred method for production databases, it can demonstrate how macros and automation can interact with data entry in non-traditional ways.

You can also create a label form where the user decides how many labels they need. When they enter the number, a macro loop creates each label as a new record, which can then be edited or customized before printing.

Moving on to reports, let us look at changing text on a report based on whether it is a quote or an invoice. In previous database classes, you might have used queries to change fields or labels in your report. This works, but Access offers more advanced features through events. For example, you can use the OnFormat event in the report's detail section. By adding an event procedure, you can dynamically change the content or formatting of the section as the report is printed. Here is how you might use this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Quote = True Then
Me.lblType.Caption = "QUOTE"
Else
Me.lblType.Caption = "INVOICE"
End If
End Sub

This code checks a field (such as 'Quote') for each record and changes the label caption accordingly. It gives you more flexibility than relying solely on queries.

Finally, a note on macros and submacros. In Access 2016 and later, macro groups were renamed to submacros. This allows you to organize related macro actions together under one macro name, making it easier to maintain and reuse blocks of automation within your database. If you are using an older version of Access, upgrading to a newer one like Access 2016 is highly recommended, as it provides better macro features, more robust automation capabilities, and improved stability.

By following these techniques, you can take your Access databases to the next level, making them more secure, efficient, and user friendly. Practice each of these methods and experiment with adapting them to your own database projects. Automation through macros and events opens up a wide range of possibilities, from enhancing reports to simplifying repetitive tasks.