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-procedures, 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.   

Your Feedback?

I have been writing the MS Access Tips blog around three years, and it is now being read by hundreds of people each day. I am very interested to learn more about who is reading my blog, and and what people think about it.  I would be really grateful if you could take the time to answer 6 simple question in a survey that I have set up for this purpose.  This will help me to continue writing articles that are relevant and interesting to you, the readers.

Please click the link below to begin the survey.  Many thanks.

Survey for the MS Access Tips Blog

Using the Make Table Query to Collect Archive Data

Information stored in a database is likely to change over time.  When details change, data may be overwritten, and once this happens it is lost.  There are certain scenario's where it is important to have access to 'historic' data, by which I mean a snapshot of information which was stored in the database at a given point sometime in the past.  Let's take an Order Management System for example.  Suppose you need to find a customer contact name from two years previous?  If the customer in question happened to be an organisation or business, it is possible that there was a different person in post at that time.  In this case, it would be useful to have some facility to access data from that point in time.  Whilst we can't create this sort of archive from the present backwards,  we could have taken a snapshot of this data at periodic intervals previously.  One way of doing this was to have created a Make Table Query to extract relevant data from the database with a query and store the results in a new table.  These can then be saved in a separate archive database ready to be accessed if and when needed.

Taking the Order Management System scenario, there is data stored in four related tables, and only certain  fields from each table need to be archived.  We could simply make a copy of each of the four tables, but this would result in the storage of unnecessary information.  It would also be less convenient to access at a later point in time.

The related tables in question are tblCustomer, tblOrganisation, tblPerson and tblAddress.

Figure 1: The Make Table Query for an Order Management System.

Creating a Make Table Query

Before we create the Make Table Query, you might like to create a new blank database file.  This is so we have a separate database into which we can paste the new table from the query.  In this exercise I have called it HistoricData.accdb.

Here is the procedure to create a Make Table Query:

1. Open the database which we are going to query (In my case this is the Order Management System).
2. Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon).
3. Select the tables to be used from the SHOW TABLE dialog form. I have selected  tblCustomer, tblOrganisation, tblPerson and tblAddress.
4. Next select all the fields from the existing tables to be used in the query.  These not only form the query results, but also the structure for the new table.  I have chosen fields from all four tables so that the new table will be self sufficient. 
5. Amend the table relationships (or Joins) as required.
6. Click the MAKE TABLE icon (located in the QUERY TYPE group of the DESIGN ribbon).  This opens the MAKE TABLE dialog form (see Figure 2 below).

   

   Figure 2: The Make Table Dialog Form.

7. Enter the name of the new table that we are going to create.  I have called mine tblCustomerTeamsMay2012.
8. Click the option button for ANOTHER DATABASE. This ensures the new table will be created in a different database.
9. Browse to the location of the external database.  I have called mine HistoricData.accdb.  Double click the file to select and then click OK to close the dialog form.
10. Test the Query by clicking the DATASHEET VIEW icon (located in the RESULTS GROUP of the DESIGN ribbon).
11. If you are happy with the data displayed, you can run the Make Table Query by clicking the RUN icon (located in the RESULTS group of the DESIGN ribbon).  This will now create the new table in the external database.

You can now open the external database and view the newly created table:

Figure 4: The newly created tblCustomerTeamsMay2012 table (Design View).

Now all the database administrator has to do is run this query every month, changing the new table to the appropriate month.  Over time, we collect a 'warehouse' of data ready to be accessed if and when needed.  Since this data is stored in a separate database, it does not impact upon the performance of the actual system from which the information was collected.

Using the Access Database Splitter

Last year I wrote a post on Linking to an External Data Source.  This is where we split an Access database  into a Back End (which stores all the tables and data), and a Front End (which contains all the forms, queries and reports).  There are a number of advantages to observing this practice.  To begin with, splitting a database file in this way is all but essential if it is to be deployed on a network in a multi-user environment. Another big advantage is that we can easily replace a front end file with a new updated version with minimal disruption - the actual data stored in the back end tables is not touched in this process. 

If you read the my post last year, you may remember that I showed you how to link a front end Access file to an existing back end database.  This is fine if the back end database already exists, or if you decide to create the application in two separate files from the start.  However, many developers create or inherit a complete database application as one stand alone file, and then proceed to split the database.  Splitting the database can be done manually, but this is often time consuming and there is always the possibility of making errors in the process.  An easier and quicker way of doing this is by using the Access Database Splitter.  This is a wizard that splits the database into a front and back end, as described above.  

Splitting an Access Database

The database that I am working with here is an Order Management System containing a number of tables.  If you want to try this on one of your own database files, it does not matter how many tables it contains - it could have a hundred or it could just have one.  

Figure 1

1. Click the ACCESS DATABASE icon (located in the MOVE DATA group of the DATABASE TOOLS ribbon).  See figure 1 on on the right.  This opens the DATABASE SPLITTER Wizard.
2. Click the SPLIT DATABASE button at the bottom of the Wizard (see figure 2).   
3. You are now asked to select one of your folders for the new back end Access file.  Browse to a location of your choice, enter a back end file name, and then click SPLIT.  After a moment or two, you should get a message box saying "Database Successfully Split".
4. Click OK.

Figure 2: The Database Splitter Wizard from Stage 2.

You can now look at the navigation pane of the front end database and see the linked tables (see figure 3).  You can try adding data to one of your tables and then open the back end file to see it stored there.  You could also try copying the front end file in order to access the back end tables with different front ends.  In so doing you can see the basic principle behind how multi-user systems operate*.

Figure 3 (above): This is how linked tables appear in the Front End Database file.

Figure 4:  Your Database is now split into two files one for the front end and one for the back end.

*Tip:  If you want to run more that one Access front end at a time, you will need to open multiple instances of the Access Database.  So rather than clicking on the access front end file directly (ie from within Windows Explorer), you should open Microsoft Office Access from the windows START button or DESK TOP and then open each front end file from the GETTING STARTED WITH MICROSOFT OFFICE ACCESS screen.