Class Modules in Microsoft Access
(aka.emulating Object-Oriented programming)

Editing and deleting objects

Introduction

This example shows how to extend our clsOrder object with methods that allow you to Edit and Delete the database records.

Instructions

1. Create a new class module clsOrder1 with this code (clsOrder1.txt).

2. Experiment with this new object in the Debug Window as follows:

   ' Create an new instance of object in memory
   Set oOrd = New clsOrder1
   
   ' Add properties to "empty" object
   oOrd.CustomerID = 1
   oOrd.Item = "Widget"
   oOrd.Qty = 200
   oOrd.UnitPrice = 5
   
   ' Save to database
   ? oOrd.Create
   True
   
   ' Check new ID
   ? oOrd.OrderID
    3
   
   ' Confirm then change value of Qty in object
   ? oOrd.Qty
    200 
   oOrd.Qty = 1000
   ? oOrd.Update
   True
   
   ' Load data from database again and check change has been done
   ? oOrd.OrderID
    3 
   ? oOrd.Load(3)
   True
   ? oOrd.Qty
    1000 
   
   ' Now delete the object from database
   ? oOrd.Delete
   True
   
   ' And now try and load it again
   ? oOrd.Load(3)
   False
   

   If you want to do this in a test code snippet instead, don't forget to change the "?" to "Debug.Print" (but if you've got this far, you'll know that already).

Things to note about the code

• This time we've made use of the Nz function when reading data from the database in the Load function:

      Me.Item = Nz(!Item)
  

  This copes with the problem (as you'll probably have already found) of trying to put a Null value from the database into one of your object's variables. You could get over this by declaring all your object class properties as type Variant, but this defeats the point of having typed values at all.

• The Update method limits the size of the recordset it works on by using a query. This way also provides a quick check - using the RecordCount property - if the particular record isn't there.

      sQry = "SELECT * FROM " & scTABLE & _
      	" WHERE ([OrderID]=" & Me.OrderID & ");"
      Set rs = CurrentDb().OpenRecordset(sQry, dbOpenDynaset)
      With rs
          If .RecordCount = 0 Then
              .Close
              Err.Raise 40000, , "Cannot find OrderID=" & Me.OrderID
              GoTo Done
          End If
          
          .Edit
          SaveFieldsToDb rs
          .Update
          
          .Close
      End With
  

• If we use a Private procedure in a class module, like the SaveFieldsToDb subroutine, it can be used within the class module just like any other local procedure, but you cannot use it externally. Try this:

      Set oOrd as New clsOrder1
      oOrd.SaveFieldsToDb
  

  Declaring the procedure Private means just that: whatever it does is private to the module and cannot be accidentally used by an "outsider".

• The Delete method just uses a simple delete query. Note the use of the dbFailOnError parameter to make sure any errors are trapped.

      sQry = "DELETE * FROM " & scTABLE & _
      	" WHERE ([OrderID]=" & Me.OrderID & ");"
      CurrentDb().Execute sQry, dbFailOnError
  

• Even though this code might look a bit complicated, once you've written it once, it can easily be copied to other objects and changed with just one global edit.