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.

Using Calculated Fields in Queries

Queries play an important part (in relational database design) in pulling information from different tables together. Consider the Many to Many relationship at the centre of an Order Management System.  Here we have three tables - Orders, Order Details and Products.  When we come to create the record source for the order items section of the invoice report,  it is necessary to get data regarding an order items quantity from the Order Details table, and that of product cost per unit from the Products Table. Then it is the OrderId from the Orders Table which groups all Order Details records into a single order.

Figure 1 (above):  A simplified example of a Many to Many Relationship from an Order Management Database.

Figure 2: A sample Invoice Report taken from one of my Order Management Database designs.
The Order Items Section is located in the middle of the report.  CostPerUnit is
displayed here as Unit Cost and Amount as Total.  This  section contains
details of each individual order item which, taken together,
makes up the whole order.

As well as bringing information together in this way, query's play another important role in as much as they are commonly used to calculate and process information from these tables.  This may be done by means of a Calculated Field.  This is basically a new query column, the values of which being derived from some calculation or expression.  As such, we are effectively creating new data for our database.  So why is this important?  Well, consider the Order Management Database Invoice mentioned earlier.  As already stated, the Order Details table contains data pertaining to an order items quantity, and the Products table to an items Cost Per Unit.  There is nowhere in a well designed ('normalised') database where we store the total of quantity*cost per unit. It is considered good practice to calculate this information (so we avoid storing unnecessary data which uses extra memory, and makes the database less efficient and user friendly).  The Calculated Field used in a query is a common way of doing this.

Creating a calculated field is really quite easy.  Rather selecting a table field on the top row of the query design grid, we manually type the name (or alias), followed by a colon, then followed by the calculation or expression.  So if we wanted to create a calculated field to process quantity * cost per unit, we would use this syntax:

Amount: [Quantity]*[CostPerUnit]

I should point out that the alias used here - ie Amount - is entirely arbitrary text chosen by the database developer. It is, however, good practice to make it something meaningful in order to make it easier for the developer to understand at a later point in time.

Let look at how this fits in with the rest of the query:

Figure 3: Query with Calculated Field on the right.

As you can see the query contains a mixture of fields from all three tables involved in the many to many relationship.  The Calculated Field has been added to the column on the right.  You may have noticed that the calculated field looks slightly more complicated than the syntax I wrote earlier in the post.  This is because we are also specifying the table name as well as the field name.  As such:

[tblOrderDetails].[Quantity] 

... refers to the Quantity field of tblOrderDetails, and:

 [tblProducts].[CostPerUnit] 

... refers to the CostPerUnit field of tblProducts.

It is essential to write the full reference in this way if any tables or sub-queries involved in the query contain duplicated field names.  Although this is not the case in this example, it does no harm to get into the habit of writing the full syntax anyway.

And this is the output from the query showing the result of the calculated field on the right:

Figure 4: The Query Output.

Since this query has an ID field corresponding to tblOrder.OrderId, it can now be used as the record source for the Order Items section of an Invoice Report or Orders Subform.  To do this the LINK MASTER FIELDS property of the subform is set to ID and the LINK CHILD FIELDS is set to OrderId.  There is then the matter of creating the record source for the main section of the invoice report or orders form (this also involves creating Calculated Fields to get the Order Totals).  Unfortunately this goes beyond the scope of this particular post.

Creating a Form for a Many to Many Relationship

I was recently asked how to create a form to input order management data where there is an underlying Many to Many Relationship in place.  There are, of course, many ways of dealing with this common scenario, but the particular solution I suggested involved creating an Order Form, with an Order Details subform.  When the user enters the Order Details data on the subform, he or she is able to select a Product Item from a Combo Box list. The Row Source used for the Combo Box list is data drawn from the Products table.  Hence we have all sides of the Many to Many Relationship represented on a single form.

Figure 1 (Above): The Many to Many Form; the solution I suggested
in answer to dcodding's question.

 Figure 2: The underlying Many to Many
Relationship Structure, upon which the form is based.

It is interesting to note where each of the fields from the Order Form is located in the underlying Many to Many Relationship structure.  So let me briefly take you through this.

The main section of the order form has three fields derived from tblOrder, the left hand table in figure 2 above.  These are the OrderId and OrderDate fields.  In addition to this we also have the CustomerId field which is the foreign key from tblCustomer (a table which is not part of the Many to Many Relationship, therefore not shown in Figure 2).

