Creating Professional Databases

Visual basic for applications (VBA)

Now that you have mastered many of the skills in developing databases using Microsoft Access, there is just one more thing you may need to know. If you want the full flexibility and power of creating any kind of database, you should know something about Visual Basic for Applications (or VBA).

In the previous section, we saw the benefits of using predefined scripts called macros to help perform a variety of actions. While this has proved to be invaluable, there will be circumstances where you cannot rely on macros to achieve everything you want. This is where VBA comes into its own.

VBA is the language for creating your own truly customised functions and subroutines where you can perform tasks in exactly the way you want it. All the secrets to making powerful databases really comes from this programming language and is the key to your full understanding of this database development tool.

When would I need to use VBA?

It is best to use VBA when:

  1. you need to create more sophisticated conditional operations than is possible through the Macro window;
  2. you must perform repetitive tasks using a Loop statement;
  3. you have to create more complex input dialog boxes than is possible using the standard MsgBox macro;
  4. you want to manipulate the data in an entire table or part of the table (also known as a dynaset);
  5. you require more effective error trapping; and
  6. you want to implement external third-party plug-ins known as DLLs into your database solutions.

Where are VBA scripts stored?

VBA scripts are found in what are known as modules. Modules are the storage areas for holding the VBA instructions. If you need an analogy, try to think of modules as like Microsoft Word documents. Each Word document holds a certain amount of text. The same is true of modules.

To keep things simple for programmers, Microsoft Access divides modules into two types:

  1. Class Module

    This is where all the VBA programming code needed to perform a task associated with forms are stored. So if you click a button on the form, tab out of a field, open or close a form, and do any kind of event associated with the form, a piece of code may be run to perform a task and this is stored in the Class Module.

  2. Standard Module

    This stores other VBA programming code designed to perform tasks that is not related to a form.

The existence of a Class Module and a Standard Module merely helps programmers with their house-keeping work since databases can get very large and complex with numerous tasks to be performed by VBA. To be able to see clearly which VBA code affects which forms and to separate those not associated with a form, Microsoft Access has divided the tasks into these two modules.

Now within each module, there can be numerous tasks to be performed in the language of VBA. These tasks may all call upon one another as required to perform an overall task by the module. Or the tasks may be independent sets of VBA code for use in different parts of the database.

To call upon these tasks, we need to give them a name and to define where the tasks begin and end in the language of VBA. We do this by using one of the following pieces of VBA code in Microsoft Access:

  1. Procedures

    Sub TaskName()....End Sub

  2. Functions

    Function TaskName(inputvalueA, inputvalueB, etc)....End Function

The term function means to return a value after performing the task within the function. You supply any information the function needs to perform its task, and the function will spit out a result. The term Sub, on the other hand, does nothing more than perform tasks of a general nature without spitting out a result. You merely let the subroutine do its job of gathering and/or manipulating data or other objects in your database or other databases and nothing more.

Where do I write VBA code?

If you want to associate VBA code with an event on a form:

  1. you should first highlight a control on the form where you want the event to happen. For example, you may have designed a button on the form to perform a task but the task itself has not been defined or programmed in any way (i.e. it does nothing when you click on it). Choose this button.
  2. Open the Properties dialog box for the control.
  3. Within this Properties dialog box, click on the Event tab.
  4. Choose an event you want the VBA code to be associated with (i.e. activated). For example, if the event is to click on a button, choose the OnClick event by clicking into the white field box next to the event.
  5. You will notice the Expression Builder button will appear to the right of the white field box for the OnClick event. This is the button with the three full stops (...) inside of it. Click this button.
  6. You will be shown the Choose Builder dialog box. Select Code Builder. The row will appear highlighted.
  7. Click the OK button. This will activate the Visual Basic Editor window where all the VBA code is written.

How do I write VBA code?

Well firstly let us begin by understanding how this Visual Basic Editor window works.

You will notice on the left side of the window a folder containing the Microsoft Access Class Objects. The folder has been opened to show you all the Class Modules. One of the Class Modules has been highlighted for you. The name of this highlighted module refers to the form you are working on. Any VBA code referring to this form are stored inside this particular Class Module highlighted for you.

Similarly, if you need to refer to the value of a field on the form when writing your VBA code, you would use the name of this module and the field name separated by a full stop.

