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.

Figure 6: FINISH AND MERGE.

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.

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

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.

Scope
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⁽¹⁾.  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.

Conclusion
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.

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.   

Hiding the Navigation Pane with VBA

Once you have created an Access Database Application to be used by other people, it may well be important for you to prevent users from gaining access to any of its design features.  By this I mean, you may not want users to modify your tables, forms and queries etc in design view (inadvertently or otherwise).  There are a number of measures you need to take in order to secure your database against this possibility.  One of these involves preventing users from accessing the NAVIGATION PANE, a major doorway to all of your database objects.  Hiding the NAVIGATION PANE can be done with a couple of lines of VBA Code.

To use this code, you will need one of your database forms' to open automatically as your application opens. There are instructions to do this in my post on How to Display a Form Automatically. Once this is in place you can insert the following code into that form's ON LOAD event.

    DoCmd.SelectObject acTable, "tblCustomer", True
    DoCmd.RunCommand acCmdWindowHide 

The purpose of the first line of code is to select any object in the navigation pane.  We have selected one of the database tables called tblCustomer, and then set the INNAVIGATIONPANE parameter to true.  Once  a database object from the navigation pane has been selected, we can then use the DoCmd.RunCommand with the acCmdWindowHide parameter in the following line of code.  This has the effect of hiding whichever window happens to contain the selected object. 

The user can quite easily get the Navigation Pane back by pressing the F11 Access Special Key (assuming  that he or she knows about it), but this facility can be disabled.  To do this you will need to deselect the USER ACCESS SPECIAL KEYS check box in the CURRENT DATABASE section of ACCESS OPTIONS, (via the OFFICE button).

Hiding the Navigation Pane alone is not sufficient to secure a database, but it is one important part of doing so.

Calculating a Future Date with the DateAdd Function

If you read my blog last week you may have seen the sample Lending Library Database that I created to demonstrate a working example of the DAO Recordset.  You may have noticed that I used a function called DateAdd to calculate the future date that a library book is due for return.  As you may remember, this date was automatically entered as the value for the DueDate field in the tblLoan junction table via VBA code.

I thought it would be interesting to take a closer look at how the DateAdd function works. However, please  do bear in mind that the DateAdd funciton can also be used in Queries and Calculated Form Controls (not just in VBA code).  This is how the syntax for DateAdd is constructed:

DateAdd("interval", number, date)

Figure 1 (above): This shows the DateAdd function used
as a Calculated Field in a Query.  You can see an example of a Calculated Field
in my post on the related DateDiff Function. (NB DateDiff calculates and returns the difference between 
two dates, whereas DateAdd calculates and returns a new date based on the value of the number parameter passed).

Figure 2: This shows the DateAdd function used as a Calculated Control in a form.  It can be entered
into the CONTROL SOURCE property or directly into the text box on the  Form Design Grid.

DateAdd Parameters

As you can see, the DateAdd function has three parameters - interval, number and date.  

Interval

The interval parameter determines whether we are adding intervals of days, weeks, or months etc.  The parameter is entered as a string value; days are entered as "d", weeks as "ww" and months as "m".  There are 10 possible parameter values to choose from, ranging from years "yyyy" right down to hours "h", minuets "n", and seconds "s".  

Number

The number parameter is the number of intervals to be added.  So if we want to calculate a date 30 days in the future, we would set the interval to "d" and the number to 30.

Date

The date parameter is the base date we are applying the DateAdd function to.  As such, if we want to calculate a date 30 days from 1/4/2012, we would simply enter 1/4/2012 as the date parameter value. If you want to calculate a date from today (whatever date that may be on the day you are reading this) you can enter the Date() function as the actual parameter value.  This will return the current date when the database is being run.

Calculating a Library Book Due Date

This is the syntax I used in the last weeks lending library database to calculate a DueDate 30 days from when a book is issued:

DateAdd ("d", 30, Date())

Figure 3: This shows the DateAdd function used in the fourth line of this VBA code snippet.
The rstLoan object is a DAO Recordset, and DueDate is the field name in the tblLoans Table.
You can see the rest of the code in last weeks blog post on the DAO Recordset.

As you can see, the interval used is days, the number parameter is 30, and I have used the Date() function to return the current date for the date parameter.

The DAO Recordset in Action - An Example Lending Library Database

Last week I wrote a post on How to Access Data with VBA Code.  Instrumental in this was the use of the DAO Recordset Object.  Through using this object we were able to add, find, edit and delete records from an Access Database table or query. This week I am going to provide a working example of how the DAO Recordset is used in an actual database application.  In order to do this, I have created part of a Lending Library Database for demonstration purposes. This can downloaded by clicking the link.

The Lending Library Database