The relationship between the main section of the Order Form and it's subform is modelled on the One to Many Relationship between tblOrder and tblOrderDetails. However, because we have an underlying Many to Many Relationship, I have created a query for the Subforms' Record Source which has fields from both tblOrderDetails and tblProducts.  Moreover, the second ProductId field is actually a combo box which uses data from tblProducts as its Row Source.

Lets take a closer look at the subform's design and underlying query:

Figure 3 (Above): The Order Details Subform design grid.

Figure 4: The Query used for the Subform's Record Source.

As you can see, ProductId, ProductId, and Quantity are derived from tblOrderDetails, and CostPerUnit is derived from tblProducts.  ProductId is, of course the foreign key from tblProducts. There is also a Calculated Field (Total) which multiplies the CostPerUnit by Quantity to provide the actual purchase price.

So why then do we have two ProductId's?  The reason for this is that the second ProductId does not actually display the Id number per se. Since this field is represented by a Combo Box control on the subform, the data which is displayed can be different to that which it is bound to.  So in order to make the subform more user friendly, the control is set up to display the product's ItemName from tblProducts whilst being bound to the tblOrderDetails.ProductId field. This is done by setting the combo box Control Source property to ProductId,  and its Row Source property to tblProducts. As such, using the Combo Box in this way has enabled the subform to obtain information from the products table on the right hand side of the Many to Many Relationship. For detailed information on how to do work with Combo Boxes in this way, you might like to see my post on Customizing an Access Combo Box.

So what happens when the user comes to enter data in the subform? If  the user knows the productId number for the Order Item in question, he or she will enter that number in the first column of the subform.  Since this is the foreign key for the Products table, doing so will 'bring' any other subform field derived from tblProducts along with it.  In other words once the user enters a productId, data in the CostPerUnit field for that product record is displayed in the appropriate field of the subform.  In addition to this, the ItemName for the product is displayed in the Combo Box control, since its Row Source is bound to ItemName in tblProducts.  If, on the other hand, the user does not know the ProductId, he/she may leave the first field blank, and select a value from the Combo Box drop down list.  Since the Combo Box Control Source is bound to ProductId of tblOrderDetails, and its Row Source, as we know, is bound to ItemName of tblProducts, this is effectively the same as entering the ProductId number directly.  What's more, data from tblProducts is 'brought' over to the subform as before.  Hence, the first productId field of the subform is filled in automatically.

We see, therefore, that this relatively simple and user friendly form not only represents all tables in this Many to Many Relationship, but also provides a practical example of how the Many to Many Relationship works in action.

The Many to Many Relationship

Last month we learnt how to create a comparatively simple One to Many Relationship.  In this post we shall go one step further and create a Many to Many Relationship in the context of a Customer Orders database.

The Many to Many Relationship differs from the One to Many, in that the latter involves just two tables.  The table on the One side of the relationship potentially having multiple corresponding records in the table on the Many side.  It is depicted in the Access Relationships window like this:

Figure 1: The One to Many Relationship.

By contrast the Many to Many Relationship consists of three tables.  The two tables on the Many sides of the Relationship are joined to a third Junction Table connecting them together.  It is depicted in the Access Relationships window like this:

Figure 2: The Many to Many Relationship.

In the screen-shot above, we see that tblOrder and tblProducts are joined together in a Many to Many Relationship.  TblOrderDetails is the third Junction Table mediating between the two. This mediation takes the form of two ordinary One to Many Relationships, with the junction table set up to be on the Many side of both. Let us now examine the reason why this is so.  

When a customer makes an order, there may be a number of products purchased.  As such we have a One to Many scenario - One order potentially 'containing' Many products. However, one type of product may also appear in a number of different orders.  This too, is a one to many scenario, but this time going in the opposite direction. As such, we need the Junction Table in between to record each product appearing on a particular order, and each order where a particular product appears.

One more thing: you may notice that there seems to be two primary keys pictured in the Junction table of Figure 2 above.  These are also the foreign keys from the other two tables in the relationship. There is really only one primary key, but it consists of the unique combination of the two primary keys from the two tables on the Many sides of the relationship.  As such, one particular product type can only appear in any particular Customer Order once, and vice versa.  

To understand this more clearly, you may find it helpful to visualise the layout of an invoice.  Each individual invoice pertains to a particular order for a particular customer.  The invoice includes a list of all the different products purchased for that order.  The same product types may also appear on different invoices (for the same or different customers).  The data from these lists are derived from the Junction table.
   

Creating a Many to Many Relationship