On the right side of the window is where the VBA code is actually written and edited. Microsoft Access has been kind enough to type in the Sub...End Sub codes for us. This lets us know we are ready to build the subprocedure for performing a certain task inbetween these two pieces of code. As a general rule of thumb, any code we type in here should be indented to make it more readable.

When creating VBA code, we usually begin by defining variables and constants. These are places to temporarily store information and use it as part of the VBA code to help the subprocedure perform a task. Of course, this depends on the nature of the task to be performed. Sometimes the code behind the task does not require variables and constants. Or sometimes it does.

To define variables and constants, we use the VBA code Dim and Const respectively, which is a short form for Dimension and Constant. Now suppose we want to create a constant whose name is Secret with the value 4. To define this constant in VBA, we type:

Const Secret=4

To define a variable called UserInput to store an integer number, we define the variable as follows:

Dim UserInput As Integer

We can remove the words "As Integer". If we do, the variable UserInputText becomes a variant, meaning it can store data of any type (date, integer, decimal numbers, text, pictures etc), or you can type:

Dim UserInput As Variant

If you do this, any calculations you do later in your VBA code may require you to test and/or convert the value of this variable into a suitable type for the calculation to have meaning. Usually it is better just to define the type right at the onset so you can minimise the work you have to do later on.

So now your VBA code in total should look like this:


Sub FindSecretNumber()

  Const Secret=4

  Dim UserInput as Integer

End Sub
 

To create an Input dialog box for the user to enter a number into the UserInput variable, we write:

UserInput=InputBox("Enter the secret number between 1 and 10")

When this piece of code is run, a dialog box is shown with the message "Enter the secret number between 1 and 10" together with a white field box underneath where the user can type a number.

But how do you know whether the number will be an integer? Good question. Are you familiar with Microsoft Excel functions? If so, you may recall how the Int() function helps to turn any number into an integer (i.e.... -3, -2, -1, 0, 1, 2, 3...). This same function can be used in VBA to turn any number entered by the user into an integer in Microsoft Access. So now our VBA code will look like:

UserInput=Int(InputBox("Enter the secret number between 1 and 10"))

But again will the user be so nice as to enter an integer between 1 and 10? Probably not. This is where the If...Then...Else statement comes in handy. Firstly, let us write the condition for testing the variable UserInput to see if it is less than 10 and greater than 1. If you are familiar with Microsoft Excel, you can write the condition as follows:

UserInput < 10 and UserInput > 1

condition in VBA code:


If UserInput < 10 and UserInput > 1 Then

  [Additional VBA code when the condition is true]

Else

  [Additional VBA code when the condition is false]

End If
 

To display a message to the user, we type the VBA code:

MsgBox "Enter your message here."

Hence our conditional statement can be written like so,


If UserInput < 10 and UserInput > 1 Then

  MsgBox "Thank you."

Else

  MsgBox "Sorry. This number is not between 1 and 10."

End If
 

Now the aim of this subprocedure is to determine whether the user knows the secret number. In order to do this, we have to test UserInput with the constant Secret to see if the values are the same. The conditional statement we could write is:


UserInput=Secret
 

This makes the If...Then...Else statement look something like the following:


If UserInput=Secret Then

  MsgBox "You have found the secret number."

Else

  MsgBox "Sorry. This is not the secret number."

End If
 

Is there a way to ask the user to reenter the number again and again until he/she guesses the correct number? This is where the Loop statement comes in handy. The loop statement we can use in VBA code is:


Do

  UserInput=Int(InputBox("Enter the secret number between 1 and 10"))

  If UserInput < 10 and UserInput > 1 Then

    If UserInput=Secret Then

      MsgBox "You have found the secret number."

    Else

      MsgBox "Sorry. This is not the secret number."

    End If

  Else

    MsgBox "Sorry. This number is not between 1 and 10."

  End If

Loop While UserInput <> Secret
 

The Loop statement says "repeat everything between the Do and Loop statements while the condition is untrue. But if the condition is true, escape from the loop and do something else."

If you want to be really efficient about it, you could simplify this VBA code in the following manner:


Do

  UserInput=Int(InputBox("Enter the secret number between 1 and 10"))

Loop While UserInput <> Secret
 

Then the complete VBA code would be:


Sub FindSecretNumber()

  Const Secret=4

  Dim UserInput as Integer

  Do

    UserInput=Int(InputBox("Enter the secret number between 1 and 10"))

  Loop While UserInput <> Secret

  MsgBox "Yes! The secret number is " & CStr(UserInput)

