Friday, 15 February 2019

Creating a Custom Save Button for an MS Access Form.

When we use an MS Access form to create a new record, or update one that already exists, the process of saving that data is normally taken care of automatically "behind the scenes."  All we need to do is move to or create a new record, close the form, or close the database, and Access saves the original record for us.

Record Navigation Buttons: when a record is added or changed, moving to a new or different record 
via the navigation buttons, will save the origin form record automatically.

However, there are times when it may necessary to save a record manually.  Suppose, for example, we have two tables joined together in a one to many relationship. We create a new record on a form bound to a table on the one side of the relationship, and then click a button to open a new form bound to a table on the many side of the relationship.  The newly opened form is then used to create a sub-record for the parent record on the original form.

Parent and sub-forms: In this screenshot, the parent form "Work Session" is bound to the tbl_work_session table, and the "Entry Log" form is bound to the tbl_entry_log table.  There is a one to many relationship between the two tables, with tbl_work_session being on the one side of the relationship.
The problem in this scenario is that when we come to close the form for the sub-record (thereby saving the data to the underlying table on the many side of the relationship), the parent record on the original form has not, at this stage, been inserted into the underlying table on the one side of the relationship.  That is to say, Access has not got around to autosaving the parent record since the parent form has not moved to any other record, or been closed etc - all we have done is click a command button to open a new form.  As such we get an error message saying "You cannot add or change a record because a related record is required in [the parent] table"; this is because the rules of referential integrity have been broken (ie the sub-record in the many table requires a parent record in the one table).

However, it is simple to overcome this issue. We just need to save the parent record manually before trying to close the sub-record form.  A quick and easy way to do this is to click the pencil symbol on the top left of the parent-form, or the SAVE icon in the RECORDS group of the HOME ribbon. This will save the parent record, thereby allowing it's sub-record to be saved afterwards.

 The pencil symbol (above left) can be used to save the current record manually.  Alternatively, the SAVE icon in the RECORDS group of the HOME ribbon (above right) can also be used.
However, this is not particularly useful from the perspective of user-friendliness. That is to say, a user may not realise or understand that this action is necessary.  A better way of doing it would be to save the parent record automatically when the user clicks the command button to open the sub record form. As such, we need to alter the open form button (referred to as "CREATE NEW ENTRY" in our example above) so it also becomes a custom save button.  To do this, we just add the following line of VBA code to the buttons ON CLICK event:

Application.RunCommand acCmdSaveRecord

See the screenshot below for the full code:

VBA code to save the parent record before opening the sub-record form.

Creating a Custom Save Button from Scratch - A Tutorial

Here is a step by step tutorial for creating a custom save button from scratch on Access for Office 365:
  1. Open the form in DESIGN VIEW.
  2. Go to the CONTROLS group of the CREATE ribbon.
  3. Click the MORE button () to the lower right of the control icons, and de-select the USE CONTROL WIZARDS icon when the additional options appear. 
    Above: additonal  options below control icons.
  4. Click the BUTTON control icon () from the CONTROLS ribbon. The cursor will now change to a button icon when it enters the design grid.
  5. Position the cursor at the desired location on the design grid, and click the left hand mouse button.  This will create the new button at the selected location.
  6. Select the new BUTTON contol by left clicking it, and open the PROPERTIES sheet from the TOOLS group of the DESIGN ribbon (if it is not already open).
  7. Click the EVENTS tab on the properties sheet, and left click the ON CLICK row of the events tab.
  8. Click the ELIPSE button (three horizontal dots) which appears on the right hand side of the row.
    Above: the ON CLICK event row on the PROPERTIES sheet.
  9. Select CODE BUILDER from the CHOOSE BUILDER dialog form which opens.  This opens the VBA editor.
  10. Enter the following code in the CLICK sub procedure for the control button.

Above: The CLICK sub procedure for the new control button shown in the VBA editor.
Now when the user clicks the new button at runtime, any data s/he entered will be saved to the corresponding record in the forms underlying table.