Lets have a go at creating a Many to Many Relationship ourselves.  We will begin by downloading a database containing tblOrder and tblProducts .  There is also another table called tblCustomer which is joined to tblOrder in a One to Many Relationship.  We will then create the tblOrderDetails Junction Table and the Joins which make up the Many to Many Relationship.

Setting up the Exercise

1. Download or create the tables to be used in this exercise - ManyToMany_Exercise Tables.
2. Open the database and select the DATABASE TOOLS ribbon.  
3. Click the RELATIONSHIPS icon in the SHOW/HIDE group.  Here are the tables we are starting off with.
   
   

   

   Figure 3: Start of Many to Many Exercise

   
   

Create Junction Table

1. Select the CREATE tab on the Access Ribbon.
2. Click the TABLE DESIGN icon from the TABLES group.
3. Enter the first two fields in the TABLE DESIGN GRID.  This is OrderId and ProductId.  Select NUMBER as the DATA TYPE for both fields.
4. We now need to designate both these fields as the joint primary key.  Select both rows by holding down the CONTROL key on your keyboard and clicking the two blue sections on the far left of the Grid.  Both rows are then highlighted.  You can now click the PRIMARY KEY icon in the TOOLS group of the DESIGN ribbon.  Two primary key symbols appear now on the far left of the two rows.
   
   

   

   Figure 4: Selecting the Primary Key on two fields.

   
   
5. Add the remaining two fields to the Grid.  These are Quantity (DATA TYPE: NUMBER) and CostPerUnitPaid (DATA TYPE: CURRENCY).
6. Close the table, saving it as tblOrderDetails when prompted.

Creating the Joins

1. Select the DATABASE TOOLS tab on the Access Ribbon.
2. Click the RELATIONSHIPS ICON in the SHOW/HIDE group.
3. The Junction Table we just created cannot be seen at first.  To make it visible, click the SHOW TABLE icon in the RELATIONSHIPS group of the Access Ribbon.  Double click tblOrderDetails from the SHOW TABLE dialogue box to add it to the RELATIONSHIPS window.  Close the SHOW TABLE dialogue box.
4. Click and drag the new table to a location between tblOrder and tblProducts.
5. We are now going to create a One to Many Relationship between the ID field of tblOrder, and the OrderId field of tblOrderDetails.  Do this by clicking  the tblOrder.ID field, and dragging over to tblOrders.orderId.  When you release the mouse key the EDIT RELATIONSHIPS dialogue box opens.  Click the three tick boxes and click the CREATE button to create the relationship.
   
   

   

   Figure 5: The Edit Relationship Dialogue Box.

   
   
6. Next we need to create a One to Many Relationship between the tblProducts.ID field and tblOrderDetails.productId.  Follow the same procedure as above to create this relationship.

The Many to Many Relationship is now in place.  Your RELATIONSHIPS window should now look like this:

Figure 6: The Many to Many Relationship.

Lets end this post by taking a brief look at the Many to Many relationship in action.  Obviously we have not created any tables or reports to enter or display information in a user friendly manner (this is something I hope to cover in a future post).  We can, however, see how all this information is connected together using the example of a single order.

The screen-shot below shows an order which I created for one of the customers in the customer table.

Figure 7: Three nested tables showing an order made by customer Tracey Smith.

As you can see there are three nested tables. The top level table shows the customer record from tblCustomer.  The name of the customer here is Tracey Smith and her customerId is 2.  The mid-level table shows the order record from tblOrder. There is just one order on the database for this customer.  The order ID is 18, and the orderDate is 28/4/2011.  The inner table shows the order details from tblOrderDetails. As you can see there are four product items for this order.  The product ID's are 3,2,1, & 5. The customer ordered one of each item (see the quantity field).

In the next screen-shot (figure 8) we see the products table. Each row is expanded to show a set of records from tblOrderDetails corresponding to them.  As such, we see the orders within which each product appears:

Figure 8: The Products Table (tblProducts) showing the order details (tblOrderDetails) for each item.

As you can see, Order 18 (which we looked at above from the Customer Order side of the Many to Many Relationship) is seen here from the Product side, along with all other orders.  If you look carefully you will see that an instance of OrderId 18 appears under each productId (ie 3,2,1, & 5) which we mentioned above.  As such we see how the data contained in our tables appears from both sides of the Many to Many Relationship.

Of course, this is a somewhat fragmentary perspective from which to view our data.  That is why we need  forms, queries, and reports to enter, process and display all this information in a user friendly manner.  This is something that I hope to cover in a future post.