Simply Visual Basic for Microsoft Access

(1)

1

Microsoft® Access Made Simple

Thank you for subscribing to Simply-Visual Basic® for Microsoft ® Access Newsletter. This Newsletter is a product of the www.simply-access.com website,

The Newsletters are a series of lessons on Visual Basic® for Applications development system. The lessons are easy to understand with numerous screen shots and detailed explanation of the Visual Basic® for Applications development system code.

If you have no idea about the Visual Basic® for Applications development system this is the place to start. These lessons will give you a firm grounding to expand your wings when building your

Microsoft® Access Databases.

Disclaimer

Whilst every effort is made to test the code contained within these lessons, the code provided by this Simply-Visual Basic for Microsoft® Access 2000 Newsletter is for demonstration purposes only. Using the code in your projects is entirely at your own risk.

Acknowledgements

Symbols

® = Registered trademark or service mark TM = Trademark ownership claimed © = Copyright ownership claimed

Microsoft ® Visual Basic® and Windows ® are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Screen shot(s) reprinted by permission from Microsoft ® Corporation.

References to Microsoft ® Corporation, its products, trademarks and screen shots are in accordance with their guidelines, www.microsoft.com/permission and http://www.microsoft.com/trademarks.

Copyright

(2)

Newsletter Number Three.

Objective

This week, you will become familiar with the hierarchy of your code, what fits where and with what.

You will become more familiar with:

• modules

• the difference between public and private routines;

• subprocedures

• functions

Introduction

I thought long and hard on how to portray this part of the lesson. Since I come from a health

background, prior to delving into Microsoft Access and VBA, I thought the only difference between private and public, was in relation to whether a patient had health insurance (private) or not

(public). This new language of VBA meant I had to learn new meanings for different words, to grasp the meaning of new words I had never heard before and the most difficult (at times) not to use spaces when joining certain words together. These are only a few of the new things one has to learn when delving into VBA. So it is with the thought you also may also be going through the same learning curve that I decided to start the lesson with an explanation of the different hierarchical sections of a module and then to use a graphic portraying a birds-eye view of where code is added to Microsoft Access, what purpose that code has in the place it is stored and the difference between the types of code stored where. This may sound confusing at the moment, but as the lesson

continues, this will become clearer.

You will find as you read more on VBA, different programmers or ‘experts’ call different types of modules and procedures different names. I do not profess to be an expert; all I can hope to achieve

(4)

is to pass on the information I know to you in the simplest way possible. I will include the different names for modules and procedures that I know of, this does not mean it is an exhaustive list and it is likely you have found different names yourself as you research this very interesting topic.

So here goes my attempt. What I am displaying in Figure 3.1 is my understanding of the hierarchy of modules, procedures and functions within Microsoft Access. But first I will clarify the names I am going to use within these lessons. I have listed them in bold (headings) in the following list of definitions. (In future editions of this Newsletter, these definitions will be included in the Glossary.) The other names I have come across are listed just below the definitions in italics. As indicated earlier I do not presume this is an exhaustive list. I have even left a couple of spaces for you to add other names, which you may come across. You may wonder why I am including the other names, but when you are reading on this subject elsewhere, you can refer back to this list, to compare the different terminology and even add your own researched names to the list.

Definitions

Modules

Modules are where all the code (procedures) are written and stored. Each module can contain many procedures. Modules are Standard Modules, Form Modules or Report Modules. Standard Modules store procedures that can be used by any form or report that is within your database, whereas Form Modules or Report Modules are assigned to a specific form or report. The

procedures contained within Form or Report Modules can only be accessed by the form or report they are assigned to.

You may also see Form and Report Modules referred to as: Class Modules

Form Class Modules Report Class Modules Code Behind Forms (CBF) Other names you have found:

(5)

5 Procedures, subprocedures and functions

Procedures are code that performs a specific task; this may consist of one or more actions as designated by the code. As an example the ‘On Click’ event for the first command button found in the form frmMainMenu in Lesson Two (VBADatabase2), performs two actions, which results in the task of opening the frmPersonalDetails and moving to a new record.

Procedures can be either subprocedures or functions. The major differences between

subprocedures and functions are that a function returns a value whereas a subprocedure does not. The above example of the ‘On Click’ event to open a new form and move to a new record is a subprocedure as there is no value produced; only a series of actions (2) are undertaken.