Friday, 25 January 2019

Passing Multiple Values via the OpenArgs Parameter of DoCmd.OpenForm

The DoCmd.OpenForm command is a useful tool for the Access for Office 365 database developer wanting to control the manner in which a form opens during runtime.  I have written about it previously in my post on how to "Control Form Opening".  However, in today's post, we are going to focus on a specific area of this command's use - that is, how we can pass multiple values via its OpenArgs property.

The OpenArgs parameter of DoCmd.OpenForm gives us the means to pass a single string value to the OpenArgs property of new form being opened.  After the form has opened, this value can then be read and used by any code able to reference the new forms OpenArgs property. As things stand, the limitation of this is that we can only pass one string value as the command executes.  So let's see how we can get around this, and pass any number of strings values in one go.

To demonstrate how this works, I have created an Access for Office 365 form containing three text boxes and a submit button.  When the user populates the text boxes and clicks SUBMIT during runtime, the values contained in each of the three text boxes are passed over to a new form by means of the OpenArgs parameter of the DoCmd.OpenForm command.

Above: Form to pass multiple values via the OpenArgs parameter of DoCmd.OpenForm.

You can download the full OpenArgsPost.accdb database used in the this exercise from here. 

(NB I have tested the download process using the Chrome browser which works OK.  The download process may fail using other browsers such as Microsoft Edge. If you experience issues please try using a different browser). 

Part One: Passing the Values

Let's look at what happens behind the scenes when the SUBMIT button is clicked.  First of all, clicking SUBMIT (referred to as btnSubmit in DESIGN VIEW) results in the control button's ON CLICK event firing.  This, in turn, starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnSubmit.

The code begins by declaring a string variable called strOpenArgs:

Dim strOpenArgs AS String

The next two lines of code reference the value entered by the user in the first text box.  The value is stored in the strOpenArgs string:

strOpenArgs = txtValue1.Text

The next two lines of code after that reference the value of the second text box, and append it to the strOpenArgs string preceded by a semi-colon (the significance of the semi-colon will be explained in part 3 of this post):

strOpenArgs = strOpenArgs + ";" + txtValue2.Text

This is then repeated for the third text box:

strOpenArgs = strOpenArgs + ";" + txtValue3.Text

So at this point in the program flow, the value of strOpenArgs is as follows:


This is the final value of the DoCmd.OpenForm OpenArgs parameter which will be passed to the new form in the following line of code:

DoCmd.OpenForm "frmReceiveOpenArgs", , , , , , strOpenArgs

Part Two: Retrieving the Values

Let's take a look at the new form which opens after the SUBMIT button has been clicked, and the event procedure explained above has finished executing:

Above: the new form which opens in response to clicking the SUBMIT button.

As you can see in the screenshot above, the new form consists of three empty text boxes (for displaying retrieved values) and a RETRIEVE button.  When the user clicks RETRIEVE the text boxes will be populated with the values submitted from the first form.  In our case, Retrieved Value 1 will display the value "one", Retrieved Value 2 will display the value "two", and Retrieved Value 3 will display the value "three".

Let's look at what happens behind the scenes after the RETRIEVE button is clicked.  Like the first form, clicking the command button, which in this form is called btnRetrieve, results in it's ON CLICK event firing.  This starts the following VBA event procedure (see screenshot below):

Above: The VBA event procedure for btnRetrieve.

The code begins by declaring a string variable called strOpenArgs before referencing the forms OpenArgs property and storing its value in the string.  (Just to recap, the value of the OpenArgs property (ie "one;two;three") was determined by the OpenArgs parameter of the  DoCmd.OpenForm command invoked when the user clicked the SUBMIT button on the previous form):

dim strOpenArgs As String
strOpenArgs = Me.OpenArgs

The next couple of lines of code are very important. A string array called strValues is declared and has its elements populated with data returned from a function called getOpenArgsArray.  This function is located in a separate module called Utilities which is global in scope.  This is where the core of the processing is done for this exercise:

