The following sections provide useful information about VBA. The object names are provided so you can easily examine them in the Northwind 2.0 Starter Edition database.
VBA (Visual Basic for Applications) is the programming language used in all Microsoft 365 products, not just Access. It is primarily documented here: Access developer documentation. To get started, see Introduction to Access programming.
When searching for information, be sure to look for Access specific examples and include Microsoft Access in the search term. Often, solutions for the other Microsoft 365 products will work, but may require changes.
Microsoft Access is a mature product. That means there are a lot of examples out there, which is great for you. This also means that older books on Access programming are still viable for you to look at. Many of the older books are still available on used book sites at a fraction of their original cost.
Microsoft Access files are Microsoft 365 files. Microsoft 365 files must be in a trusted Location or have their content enabled. These items are considered safe because you created them, or they have come from a trustworthy source. This check occurs every time you open any Microsoft 365 file. We will refer to this as trusted/enabled from here on. If a new version of the application is released and opened from a non-trusted location, the process of enabling the content will repeat. For more information, see Trusted Locations for Microsoft 365 files, Decide whether to trust a database, and Add, remove, or change a trusted location in Microsoft 365.
Macros, functions and sub procedures are how you implement business logic into your Access database.
Controls on a form (such as buttons, text boxes, labels, and so on) can use their events (like clicking the control) to trigger other processes, such as adding, deleting records, or opening forms. These processes can be implemented using macros, or VBA. Northwind uses mostly macros, and some VBA. For more information, see RunCode Macro Action.
Some control types have built-in wizards that automatically create a macro. For example, adding a command button to a form will open a wizard that will offer several choices of functionality for the button. Adding a combo box will open a wizard that can be configured to find a particular record on the form.
The Navigation Pane is the main way you view and access all your database objects and it displays on the left side of the Access window by default. The Northwind 2.0 Starter Edition Navigation Pane has been customized. We created a custom category called Northwind Starter 2.0. This allows us to organize the objects by functional area. For more information, see Customize the Navigation Pane.
It's important for you to learn about scope and visibility within Access and Microsoft 365. Scope refers to the availability of a variable, constant, or procedure for use by another procedure. There are three scoping levels: procedure-level, private module-level, and public module-level. You determine the scope of a variable when you declare it. It's a good idea to declare all variables explicitly to avoid naming-conflict errors between variables with different scopes. All modules have two directive statements: Option Compare Database and Option Explicit. For more information, see Understanding scope and visibility, Public statement, Private statement, Static statement, and Understanding the lifetime of variables.
Sometimes you need a variable to exist after the object which created it goes out of scope. There are three primary ways to do this: Public Variables, TempVars, and storing the values in a local table. Each of these has pros and cons. Many developers use a mix of these.
Public variables and TempVars exist for the current session and go out of scope when the application is closed. What if you want to keep user specific variables across their sessions? You can store those types of values in a local table. In Northwind 2.0 Starter Edition, we have those values in a table called SystemSettings. For example, a value in the table is "ShowWelcome". This value tells us if you want to see the welcome screen each time you log in or not.
If you’ve used any control wizards built into Access you know that if a macro is created, often there is no error handling at all, and if VBA is created, it may be limited to a MsgBox Function, Err.Description style.
In Northwind 2.0 Starter Edition, we have implemented what’s called a global error handler. Errors that happen in any procedure call a function at the global level to show the error. The big advantage is that code is consistent, and if the message needs to change, for example showing the error number or logging the error to a file, it only can be done in one place.
clsErrorHandler is the class module that implements the error handling code. A class module keeps all its main and helper functions together in one unit, making the code more encapsulated. The AutoExec macro eventually calls the startup function in modStartup, it creates an instance of clsErrorHandler, and saves it as a global variable so it can be used throughout the application.
In fact, the error handling code in the procedures is so consistent that we were able to create all of it in less than five minutes using some fancy VBA code that outfitted each procedure with the proper error handler. This code is not included in the template.