End Sub
 

Notice how we used CStr(UserInput)? This is a funny feature of VBA which requires us to convert the integer stored in UserInput into a text string so it can be properly displayed in the message box. Sometimes it would be nice if Microsoft Access could automatically make the conversion when using a message box. But as you will find, often you must be precise at the moment of writing VBA code in order to tell Microsoft Access exactly what you want it to do. With practice, you will develop the skills of being precise while still balancing this with refined and simplified code.

Okay. We are not quite finished with the VBA code. Sometimes a user running this code may decide to enter a number so large that any testing we do with the Loop statement (or the If...Then...Else statement) would collapse because of a general Microsoft Access error. To capture all possible errors properly, we must introduce an error trapping routine inside the subprocedure like so:


Sub FindSecretNumber()

On Error GoTo Trap

  Const Secret=4

  Dim UserInput as Integer

  Do

    UserInput=Int(InputBox("Enter the secret number between 1 and 10"))

  Loop While UserInput <> Secret

  MsgBox "Yes! The secret number is " & CStr(UserInput)

TrapExit:

  Exit Sub

Trap:

  If Err.Number=6 Then

    MsgBox "Your number is much too big!", vbOKOnly

  Else

    MsgBox "Unknown error: " & Err.Number, vbOKOnly

  End If

End Sub
 

Error trapping in the language of VBA works by first telling Microsoft Access any outrageous errors it finds is to be handled by a subroutine called Trap, otherwise perform the VBA code that follows immediately after this error trap until it reaches TrapExit where the Exit Sub terminates the subprocedure. If the Trap subroutine has to be run because of an error, there is the option to test the error number and give information to the user what the error is about and how to solve it.

This is a complete VBA subprocedure! To run it, place the cursor anywhere between Sub and End Sub and click the Run Code button in the toolbar.

The importance of planning...

Perhaps this is a good time to explain the importance of planning when developing the VBA code. For example, will the code need to run through all or some of the records in your database to test or make changes to one or more fields? If so, you will definitely need code for creating a Loop statement.

Do you need to test the values of the fields for a condition before implementing the changes? If so, you will definitely need the If...Then...Else statement.

Will the user require to input information? If so, you will need VBA code for creating an input dialog box.

What happens if something should go wrong? What error checking will you need to test for should the user enter nonsense information? How will you display a message to let the user know what is happening?

All these questions are the sorts of things you need to be thinking about and have a basic solution for. It may not be necessary to write the exact VBA code straight away. But you need to have a rough idea of how you will go about achieving the things you want from your subprocedure (or function).

Once you have a basic plan, start investigating the VBA codes available to achieve each step in your plan. For example, there are several different Loop statements. Which exact Loop statement will do the job properly? If you need help in choosing the right VBA code and the arguments needed to make the code work, use the Help facility and type the name of the VBA code.

Revealing the useful VBA codes

For a selection of the more useful and powerful VBA codes for doing specific tasks, have a look at the following:

Loop statements

As you've seen in the previous example, the "While UserInput<>Secret" component appears at the bottom of the loop and just after the word "Loop". In other Loop statements, the "While [condition]" statement can appear immediately after the word "Do". This is useful if you want to test a condition before commencing the loop process. Another variation of these two loop statements is to replace the word "While" with "Until". The "Until" statement is used to repeat a loop until a condition is met. The "While" statement is used to repeat a loop while the condition remains false. But if you are clever, you can implement a loop using one type of loop statement. The other loop statements merely help to make the VBA code more readable and easier to understand.

For loops

Just to keep programmers on their toes, there is also a For...Next loop statement. This loop is designed to repeat a set of VBA code for a fixed number of times. In other words, no condition needs to be specified to exit from the loop. But again, if you are clever, one loop statement can handle this situation.


Sub CountLoop()

  Dim Counter as Integer

  For Counter=1 To 10

    MsgBox CStr(Counter)

  Next

End Sub
 

You can also step the counter by a certain amount if you wish by writing "For Counter=1 To 10 Step 2". This means the loop will occur five times instead of the original 10 times because of the way the original loop increments the Counter by the smallest whole integer number (i.e. 1) but now the increments are in steps of 2.

CStr(), CInt(), CDate()

Common VBA code names for converting a variable, constant or field value to a specific type. In order of appearance, the codes are (i) convert to a string (i.e. text format); (ii) convert to an integer; and (iii) convert to a date.

