Class Modules – Unbound Forms iii – CRUD

In this post we will be giving you the code you need to Create, Update and Delete records through a Class Module.

Encapsulation

One of the best reasons to use a class module is because of a concept known as encapsulation. Encapsulation means that all the code you need to generate an object is held within the class. In other words, a form that uses the clsFilms class will only need to reference the properties and methods of said class and not need to know anything else about clsFilms. The Film data is going to be retrieved and stored somewhere but the form will not need to know where; the form only knows about the public methods of the class and frankly that is all it needs to know.

Therefore, the code that takes care of creating, updating and deleting records in the Films table will reside in methods within the class. Let’s take a look at what extra code we need to CRUD up our class.

The Code

Private rs As ADODB.Recordset
Private cnn As ADODB.Connection
Private strSQL As String

''''''''''''''''''''''''''''''''''''''''''''
'''''Initilisation and kill code''''''''''''
''''''''''''''''''''''''''''''''''''''''''''
Private Sub Class_Initialize()
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
End Sub

Private Sub Class_Terminate()
Call killRecordset
End Sub

Public Sub loadData(ID As Long)
'This sub procedure loads the data based upon the
'passed in ID value -1 means new record

If ID > 0 Then
    strSQL = "Select * From Films Where [ID]=" & ID
    Call loadRecordset(strSQL)
    Call setFields
Else
    m_ID = -1
    strSQL = "Select * From Films "
    Call loadRecordset(strSQL)
End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''Recordset code''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''

Private Sub loadRecordset(strSQL As String)
With rs
        .Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        .MoveLast
        .MoveFirst
    End With
End Sub

Private Sub killRecordset()
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

Public Sub setFields()
With rs
    m_ID = .Fields("ID")
    m_FilmName = .Fields("FilmName")
    m_YearOfRelease = .Fields("YearOfRelease")
    m_RottenTomato = .Fields("RottenTomatoes")
    m_Director = .Fields("DirectorID")
End With
End Sub

''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''Record Operations'''''''''''''
''''''''''''''''''''''''''''''''''''''''''''

Public Function SaveRecord() As Boolean
With rs
    If m_ID > 0 Then

        .Fields("FilmName") = m_FilmName
        .Fields("YearOfRelease") = m_YearOfRelease
        .Fields("RottenTomatoes") = m_RottenTomato
        .Fields("DirectorID") = m_Director
        .Update
    Else
        .AddNew
        .Fields("FilmName") = m_FilmName
        .Fields("YearOfRelease") = m_YearOfRelease
        .Fields("RottenTomatoes") = m_RottenTomato
        .Fields("DirectorID") = m_Director
        .Update
    End If
End With
End Function

Public Function UndoRecord() As Boolean
With rs
    m_ID = .Fields("ID")
    m_FilmName = .Fields("FilmName")
    m_YearOfRelease = .Fields("YearOfRelease")
    m_RottenTomato = .Fields("RottenTomatoes")
    m_Director = .Fields("DirectorID")
End With
End Function

Public Function DeleteRecord() As Boolean
Dim lngID As Long

lngID = m_ID
'We are going to use a simple query to delete the record
Call killRecordset

strSQL = "DELETE FROM Films WHERE [ID]=" & lngID
CurrentDb.Execute strSQL, dbFailOnError

End Function

OK, so you weren’t expecting quite as much code as that. Well, class module coding is quite verbose and involves a much higher initial time investment than standard coding but don’t leave us just yet! We promise that it will be worth every effort and will bring long term rewards.

The Explanation

Private rs As ADODB.Recordset
Private cnn As ADODB.Connection
Private strSQL As String

These are instance variables and belong to the class. They are equivalent to properties in every way except they don’t have any Get or Let properties methods attached to them and for this reason cannot be accessed from outside of the class. Therefore, they have not been prefixed with m_.

Private Sub Class_Initialize()
Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
End Sub

Private Sub Class_Terminate()
Call killRecordset
End Sub

Whenever you instantiate a class you have the option to add a sub-procedure that runs some code. In the above code snippet, we have a Class_Initialize method that instantiates a recordset (Set rs = New ADODB.Recordset). Because rs is an instance variable, as long as the class is instantiated, the rs will persist. What? In other words, the recordset will be available until the object created by the class is killed.

When the object created by the class is killed, we also have an option to run a sub-procedure. Class_Terminate will call another sub-procedure(KillRecordset) that will kill the recordset.

Note:  In this class module we will be using ADODB recordsets instead of DAO recordsets. You absolutely can use DAO but if you use ADODB, you only need change the connection string in order to connect with a different database system, such as SQL Server.

Public Sub loadData(ID As Long)
'This sub procedure loads the data based upon the
'passed in ID value. -1 means new record

If ID > 0 Then
    strSQL = "Select * From Films Where [ID]=" & ID
    Call loadRecordset(strSQL)
    Call setFields
Else
    m_ID = -1
    strSQL = "Select * From Films "
    Call loadRecordset(strSQL)
End If
End Sub

We know that rs is an ADODB.Recordset and that it is an object that is designed to hold a dataset but we haven’t told it which dataset to hold.

Public Sub loadData(ID As Long) 

The name of the sub-procedure is loadData and it takes a single argument (ID as long). We know the ID of the record we want to see and pass it into the class.

If ID > 0 Then

If we are adding a new record, the ID will be -1.

strSQL = "Select * From Films Where [ID]=" & ID 

We create a SQL Statement that we use as the basis for the recordset.

Call loadRecordset(strSQL) 

In order to load the recordset we call the loadRecordset method (discussed below).

Call setFields 

We call another function to set the properties of the class equal to the values of the recordset.

m_ID = -1
  strSQL = "Select * From Films "
  Call loadRecordset(strSQL) 

We use this code for when we are creating a new record. As we don’t have a record to use in Films, we open the Film table awaiting an insert.

Private Sub loadRecordset(strSQL As String)
    With rs
        .Open strSQL, cnn, adOpenKeyset, adLockOptimistic
        .MoveLast
        .MoveFirst
    End With
End Sub

This method loads a recordset based upon the passed in strSQL argument.

Private Sub loadRecordset(strSQL As String)

The name of the method is loadRecordset and it takes a single argument (strSQL as String).

With rs...End With 

By using a with statement we only need to use the dot (.) operator to reference properties of the recordset object. (.open instead of rs.open).

.Open strSQL, cnn, adOpenKeyset, adLockOptimistic 

With this line of code we open a recordset based on the strSQL variable (it will be something like: SELECT * FROM Films). adOpenKeySet determines the cursor type. adLockOptimistic determines the locking type. To learn more about cursor and lock types view this page.

.moveLast
.moveFirst 

Not strictly necessary but always worth moving to the last record and back to the first record so that the recordcount property is populated.

Public Sub setFields()
With rs
    m_ID = .Fields("ID")
    m_FilmName = .Fields("FilmName")
    m_YearOfRelease = .Fields("YearOfRelease")
    m_RottenTomato = .Fields("RottenTomatoes")
    m_Director = .Fields("DirectorID")
End With
End Sub

This is a simple sub-procedure that takes the values of the fields in the active record in the ADODB recordset and adds them to the clsFilm properties. We have given this code its own method as it is called from different locations within the class.

Private Sub killRecordset()
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

This method simply kills the recordset object. Again, we have put this code in its own method as it is called from multiple points in the class.

Public Function SaveRecord() As Boolean
With rs
    If m_ID > 0 Then

        .Fields("FilmName") = m_FilmName
        .Fields("YearOfRelease") = m_YearOfRelease
        .Fields("RottenTomatoes") = m_RottenTomato
        .Fields("DirectorID") = m_Director
        .Update
    Else
        .AddNew
        .Fields("FilmName") = m_FilmName
        .Fields("YearOfRelease") = m_YearOfRelease
        .Fields("RottenTomatoes") = m_RottenTomato
        .Fields("DirectorID") = m_Director
        .Update
    End If
End With
End Function

Now we get to the methods that really interest us. This is the reverse of the setFields method as SaveRecord takes the values of the clsFilms properties and either writes them to the database or inserts a new record (it evaluates the value of m_ID; -1 means that the record is new, any other value is the ID of the film).

The code we need to update to the table is fairly simple as we already have a recordset object open.

Public Function UndoRecord() As Boolean
With rs
    m_ID = .Fields("ID")
    m_FilmName = .Fields("FilmName")
    m_YearOfRelease = .Fields("YearOfRelease")
    m_RottenTomato = .Fields("RottenTomatoes")
    m_Director = .Fields("DirectorID")
End With
End Function

Something you will need is the ability to undo changes made to a form. Our concept of undoing is simple and in fact a little redundant. When a user uses an unbound form, none of the changes made to the form are committed to the database until the save button is clicked. So, to undo the changes all we need to do is read the original property values from the class module again (just like when the form loads). We are only including this code in the class to ensure that the properties of the class are directly lifted from the table.

Public Function DeleteRecord() As Boolean
Dim lngID As Long

lngID = m_ID
'We are going to use a simple query to delete the record
Call killRecordset

strSQL = "DELETE FROM Films WHERE [ID]=" & lngID
CurrentDb.Execute strSQL, dbFailOnError

End Function

In this method we are providing the ability to delete a record from the database. Although we have an open recordset, we don’t want to pass a delete command to it as it is possible to delete all the records in the recordset by accident! We only want to delete the active record but don’t want to take a risk with our data. For that reason, we are generating an SQL statement and executing it.

lngID = m_ID

We need to know which record to delete so we use the ID of the active record in the recordset.

Call killRecordset

We have a sub-procedure to kill the recordset and call it before doing anything else in this method.

strSQL = "DELETE FROM Films WHERE [ID]=" & lngID

This is a simple SQL Statement that will delete a specific record from the Films table (based on the ID).

CurrentDb.Execute strSQL, dbFailOnError

This line of code executes the query. If the query fails, an error will be generated.

Here is the database with the added code.