Dim strValues() As String
strValues = getOpenArgsArray(strOpenArgs)

The purpose of the getOpenArgsArray is to split the value of its parameter, strOpenArgs (which in our case is "one;two;three"), into three separate strings, with each array element containing an individual string.  So for us, the strValues array is populated with the following values:

Array ElementValue

These values are assigned to each of the three text boxes in the next six lines of code:

Me!txtRetValue1.Text = strValues(0)

Me!txtRetValue2.Text = strValues(1)

Me!txtRetValue3.Text = strValues(2)

The end result can be seen in the screenshot below:

Above: the end result after the user clicks RETRIEVE.

In this way, we have used the DoCmd.OpenForm OpenArgs parameter to pass multiple values to a new form, thereby overcoming the limitation of only being able to pass a single string within the statement.

Part Three: The getOpenArgsArray Function

However, this post would not be complete without an explanation of the getOpenArgsArray function, as this is where the core of the processing was done for this exercise.  Let's take a look at the code used in the getOpenArgsArray function:

Above: code for the getOpenArgsArray function.
NB: in this screenshot the strArgs array is declared with 7 elements.
This is because the function is global in scope and other event procedures using
 the function may need addtional elements.  This means that the OpenArgs
 parameter of DoCmd.OpenForm can pass up to 7 values with this current setup.
If more elements are needed, the array can be declared with an adjusted number as necessary.

As mentioned in section 2 above, the purpose of the getOpenArgsArray function is to split the value of the strOpenArgs parameter passed to the function, into individual sections, and to save each section as a separate array element. So in our case, the function receives a string containing the value "one;two;three", and returns an array with three elements ie 

Array Element    
Element 1 
Element 2
Element 3

This is acheived by iterating through the strOpenArgs string in a FOR NEXT loop and appending each individual character to a temporary string called strBuildString.  Then when the current character of strOpenArgs in the loop is one of the semi-colons, the value of strBuildString is saved to a new array element. 

Lets break this down further.

The following three lines of code are concerned with iterating through each  character contained in the strOpenArgs string.  Each character is temporarily stored in a string variable called strChr:

For i = 1 To Len(strOpenArgs)
    Dim strChr As String
    strChr = Mid(strOpenArgs, i, 1)

The value of strChr is then checked to see whether or not it is a semi-colon.  If it is NOT a semi-colon, the character is appended to the strBuildString variable :

    If Not strChr = ";" Then
          strBuildString = strBuildString + strChr

If, on the other hand, the character IS a semi-colon, the current value of strBuildString is saved as a  new array element in strArgs().  The strBuildString variable is then cleared ready to build the value of the next array element:

             strArgs(argCounter) = strBuildString

             strBuildString = ""

The element number of the array is kept track of in an integer variable called argCounter.  This is incremented by one after the strBuildString variable is cleared:

              argCounter = argCounter + 1
     End If

Next i

After the last iteration of strOpenArgs has completed, the final array element is saved, and the return value of the getOpenArgsArray function is set to the value of the strArgs() array:

strArgs(argCounter) = strBuildString
getOpenArgsArray = strArgs

Exit Function

Friday, 4 January 2019

Changing "Entry Key Behaviour" to Allow New Lines within a Textbox

In my previous blog post, I wrote about Adding Rich Text Formatting to MS Access Data.  However, there was one related topic which I didn't cover - ie how to change the entry key behaviour to allow new lines within a text box.  This is done by changing the ENTRY KEY BEHAVIOR property of the text box in question.

The ENTRY KEY BEHAVIOR property set to 
NEW LINE IN FIELD for the txtDescription text box.
Let's imagine we have a text box on a form in which we are going enter a large amount of text data separated by paragraphs. We have set the field in the underlying table to accept LONG TEXT data, and perhaps also enabled rich text formatting.  The first thing we do is resize the text box in FORM DESIGN view by selecting the text box and dragging the lower right corner so it is large enough to display a good area of text without having to constantly scroll down.