Date()

This built-in Microsoft Access function (i.e. you don't have to create it yourself) returns today's date.

DoCmd

This is one of the most useful VBA commands. This one is designed to perform Access commands. Want to open tables, reports and forms? Try DoCmd. Want to create a search button? Try DoCmd.

More about functions

Functions are a great way to generate quick results for use in any query, control on a form, report or other objects. When you create a function, you call the function by its name and, if necessary, provide information inside the parenthesis () so the function can do its job. Once you've called the function up, it immediately returns a value.

Here is an example:


Function Reverse(Phrase)

  Dim TempPhrase as String

  Dim Counter as Integer

  Dim StringLength as Integer

  StringLength=Len(Phrase)

  For Counter=StringLength To 1 Step -1

    TempPhrase=TempPhrase+Mid(Phrase, Counter, 1)

  Next

  Reverse=TempPhrase

End Function
 

The function you have just seen is designed to reverse the letters of every word in a phrase so that it appears backwards. There may or may not be a use for this kind of function in your own work. But for the purposes of teaching you VBA, this is a great way to learn more about functions.

The way this function works is firstly to let the function know information is coming into it through a variable called Phrase. Secondly we define a few variables to help with the process of reversing the text. Thirdly we set StringLength to the text length of Phrase so we can specify how many times the For...Next statement is to loop to complete the task. Fourthly, we perform the actual task of reversing the letters in the text stored in Phrase by moving right to left and picking out individual characters using the Mid() function, and storing this character from a left to right movement inside TempPhrase. To make sure we don't lose whatever is already in TempPhrase when we go through the loops, we set TempPhrase to have the same text value as TempPhrase plus we add an extra character. As the loop repeats itself, extra characters keep getting added at the end of the text string inside TempPhrase until the entire length of the text string for Phrase is reached. The original text in Phrase has now been reversed and stored inside TempPhrase.

There is one more thing to do before the function is complete. We must tell the function what the result is. This means using the name of the function and giving it the value of TempPhrase. That is why we write "Reverse=TempPhrase". So now the function can return the result.

That's it! The function is ready to perform its task of printing backwards any text that is thrown into it.

If you want to use this function in your own work, all you have to do is write "Reverse("Reverse this text")" or "Reverse(FieldName)" where FieldName is a field containing the text you want to print backwards. The critical thing to remember is writing the basic function which is Reverse(). What you put inside the brackets is totally up to you. But choose something that you know will have some text.

How do I use fields on a form in my VBA code?

Now we start to get into something which is a bit trickier. The way we use fields in VBA code is to write a reference path to the field so that Microsoft Access can understand that you are referring to a field. The field is referenced like so,

[object]_[name of form].[name of field].[type]

This may look like goobledygook to you, but imagine you created a form and placed a few fields on it. The form name is called "frmDataEntry", and the field you want to reference in your VBA code is called "LastName". Because we know this field is of type "text", we can write a reference to this field in VBA code in the following way:

form_frmDataEntry.LastName.Text

All this does is tell Microsoft Access to look in the Form object to find the form called "frmDataEntry" and somewhere on this form should be a field called "LastName" of type "Text". Once Microsoft Access knows what we are referring to, we can use the value stored in LastName for the current record to do whatever we want in our VBA code or change the value of it in any way we wish.

What happens if the field name has a space character in it? If the field name is "Last Name" instead of "LastName", you must put in the square brackets around the field name like so,

form_frmDataEntry.[Last Name].Text

The same is true for the name of the form itself.

How do I stop others from viewing and editing my VBA code?

You have two options:

  1. Password protect your modules containing your VBA code; and/or
  2. Save your database file as an MDE file.

MDE files is a special format created by Microsoft Access for optimising the VBA code into a non-editable source code and then compacts the database. Such files are designed to make for a smaller size and gives greater performance when running them.

You only make an MDE file of an Access database when the database is complete and thoroughly tested and you are ready to distribute the database to your clients. When you do create an MDE file, you must keep the original Access database because you cannot redit your VBA code if you do.

To create an MDE file:

  1. Close the database you want to convert to the MDE format if it is already opened.
  2. Choose Tools>Database Utilities in the menu command.
  3. Click Make MDE File
  4. Find the Access database you want to convert.
  5. Click Make MDE and choose a location and name for the MDE file.
  6. Click the Save button.