Microsoft Access 2019 Programming by Example with VBA, XML, and ASP

Book Preface

The Access VBA Primer is divided into nine chapters that progressively introduce you to programming Microsoft Access using the 2019 version of the product. These chapters present the fundamental techniques and concepts that you need to master before you can take further steps in Access programming.
Chapter 1Getting Started with Access VBA
Chapter 2Getting to Know Visual Basic Editor (VBE)
Chapter 3Access VBA Fundamentals
Chapter 4Access VBA Built-In and Custom Functions
Chapter 5Adding Decisions to Your Access VBA Programs
Chapter 6Adding Repeating Actions to Your Access VBA Programs Chapter 7Keeping Track of Multiple Values Using Arrays
Chapter 8Keeping Track of Multiple Values Using Object Collections Chapter 9Getting to Know Built-in Tools for Testing and Debugging

Visual Basic for Applications (VBA) is the programming language built into all Microsoft® Office® applications, including Microsoft® Access®. In this chapter you acquire the fundamentals of VBA that you will use over and
over again in building real-life Microsoft Access database applications

UNDERSTANDING VBA MODULES AND PROCEDURE TYPES

Your job as a programmer (at least during the course of this book) will boil down to writing various procedures. A procedure is a group of instructions that allows you to accomplish specific tasks when your program runs. When you place instructions (programming code) in a procedure, you can call this procedure whenever you need to perform that particular task. Although many tasks can be automated in Access by using macro actions, such as opening forms and reports, finding records, and executing queries, you will need VBA skills to perform advanced customizations in your Access databases.
In VBA you can write four types of procedures: subroutine procedures, function procedures, event procedures, and property procedures. Procedures are created and stored in modules. A module resembles a blank document in Microsoft Word. Each procedure in the same module must have a unique name; however, procedures in different modules can have the same name. Let’s learn a bit about each procedure type so that you can quickly recognize them when you see them in books, magazine articles, or online.
1. Subroutine procedures (also called subroutines or subprocedures)
Subroutine procedures perform useful tasks but never return values. They begin with the keyword Sub and end with the keywords End Sub. Keywords are words that carry a special meaning in VBA. Let’s look at the simple subroutine ShowMessage that displays a message to the user:
Sub ShowMessage()
MsgBox “This is a message box in VBA.” End Sub
Notice a pair of empty parentheses after the procedure name. The instruction that the procedure needs to execute is placed on a separate line between the Sub and End Sub keywords. You may place one or more instructions and even complex control structures within a subroutine procedure. Instructions are also called statements. The ShowMessage procedure will always display the same message when executed. MsgBox is a built-in VBA function often used for programming user interactions (see Chapter 4, “Access VBA Built-In and Custom Functions,” for more information on this function).
If you’d like to write a more universal procedure that can display a different message each time the procedure is executed, you will need to write a subroutine that takes arguments. Arguments are values that are needed for a procedure to do something. Arguments are placed within the parentheses after the procedure name. Let’s look at the following procedure that also displays a message to the user; however, this time we can pass any text string to display:
Sub ShowMessage2(strMessage)
MsgBox strMessage End Sub
This subprocedure requires one text value before it can be run; strMessage is the arbitrary argument name. It can represent any text you want. Therefore, if you pass it the text “Today is Monday,” that is the text the user will see when the procedure is executed. If you don’t pass the value to this procedure, VBA will display an error.
If your subprocedure requires more than one argument, list the arguments within the parentheses and separate them with commas. For example, let’s improve the preceding procedure by also passing it a text string containing a user name:
Sub ShowMessage3(strMessage, strUserName)
MsgBox strUserName & “, your message is: ” & strMessage End Sub
The ampersand (&) operator is used for concatenating text strings inside the VBA procedure. If we pass to the above subroutine the text “Keep on learning.” as the strMessage argument and “John” as the strUserName argument, the procedure will display the following text in a message box:
John, your message is: Keep on learning