If we left the ENTRY KEY BEHAVIOR property at its default setting, it would not be possible for a user to create a new paragraph within the text box whenever they hit the ENTER key.  What we need to do, therefore, is change the property value from DEFAULT to NEW LINE IN FIELD. This is how:

  1. Open the form in DESIGN VIEW.
  2. Select the text box control to be modified by clicking it.
  3. Open the PROPERTY SHEET by clicking the icon in the TOOLS group of the DESIGN ribbon (if it's not already open).
  4. Click the OTHER tab on the property sheet.
  5. Locate the ENTRY KEY BEHAVIOR property and change the value to NEW LINE IN FIELD.
Once the property has been changed, the user can enter text in the text box and use the ENTER key to create a new paragraph without the focus shifting to the next control in the tab order.

Above: Example of a text box with the ENTRY KEY BEHAVIOR property modified to

Thursday, 27 December 2018

Adding rich text formatting to Access data

When we store information in an Access for Office 365 database, text formatting (eg setting text as bold or italic, and changing font size etc) isn't normally a consideration. For example, if we have a field storing a title, it is very unlikely that we would need to save the titles' font size.  This is something we can add later to a text box property of a form or report where the date is presented to the user.

Formatted Access 365 text box
Above: notice the text box for the task field
 is in a larger font with bold text. 
Access 365 text box properties
Above: the property sheet where
 the db designer sets formatting
 properties of the text box. 

However, there are certain instances when we do want to save formatting information relating to the data stored in a particular field of a table. This may be the case when a field contains a large amount of textual data, and we want the user entering the data to decide which text within the field is formatted in a particular way; not the database designer who can only apply formatting to all text displayed in any given text box control.

So how do we add rich text formatting to an Access for Office 365 database field?  

Rich text formatting is achieved through the addition of html markup tags to the text they relate to.  These are the same tags used by web pages for formatting content.  However, we don't need any knowledge of HTML markup, because Access for Office 365 does it for us.  All we need to do is change the TEXT FORMAT property of the field within the database table.  NB this can only be done on a table field set to the LONG TEXT datatype.

Here are the instructions for setting up a LONG TEXT field for rich text formatting:
  1. Open (or create) the table containing the field to be set to rich text in DESIGN VIEW.
  2. Change the data type for the field to LONG TEXT in the TABLE DESIGN GRID.
    Above: the entry field being set to LONG TEXT.
  3. Change the TEXT FORMAT property to RICH TEXT in the field property grid.
    Above: changing the TEXT FORMAT property to RICH TEXT.

We can now change from TABLE DESIGN VIEW to DATASHEET VIEW and add rich text formatting to data stored in the field we have just modified.  Do this by highlighting some text within the field and selecting the desired formatting from the context menu that opens automatically. Alternatively, you can also use the TEXT FORMATTING  group of icons from the HOME ribbon.
Above: an example of rich text formatting within an Access for Office 365 table field.

Displaying formatted data on Access for Office 365 forms and reports.

Now that we have set up the table for rich text formatting, there is one other thing we need to do if we want to display our formatted data in a text box control on a form or report. This involves changing the text box's TEXT FORMAT property to RICH TEXT.
  1. Open the form containing the text box we want to change in DESIGN VIEW.
  2. Select the text box control by clicking it in the design grid.
  3. Open the properties sheet by clicking the PROPERTIES SHEET icon from the DESIGN ribbon.
  4. Click on the DATA Tab on the properties sheet, and change the TEXT FORMAT property to Rich Text.

When we now re-open the form in FORM VIEW, rich text formatting is applied to any text the user had previously set before it was saved in the forms underlying table. 

Access 365 Rich Text Formatting Example

Above: an example of rich text formatting within an Access for Office 365 text box control. 
Had we left the TEXT FORMAT property as PLAIN TEXT, any data displayed in the text box would 
not have been formatted; any text that should have been, would have appeared in plain text between html markup tags.

Friday, 8 June 2012

Using MS Access Data in a Mail Merge

Suppose you have an Access Database containing the names and addresses of your customers.  Every now and again you may want or need to send a standard letter to each customer who has opted in to such mailings.  However, you are aware that these letters are going to look so much better, and work so much more effectively, if they are personalised to each particular recipient.  A basic example of this is to print the name and address of the customer at the top of the letter, and to include the customers title and surname in the greeting line.   We can go much further than this, of course, and include information about the customers account and even their previous orders (if appropriate).

There are two ways of doing this. We could create an access report which is fine and good. After all, the data is stored in Access, so why not create a the letter by means of such a report?  It is certainly possible to do so.  However, there may well be an advantage in merging your customer data stored in an Access table or query into an MS Word word-processing document.  It is arguably much easier to sit down and compose a well presented standard letter using MS Word (than it is to create the same document in an Access report).  It might be said that Word is a better tool for this particular job.

Mail Merge
The process whereby data from a table is integrated into a word processing document is known as a mail merge.  The following exercise explains how to use information from the Access table in Figure 1 below.

Figure 1: The schema of the Access table we shall be using.
Connecting to Access Data
This first stage in the process involves connecting MS Word to the database where the customer data  is stored. We are going to be working on blank document here, but normally you would begin by composing your standard letter.  The object of this exercise is to show how a Word document gains access to data in an MS Access table or query (rather than how to compose a well presented Word document per se).
  1. Open MS Word.  
  2. Click the SELECT RECIPIENTS icon.  This is located in the START MAIL MERGE group of the MAILINGS ribbon. Clicking the icon reveals a drop down menu.  
  3. Select USE EXISTING LIST from the drop down menu.
  4. Browse to the location of your Access Database (in the window that has opened) and  double click the it's file name.  This opens the SELECT TABLE dialog form in which you see all the tables and queries from your database.
    Figure 2: The SELECT TABLE dialog form.
  5. Double click the name of the table or query containing the customer names and addresses.  We are interested in the tblCustomer. The dialog form then closes.  You are now ready to select which fields to use in your document.

Accessing Table or Query Fields
We have now connected to the Access Database and the table in which the customer data is stored.  The next stage in the process is to insert fields from that table into the actual Word Document itself.  We are going to use fields to enter the customer's address at the top of the letter, and to produce a greeting line.  If we wanted, we could also go further and insert data from the table with the main text of the letter.
  1. Click the location on the document where the field is to be inserted.
  2. Click the lower half of the INSERT MERGE FIELD split button icon.  This is located in the WRITE AND INSERT FIELDS group of the MAILINGS ribbon.  A drop down list of fields appears (see figure 3 below).
    Figure 3: Insert Mail Merge Fields.
  3. Select the customers title field from the list. A title field place holder now appears on the document.
  4. Repeat step 2, this time selecting the surname field.  Make sure you leave a space between the two place holders. 
  5. Go to a new line and then repeat step 2 again, this time selecting the first address field.  Repeat this step for each of the address fields, each field on a separate line.
  6. Go down two  lines below the address fields to enter the greeting line.  Type a greeting such as "Dear ", leave a space, and then insert a title field, followed by another space, and then the surname field
Your document should now look like this:
Figure 4: The Mail Merge Field Placeholders.
Merging Access Data
Once you have added the mail merge fields to your document, you are nearly ready to run the mail merge. Before we do, however, it is a good idea to preview what the documents are going to look like after the data is merged. Do this by clicking the PREVIEW RESULTS icon on the MAILING RIBBON. Each record from the database is then merged onto a separate document. You can scroll through each of these "document records" using the navigation buttons in the PREVIEW RESULTS group. This is how our document looks when previewed:
Figure 5 (above): Preview for the first record of merged data.
We can now run the mail merge. Do this by clicking the FINISH AND MERGE button (see figure 6 above). You are then given the option to PRINT DOCUMENTS or EDIT INDIVIDUAL DOCUMENTS. Doing the latter opens a new instance of Word containing the merged documents. You are then free to make any adjustments that you see fit, and print out when ready.

Saturday, 2 June 2012

VBA Project Anatomy and Scope

Last week I explained how to create an Access VBA Custom Function in a global module.  This was something of a departure from what I covered in previous blog posts.  All of the programming I had covered previously has been associated with a given form or report - what we refer to as a  Class Object.  In this post we are going to take a step back and look at the general structure of a VBA Project as a whole,  and in so doing we will examine the concept and workings of Scope.

VBA Projects
I have mentioned in the past that Access and VBA are two separate, albeit closely related, pieces of development software.  When we create an access database, a VBA project linked to that particular database is created for us automatically.  It is within this project that we create and store all the code relating to our database.

Figure 1: The VBA Project Explorer
showing folders for Class Objects, Modules and
Class Modules.

The VBA project consists of three main elements: these are class objects, modules and class modules. These can be viewed in tree diagram format via the VBA Project Explorer (see Figure 1 right).

Class Objects
Class Objects contain code relating to elements in a  form or report.  We covered these in some detail in the Learning Access VBA series of posts. As you may remember, code from class modules is generally triggered when a form or report event is triggered (for example, the OnCurrent Event is triggered when the user moves into a record displayed on a form).

Modules, on the other hand are not associated with any particular form or report, but do contain variables, sub procedures and functions that can communicate with them in various ways.  They are a convenient place to keep code which can be read, written to, or called from the class objects.  Moreover, when declared Public, any given variable, sub procedure or function within a Module can be accessed from many different class objects (and sub procedures within class objects) elsewhere in the project.  As such, modules help us organise and manage our code, thereby making our VBA project more logical and efficient. 

Lets elaborate on this further.  Suppose we have a sub procedure called deleteRecord stored in a module.  It purpose is to delete a particular record from a particular table when called.  The advantage of locating this code in a module is so different forms in the database can also make use of this same code.  So rather than  re-write the same section of code in each form, we write it once in a module, and call it whenever one of the forms needs to use it.  As such, we not only write less code, and save time in so doing, but we can easily maintain and modify that code since it is located in one module. That is to say, if we want to change how the  deleteRecord command button behaves in all the sharing forms, we only need update our code in one central place.

Class Modules
So far we have said little about Class Modules, the third main element of a VBA Project.  These are actually worthy of a complete series of blog posts in their own right (something which I may well do in the future). For now, however, let's just say they offer the programmer a way of creating their own custom objects made up of properties, methods and events.  Like conventional modules, class modules also help the programmer organise and manage code, but in a way that is potentially more complex and powerful.

With the partitioning of class objects, modules and class modules, VBA Projects have the potential to become very large and complicated.  On the one hand these different project 'zones' need to be self contained so something happening in one class module does not interfere with something happening in another elsewhere; yet on the other, they also need to exchange information and communicate.  This is where the concept of Scope comes in.

The concept of scope relates to the way in which different zones of a VBA Project partition the processing of data that occurs within and between each of those sections. We are going to illustrate how this works in relation to variables and sub procedure/function calls. Lets start with how variables behave within a form's object class.

Figure 2:  A basic Class Object to illustrate Scope .

Variables and Scope
The screenshot in figure 2 shows a class object containing two sub procedures, Form_Load() and mySub().  A total of three variables are declared within the class object, two of which, - varB and varC  -are contained within sub procedures. VarA on the other hand is declared above both sub procedures in what is known as the General Declarations section.  The difference in positioning has a direct effect on the scope of each variable.  Let's examine this in more detail.

VarB is declared within the form's Form_Load() sub.  This means the scope of varB is local to this sub, and can only be accessed by code within it.  As such, we can can only read and write to this variable from within the same sub procedure in which it is declared. So, for example, if we assign a value to varB within the Form_Load() sub, we will not be able to read that value from code executed within mySub and vice versa.  This is what is meant by a local variable.

VarA on the other hand is written within the General Declarations section outside of any sub procedure.  The scope of any variable declared here is class object wide.  That is, it can be accessed from anywhere within it's own class object.  As such, we can assigned a value to varA in the Form_Load() sub, and then read that same value externally from mySub.  What we can't do, however, is access the variable from outside of this particular class object.  To do this we need to declare a Public Variable from a Module.

Public Variables
The scope of a public variable declared in a module is project wide - that is to say, it can be accessed from any class object, module or class module in the project.  Public variables are declared using the Public Statement (as opposed to the usual Dim Statement) in the module's General Declarations section (see Figure 3 below for an example).

Figure 3: The shows the Public Variable, varX, in the General Declarations section of Module1.
As you can see, the VBA Project in Figure 3 contains two class objects (Form_frmReadPublicVariable and Form_frmScopeTest) and a module (Module1).  Since the variable varX has been declared with the Public statement in the General Declarations section of Module1, it can be accessed from both class objects.  As such we can assign a value to varX in the Form_frmScopeTest class object, and then read that same value some time later from a sub procedure in Form_frmReadPublicVariable.  However, the disadvantage of using public variables is that they make code potentially more difficult to debug, especially in large projects where the variable might be accessed from sub procedures in many different class objects, modules and class modules.  To some extent we could say that public variables undermine the code management advantages from working in a partitioned VBA project.  That said, they can be a really useful programming tool if used carefully.

Scope and Persistence
Before we move on to look at sub procedure and function calls, it is important to say something about the nature of scope and persistence.  In the context of scope, persistence is how long a value assigned to a variable lasts or persists.  How long, that is, before the variable value is lost and forgotten.  So, for example, if we assigned a value to a local variable declared within a sub procedure using the Dim Statement, that value will persist only as long as the sub procedure is running(1).  Once the sub has finished the value assigned to the variable no longer persists.  Likewise if a variable is declared within the general declarations section of a form class object, any value assigned to the variable will only persist whilst that form is still open.  Once closed the value is lost. Of course it is public variables which have the greatest persistence.  Any value assigned to one of these will last until the database application is closed.

(1) There is an exception to this rule.  We can declare the variable using the Static Statement instead of Dim.  Doing so enables the value to persist, but only within the sub where it has been declared.  The next time that particular sub executes, the previous variable value is remembered and can be read by code (providing that code is still within the same sub).

Scope and Sub Procedure / Function Calls
If you read my post on Writing Custom Functions for Access VBA last week, you may already be familiar with the concept of calling a function.  You may recall how the myPercentage function located in an external module was called from a sub procedure within a form's class object.  You may also recall how we passed three parameters to that function.  The significance of being able to pass parameters in this way, is very important from the perspective of scope.  That is to say, if the calling sub procedure needs to share a local variable with the called function or sub, passing local data in the form of a parameter enables us to bypass the issue of scope.  As such, parameters are a way of letting various parts of the VBA project share information and communicate without compromising the real need to keep the project strictly partitioned in an organised manner.

Lets examine a simple example of how passing a parameter, this time in a sub procedure call, can bypass the issue of scope:

Private Sub Command9_Click()
    Dim varThis As Integer
    varThis = 10
    Call thatSub(varThis)
End Sub

Private Sub thatSub(argReceived As Integer)
    MsgBox ("The argument received is: " & argReceived)
End Sub

Here we have two private sub procedures within the a form's class object.  When the user clicks a command button, the Command9_Click sub procedure begins to run.  It defines a local variable called varThis and assigns it with the integer value 10.  The next line calls the thatSub sub procedure, passing the local variable varThis as the parameter.  This call results in the execution of thatSub.  Notice how the first line of thatSub contains the code argReceived As Integer within brackets.  This is where the passed parameter (varThis) is received by the called sub procedure.  The parameter which has been passed is now referred to as an argument, and it's value can be accessed from code within the receiving sub procedure.  As such, when the second line of code in thatSub runs, a message is displayed ending with the value contained in argReceived (see figure 4 below).  

Figure 4: The message displayed from the
 MsgBox method in thatSub.
We see, therefore, that the value contained in what is a local variable has been passed to a different sub procedure which would otherwise have been outside that variable's scope.

In this post we have seen how the VBA project is partition into various zones, and how those zones are largely self contained in terms of data processing.  This partitioning is maintained via a set of rules collectively known as scope, the advantage of which is the management of code within the project.  Since there is a legitimate requirement for these zones to exchange information, the working of scope can be bypassed in a managed manner through the passing of parameters and receipt of arguments.

Friday, 25 May 2012

Writing Custom Functions for Access VBA

VBA Custom Functions work in a similar way to any inbuilt MS Access Function.  Both types can be called within VBA sub-procedures1, and then return a value for the function's 'result'.  Take the inbuilt DLookUp function, for example.  This is written as follows:

varResult = DLookUp ( parameter1, parameter2, parameter3 )

Three things happen when we call this function:
  1. The function is called using VBA code (passing three parameters in the process), 
  2. The function looks up information stored in an access table (based on the parameters passed), 
  3. It then returns that information, storing the result in a variable called varResult.  
On account of DLookUp being a built in function, we do not get to see what happens in stage two.  Custom Functions are different in that we actually write a block of VBA code that executes at that stage.  Once we have set this up, we can call our custom function as often as we like.  Moreover, because custom functions tend to be located in a VBA global module, and declared as 'Public' in scope, they can be called from multiple points within our application. In this way, the creation of custom functions make our programming more organized and manageable.

Figure 1:  This screenshot of the VBA Project Explorer shows
 a module call modCustomFunction.  Providing the custom functions contained within are
declared 'public' they can be called from anywhere within the project  - such as
the form which I have called frmFunctionDemo.

How to Create a Custom Function
When we create a custom function we need to write the code that executes once it has been called from a sub procedure. As we shall see, this code is structured in a similar manner to an ordinary sub.  However, we also need to include a line of code which specifically returns a value back to the calling code. This is done by assigning a value to the name of the function (in a similar manner to assigning a value to a variable). For example:

Public Function myPercentage(argScore As Integer, argOutOf As Integer)
        myPercentage = (100 / argOutOf) * argScore 2
End Function

This creates a function called myPercentage.  The first line of code declares the function, and receives two arguments from the calling code ie argScore and argOutof.  The second line of code in this example returns the result of the myPercentage function.  There can any number of lines of code preceding the returning line.  However, I have provided a simple example which returns the result of the function in the same line of code as a percentage calculation.  This code is stored in a global module called modCustomFunctions (see Figure 1 above). 

Here is the code I have used to call this function:

Private Sub cmdCalculate_Click()
    Me!txtPercentageResult = myPercentage(Me!txtScore, Me!txtOutOf)
End Sub

This code executes when a form command button (cmdCalculate) is clicked by the user.  The function (myPercentage) is called in the second line of code.  As you can see, the calling code for myPercentage passes two parameters, the values of which reference two text box controls - txtScore and txtOutOf.  The function uses these parameters (which are received as the arguments argScore and argOutOf) to calculate the percentage and return the result.  The result is then displayed in a third text box called txtPercentageResult.

Figure 2: This is the form from which our function is called.  The CORRECT ANSWERS textbox is called txtScore, No OF QUESTIONS is called txtOutOf, and the PERCENTAGE textbox is called txtPercentageResult.  The CALCULATE button is called cmdCalculate

Figure 3: The top window shows the code calling the myPercentage function.  The lower window shows the code from the myPercentage function itself.   


1/ It needs to be pointed out here that the inbuilt functions of MS Access can be can be called from within Access itself (eg within the Query Design Grid as a criteria, or to populate a calculated control on a form etc), and not just from within VBA.  Custom functions, on the other hand, can only be called within VBA.