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.