Subprocedures can also be known as: Subroutines

Other Names you have found:

________________________ ________________________

If you consider the second Command Button in VBADatabse2 (frmMainMenu), where there is a series of Message Boxes, which are displayed in response to what the user selects from the Combo Box. The OK button, which the user clicks, has the value of 1 assigned to it. This value of 1 could have been returned for use in another procedure or on the form. If this was the case, then this procedure would be a function instead of a subprocedure as it currently is.

Procedures can also be Private or Public. Private procedures can only be called from the form or report of which they are assigned. Public procedures can be called from anywhere within the database. For this reason Private procedures tend to be found in report or form modules and Public procedures in standard modules. Although public procedures can be included in form or report modules, this practice is discouraged, as it is best to keep public procedures together, to enable the programmer to find them more easily.

(6)

Figure 3.1 outlines the hierarchy of modules, procedures, subprocedures and functions within Microsoft Access. What this graphic is depicting is that Modules can be either Standard or Form/Report Modules. If they are Standard modules they are most likely to contain Public procedures and if the are Form/Report modules, Private Procedures. Public or Private procedures, can be either subprocedures of functions.

Modules

Standard Modules Form/Report Modules

(Stored in the Module window) (Stored with the form or report)

Public Procedures Private Procedures

(Can be called from anywhere) (Can only be called from the specific form or report)

Public/Private Functions

(Returns a value )

Public/Private Subprocedures

(Completes a series of actions and does not return a value)

Is this all clear?? Probably not!! It took me absolutely ages to get my head around all this, and I am sure it is no easier for you (or maybe it is :0)).

So lets look at a few visual examples of each of the above.

Module.

A module is storage place for procedures. Figure 3.2 is an example of a module that contains many procedures. If you have a look at Figure 3.2 you will see 3 blue/grey lines. These lines separate each procedure. The procedures contained within the one Module are usually related and when developing you own code, this is a good practice to get into. In the case of the Module depicted in Figure 3.2 all of the procedures relate to the one form (OnSite), therefore it would be known as a

Figure 3.1 Hierarchy.

(7)

7 Form Module.

Standard Modules

Standard modules on the other hand are more general in nature. They can be called from anywhere within the database, therefore they are stored in the Modules section of the main window. The module ‘TimeCalculations’ as illustrated in Figure 3.4 would contain many subprocedures and/or functions on the calculation of time. As these are calculations it is more likely the procedures would be functions and would return a value. When opened in design view, the layout of a Standard Module is similar to that of a Form Module as illustrated in Figure 3.2.

Figure 3.2 Form Module.

(8)

Private Subprocedure

Within the Form Module (Figure 3.4) there are two private functions. Private Sub Combo22_AfterUpdate()

Private Sub Command24_Click

You can identify these procedures as Private by the word ‘Private’ at the beginning of the first line of code. You also know they are going to complete a series of actions and not return a value, by the word ‘Sub’ which stands for subprocedure.

In the first procedure, following the word sub you can see ‘Combo22_AfterUpdate’, this is the name of the procedure, but it also alerts you to when the subprocedure will be run. In this example it is after the user updates the value in the combo box called Combo22.

Have a look at the second subprocedure; it starts after the second line in Figure 3.4. When do you think this subprocedure would be called into action?

If you answered, “when the user clicked the command button called Command24”; or similar, you would be correct.

Figure 3.3 Standard Module.

(9)

9 We are not going to step through all the code this week, as I think it is enough just to understand the hierarchy of the code without trying to learn anything extra.

Public Subprocedure

Figure 3.5 (following) illustrates two very simple Public subprocedures. They can be identified as public by the word ‘Public’ and ‘Sub’ at the beginning of each of the procedures. These public subprocedures would be stored in a Standard Module.

Figure 3.4 Private

Subprocedures. Private

(10)

Public Function & Private Function

Functions are identified by the word ‘Function’ and are preceded by the word Private or Public depending on whether it is a private of public function. Similar to the private or public

subprocedures previously discussed and illustrated.

Conclusion

This concludes the Lesson for Week Three. I hope you have enjoyed this lesson. Next week we will be discussing the Dim statement, Option Explicit and have a very brief discussion on naming conventions. All these are very important prior to writing your own code.

Figure 3.5 Public