The table at the centre of this database is the Loans table (jnkLoan).    As you can see from figure 1, it is a junction table (from a Many to Many Relationship) which contains the foreign key from both the Library Users table (tblLibraryUser) and the Library Stock Table (tblLibraryStock).  The purpose of tblLoans is to store the details of each loan transaction whereby a library user borrows or returns an item of library stock.  

Figure 1: The table relationships from the Lending Library Database. 

Figure 2 below shows the Library User Account Form where these loan transactions are made.  Transactions are occur when the user enters a number into the ITEM BARCODE text box, and clicks the ISSUE or DISCHARGE command button.  This then runs a VBA sub procedure which uses the DAO Recordset to create a loan record in tblLoan.

Figure 2: The User Account form. Library items are issued and discharged from here.

The User Account Form also has an On Loan subform to list items out on loan to the library user.  The RECORD SOURCE for this subform is qryLoansSub, and contains fields from the jnkLoan, tblLibraryStock, and tblBook tables.  There is a criteria value of Is Null on the ReturnDate field.  As such, any loan transaction without a ReturnDate (belonging to the UserBarcode from the parent form) is listed as being On Loan.  Moreoever, the VBA code initiated by the ISSUE and DISCHARGE buttons ends with a line to requery the subform.  This ensures the subform updates as soon as a stock item is issued or discharged.

Issuing an Item

So lets have a look at the VBA code which runs when the ISSUE button is clicked:

Private Sub cmdIssue_Click()
On Error GoTo trapError
    Dim rstLoan As DAO.Recordset
    If IsNull(DLookup("StockBarcode", "tblLibraryStock", "StockBarcode = " & Me!txtIssueBarcode)) = True Then
        MsgBox "Sorry - Barcode Does Not Exist", vbInformation, "Invalid Barcode"
        GoTo leave
    Else
        Set rstLoan = CurrentDb.OpenRecordset("jnkLoan", dbOpenDynaset)
        rstLoan.FindLast "StockBarcodeFK = " & Me!txtIssueBarcode
        If rstLoan.NoMatch = False Then
            If IsNull(rstLoan!returndate) = True Then
                MsgBox "Book Is Already On Loan.  Please discharge before Issuing.", vbExclamation, "Already On Loan"
                GoTo leave
            End If
        End If
        rstLoan.AddNew
            rstLoan!StockBarcodeFK = Me!txtIssueBarcode
            rstLoan!UserBarcodeFK = Me!UserBarcode
            rstLoan!IssueDate = Date
            rstLoan!DueDate = DateAdd("d", 30, Date)
        rstLoan.Update
        Me!subOnLoan.Requery
        Me!txtIssueBarcode = Null
        DoCmd.GoToControl "txtissuebarcode"
    End If
leave:
    If Not rstLoan Is Nothing Then
        rstLoan.Close: Set rstLoan = Nothing
    End If
    Exit Sub
trapError:
    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave
End Sub

The code begins by checking that the item barcode actually exists, and is not, therefore, a data entry error. This is done using the DLookUp function on the StockBarcode field of tblLibaryStock.  If it does not exist, a warning message is displayed, and the sub procedure comes to an end.  If it does exist, the code continues to open a DAO Recordset object based on jnkLoan.  This is then searched using the FINDLAST method of the recordset object to find the most recent transaction involving that particular item.  If a record is found, the ReturnDate value is checked to make sure the item is not recorded as already being on loan (it is an not an uncommon human error for some items to be shelved without first being discharged).  If the item is recorded as being On Loan  an error message is displayed asking the staff member to discharge the book before re-issuing.  The procedure then comes to an end.  If there had not been a previous transaction for that item, or if the previous transaction has a ReturnDate value, the code continues to create a new record in jnkLoan.  

The following block of code (a section extracted from the main code above) is where the new record is created:

 rstLoan.AddNew
            rstLoan!StockBarcodeFK = Me!txtIssueBarcode
            rstLoan!UserBarcodeFK = Me!UserBarcode
            rstLoan!IssueDate = Date
            rstLoan!DueDate = DateAdd("d", 30, Date)
  rstLoan.Update

Here the AddNew method of the Recordset Object is called to create the new record.  Then the StockBarcodeFK field is set to the value of the Item Barcode textbox; the UserBarcodeFK field is set to the value of the UserBarcode on the User Account Form; the IssueDate field is set to the current date via the Date function; and the DueDate field is set to 30 days from the current date via the DateAdd function.  The changes made to the rstLoan Recordset are then saved back to tblLoan via the recordset object's Update method. Finally the code re-queries the OnLoan subform, puts the focus back on the Item Barcode textbox, and closes the recordset.

Discharging an Item

The sub procedure which runs when a book is discharged is very similar.  The main difference is that rather than creating a new loan transaction record, the last loan transaction record is found, and then edited by putting the current date in the ReturnDate field.   Here is the discharge code:

Private Sub cmdDischarge_Click()

On Error GoTo trapError

    Dim rstLoan As DAO.Recordset

    If IsNull(DLookup("StockBarcode", "tblLibraryStock", "StockBarcode = " & Me!txtIssueBarcode)) = True Then

        MsgBox "Sorry - Barcode Does Not Exist", vbInformation, "Invalid Barcode"

        GoTo leave

    Else

        Set rstLoan = CurrentDb.OpenRecordset("jnkLoan", dbOpenDynaset)

        rstLoan.FindLast "StockBarcodeFK = " & Me!txtIssueBarcode

        If rstLoan.NoMatch = False Then

            If IsNull(rstLoan!returndate) = False Then

                MsgBox "Book Not On Loan.", vbExclamation, "Already On Loan"

                GoTo leave

            End If

        End If

        rstLoan.Edit

            rstLoan!returndate = Date

        rstLoan.Update

        Me!subOnLoan.Requery

        Me!txtIssueBarcode = Null

        DoCmd.GoToControl "txtissuebarcode"

    End If

leave:

    If Not rstLoan Is Nothing Then

        rstLoan.Close: Set rstLoan = Nothing

    End If

    Exit Sub

trapError:

    MsgBox "Error " & Err.Number & ": " & Error$

    Resume leave

End Sub

If you would like to try all this out, please feel free to download the sample Lending Library Database. You can view a list of library item barcodes to work with by clicking the LIBRARY STOCK command button at the top of the User Account Form.

Accessing Data with VBA Code - Introducing the DAO Recordset

This post is all about how we can use VBA code to access the data stored in our database.  By access, I mean, being able to add, find, edit or delete records.  In so doing we are effectively automating data access thereby enabling us to create some very powerful database applications.

Creating a Reference

To do this we are going to make use of Data Access Objects (DAO) which is contained in the Microsoft Office 12 Access database engine Object Library.  The very first thing we must do is create a reference to this library if it does not already exist.  The procedure for doing this is really quite simple:

1. Open the VBA Editor.
2. Open the TOOLS menu.
3. Select REFERENCES from the TOOLS menu.
4. When the REFERENCES dialog form opens, check to see whether Microsoft Office 12 Access database engine Object Library has a tick against it.  If it has it should be towards the top of the list (all checked references appear above the others, and those that are not, appear in alphabetical order below).
5. If the library mentioned in stage 4 is not ticked, you will need to scroll down the list to find it, and then tick the check box.
6. Click OK to close the dialog form.

The reference has been created and you are now ready to start using Data Access Objects.

Introducing the DAO Recordset

The DAO Recordset is basically a VBA object which enables us to gain access to information stored in database tables or queries via code.  It is VBA's representation of the table or query, and we make use of the methods and properties of the DAO Recordset to manipulate the data that it contains.

The first coding task we have to perform is to create an instance of the recordset object.  This is then stored as an object variable. We do this with the following line of code:

Dim rstNameList as DAO.Recordset

This creates an object variable called rstNameList.    Our the next task is to set the value of  this object variable. This effectively means that we are setting  rstNameList to represent a particular table or query in our database.  This is done as follows:

Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

This line of code sets the value of object variable rstNameList to a DAO Recordset based on a table called tblNames.  This is done by using the OpenRecordset method of the Recordset Object.  This is applied against an object reference to the current database - hence our use of the CurrentDb method of the Application object preceding OpenRecordset.  Don't worry if this sounds confusing, just remember to enter the name of your table or query as the first parameter of the OpenRecordset method.  The second parameter, dbOpenDynaset simply opens the recordset as a Dynaset type (which is a particularly versatile type).  

We can now apply the methods and properties of the Recordset class against the object variable rstNameList. As we do so, remember that they are acting on a virtual representation of the database table or query being used.  Lets look at how we can add a record to our table via rstNameList:

Adding Records

Imagine our table is comprised of four fields: Id, FirstName, Surname, and DOB (ie date of birth).  This is the data our table currently holds:

Figure 1: Table data from tblNames.
The rstNameList Recordset Object is based on this table.

The following code is designed to add a new record for a person called Graham Bell:

Private Sub cmdAdd_Click()    

    Dim rstNameList As DAO.Recordset

    Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)

    

    rstNameList.AddNew

        rstNameList!FirstName = "Graham"

        rstNameList!Surname = "Bell"

        rstNameList!DOB = #7/10/1970#

    rstNameList.Update

    

    If Not rstNameList Is Nothing Then

        rstNameList.Close: Set rstNameList = Nothing

    End If   

End Sub 

Lets go through this:

This code is triggered when a command button called cmdAdd is clicked by the user.  The code then declares an object variable called rstNameList which is subsequently set to a Recordset based on tblNames.  

The central block of code calls the AddNew method of the Recordset object.  This basically signals that a new record is going to be added to the recordset.  The next three lines reference the FirstName, Surname and DOB fields, setting their values respectively.  As such the FirstName field value is set to "Graham", Surname to "Bell", and DOB to 7/10/1970.  Note the FirstName and Surname fields are of the STRING data type, so are consequently enclosed within quotation marks ie ""; whereas the DOB field is of the DATE data type so is enclosed between hash marks using the American Date Format ie #mm/dd/yyyy#. (Had we used the number data type this would not have needed to be enclosed by anything). Finally the central block of code saves the new record by calling the Update method of the Recordset object.

The final block of code simply closes the recordset object and destroys the object variable reference so as not to use unnecessary memory.

You can now go back to the original table tblNames, and click Refresh to see the new record:

Figure 2: The tblNames table after new record is added via VBA Code.

As you can see, the new record has been added at the bottom of the table.  Note that we did not need to set the value of the ID field as this is of the AUTONUMBER data type and is added by Access automatically.

Editing Records

The code for updating a particular record is very similar to the central block of code in the in the previous example.  The main difference is, however, that we must find the record prior to making any changes.  To do this we are going to use the FindFirst method of the Recordset object.  The syntax for this is as follows:

 rstNameList.FindFirst "Id = 5"

Notice that this method passes the parameter "Id = 5".  This is basically an SQL WHERE clause without the WHERE keyword.  It tells access to find the first record in the recordset where the value of the ID field is 5.  There is more information on SQL Where in my Gentle Introduction to Access SQL. Just remember that the parameter must be enclosed within quotation marks, so if the value searched for is itself a string, you must not only enclose the parameter within quotation marks ie "", but you must also enclose the actual value to be found within single quotation marks ie ' '. For example if you wanted to search for the surname Lang, the syntax would be:

rstNameList.FindFirst "Surname = 'Lang' " 

It is also possible to test whether a record has actually been found using the NoMatch property of the Recordset object as follows:

If rstNameList.NoMatch = false Then

    .... code to run if record is found. 

Else

    ....code to run if no record is found.  

End if

Once we have found the record, we can then go on to edit any, or all, of its fields.  This is done with the following code:

rstNameList.Edit

        rstNameList!DOB = #8/10/1970#

rstNameList.Update

Notice how we have used the Edit method of the Recordset object instead of AddNew.  The rule is use Edit if you want to edit an existing record, and use AddNew if you want to create a new record.  Once the record has been edited it needs to be saved using the Update method as before.  So lets look at the whole code snippet:

Dim rstNameList As DAO.Recordset
Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)
    
    rstNameList.FindFirst "Id=5"
    If rstNameList.NoMatch = False Then
        rstNameList.Edit
            rstNameList!dob = #8/10/1970#
        rstNameList.Update
    End If
    
If Not rstNameList Is Nothing Then
    rstNameList.Close: Set rstNameList = Nothing
End If

If we refresh the data in tblNames, you can see that our code has put a DOB value in Sally Lang's record:

Figure 3: The record of Sally Lang (ID 5) now has a DOB value
after her record was edited with our code.

Deleting Records

When it comes to deleting a record, we start by finding the particular record that we want to delete.  Again this is achieved using the FindFirst method of the Recordset Object.  So suppose we wanted to delete Graham Bell's record, we could do a FindFirst search on his record ID which is 8.

rstNameList.FindFirst "Id = 8" 

All we do then is apply the Delete method of the Recordset Object as follows:

rstNameList.Delete 

Again, it is a good idea to nest the above line in an If ...Then... Else statement to test that a matching record has been found.  As such the full code to delete record 8 is as follows:

Dim rstNameList As DAO.Recordset
Set rstNameList = Application.CurrentDb.OpenRecordset("tblNames", dbOpenDynaset)
    
rstNameList.FindFirst "Id=8"
If rstNameList.NoMatch = False Then
    rstNameList.Delete
Else
    MsgBox "Record Not Found"
End If
    
If Not rstNameList Is Nothing Then
    rstNameList.Close: Set rstNameList = Nothing
End If


The record has now been removed from the table:

Figure 4: Our code has deleted the intended record.

These have all been basic examples to demonstrate how we can access and manipulate data using VBA code.  How we use the Recordset object in a real database application depends entirely on what needs doing. I have found the possible uses of the DAO Recordset to be virtually endless. It is an extremely   powerful tool to draw upon when needed.  As such, I hope to show some applied examples of its possible uses in a future post.