In this post we will be demonstrating how you can instantiate your newly create clsFilms class module from the Form.
Test Code
One of the most significant differences between class and standard modules is that you cannot instantiate a class from the self-same class. What? In other words, class modules are useless on their own and have to be declared and instantiated from another object (form class, report class, standard module, other class module) to work. Standard modules do not have this restriction as they do not require instantiating.
So, up to now, everything in the clsFilms class is for nothing unless we have a way to use this class productively. What we do have with clsFilms though, is an interface that we can call from anywhere within our application and access to the methods we have created. This is not be taken lightly as a concept. By creating a class module we have centralised the code and if we were to, for example, make a change to the Films table, we would only need to amend this particular class module (assuming it represented the only way to manipulate data in the Films table) to reflect that change.
The Code
You will need to create a Microsoft Access Class Object for frmFilmsDataEntry. This is much easier than it sounds.
Open up frmFilmDataEntry in design view and on the Other tab of the property sheet, change the HasModule property to Yes. If you can’t see the property sheet click on ALT+F4.
Press Alt+F11 to open up the VBA editor and you will see Forms_frmFilmsDataEntry under Microsoft Access Class Objects. This means you have added the necessary module.
Double click on Form_frmFilmsDataEntry and copy and paste the code below into the Editor to the right.
Private cfilms As clsFilms
Private isNew As Boolean
Private lngID As Long
Private Sub Form_Close()
Set cfilms = Nothing
End Sub
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
'set id of record -1 if new
lngID = Me.OpenArgs
If lngID > 0 Then
isNew = False
Else
isNew = True
End If
'instantiate class
Set cfilms = Nothing
Set cfilms = New clsFilms
'call load data
cfilms.loadData (lngID)
'fill in controls
Call FillInForm
End If
End Sub
Public Sub FillInForm()
With Me
If isNew = False Then
.ID = cfilms.ID
.FilmName = cfilms.FilmName
.YearOfRelease = cfilms.YearOfRelease
.RottenTomatoes = cfilms.RottenTomato
.DirectorID = cfilms.Director
Else
.ID = -1
.FilmName = ""
.YearOfRelease = ""
.RottenTomatoes = ""
.DirectorID = ""
End If
End With
End Sub
Private Sub cmdSave_Click()
With Me
'validate input
If IsNull(.FilmName) Then
MsgBox "Please fill in the form name.", vbExclamation
ElseIf IsNull(.YearOfRelease) Then
MsgBox "Please fill in the Year of Release.", vbExclamation
ElseIf IsNull(.RottenTomatoes) Then
MsgBox "Please fill in the Rotten Tomatoes rating", vbExclamation
ElseIf IsNull(.DirectorID) Then
MsgBox "Please choose a director.", vbExclamation
Else
cfilms.FilmName = .FilmName
cfilms.YearOfRelease = .YearOfRelease
cfilms.RottenTomato = .RottenTomatoes
cfilms.Director = .DirectorID
End If
End With
cfilms.SaveRecord
MsgBox "Record Saved"
End Sub
Private Sub cmdUndo_Click()
cfilms.UndoRecord
Call FillInForm
MsgBox "Changes undone"
End Sub
Private Sub cmdDelete_Click()
cfilms.DeleteRecord
MsgBox "Record Deleted"
DoCmd.Close acForm, "frmFilmsDataEntry", acSaveNo
End Sub
The Explanation
Private cfilms As clsFilms
In order to use a class module we will need to declare and instantiate it. This line of codes takes care of the declaration.
Private isNew As Boolean
Declares a variable called isNew.
Private lngID As Long
Declares a variable called lngID.
Private Sub Form_Close()
This code will run when the form is closed.
Set cfilms = Nothing
Ensures that the value of cFilms is set to nothing. This is a little bit of overkill on our part as the form closing by itself will take care of that but for the sake of correctness, we have included it.
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
'set id of record -1 if new
lngID = Me.OpenArgs
If lngID > 0 Then
isNew = False
Else
isNew = True
End If
'instantiate class
Set cfilms = Nothing
Set cfilms = New clsFilms
'call load data
cfilms.loadData (lngID)
'fill in controls
Call FillInForm
End If
End Sub
This procedure runs when the form is loaded.
If Not IsNull(Me.OpenArgs) Then
Me.OpenArgs refers to a text string passed to the form. It represents one way of passing values between forms in Access. In order to pass an open argument to a form you will need to use the Docmd statement in VBA.
Docmd.openform “frmFilmsDataEntry”,,,,,5 would pass the value of “5” to frmFilmsDataEntry when it opens.
lngID = Me.OpenArgs
This sets the value of lngID equal to me.openargs.
If lngID > 0 Then
isNew = False
Else
isNew = True
End If
When opening the form, if we want to add a new record, we will not have a Film ID to pass over as it doesn’t exist yet. For that reason we pass the value of -1 (this is a standard coding practice idea). If the lngID is equal to -1 then the variable isNew is set to true. Otherwise it is set to false.
Set cfilms = Nothing
Another little piece of overkill.
Set cfilms = New clsFilms
Now we instantiate the clsFilms – class and create an object called cfilms.
cfilms.loadData (lngID)
After instantiating the class we call the method
loadData. loadData takes one argument (lngID).
Call FillInForm
And finally we need to fill in the form. We have a sub-procedure for that (explained below).
Public Sub FillInForm()
With Me
If isNew = False Then
.ID = cfilms.ID
.FilmName = cfilms.FilmName
.YearOfRelease = cfilms.YearOfRelease
.RottenTomatoes = cfilms.RottenTomato
.DirectorID = cfilms.Director
Else
.ID = -1
.FilmName = ""
.YearOfRelease = ""
.RottenTomatoes = ""
.DirectorID = ""
End If
End With
End Sub
Public Sub FillInForm()
The sub-procedure is called FillInForm and it takes no arguments.
With Me...End With
Using the With…End With structure means that we can refer to properties of the form without having to write ‘me.’ every time.
If isNew = False Then...Else...End If
We set the value of isNew earlier and now we are using to determine which code to run. Remember that isNew refers to whether the Form has been opened to enter a new record or whether it has been opened to update an existing one.
.ID = cfilms.ID .FilmName = cfilms.FilmName .YearOfRelease = cfilms.YearOfRelease .RottenTomatoes = cfilms.RottenTomato .DirectorID = cfilms.Director
If isNew = false (we are updating an existing record rather than entering a new one), we will need to read the properties of the cfilms class. The cfilms class has interacted with the database and loaded its properties with the existing values of the record. We are now entering those values in the forms in the database.
.ID = -1 .FilmName = "" .YearOfRelease = "" .RottenTomatoes = "" .DirectorID = ""
If isNew = true (we are inserting a new record), we do not need to fill out the forms with existing values as they haven’t been created yet!
Private Sub cmdSave_Click()
With Me
'validate input
If IsNull(.FilmName) Then
MsgBox "Please fill in the form name.", vbExclamation
ElseIf IsNull(.YearOfRelease) Then
MsgBox "Please fill in the Year of Release.", vbExclamation
ElseIf IsNull(.RottenTomatoes) Then
MsgBox "Please fill in the Rotten Tomatoes rating", vbExclamation
ElseIf IsNull(.DirectorID) Then
MsgBox "Please choose a director.", vbExclamation
Else
cfilms.FilmName = .FilmName
cfilms.YearOfRelease = .YearOfRelease
cfilms.RottenTomato = .RottenTomatoes
cfilms.Director = .DirectorID
End If
End With
cfilms.SaveRecord
MsgBox "Record Saved"
End Sub
Private Sub cmdSave_Click()
Clicking the cmdSave button will run this code.
With Me...End With
Using the With…End With structure means that we can refer to properties of the form without having to write ‘me.’ every time.
'validate input
If IsNull(.FilmName) Then
MsgBox "Please fill in the form name.", vbExclamation
ElseIf IsNull(.YearOfRelease) Then
MsgBox "Please fill in the Year of Release.", vbExclamation
ElseIf IsNull(.RottenTomatoes) Then
MsgBox "Please fill in the Rotten Tomatoes rating", vbExclamation
ElseIf IsNull(.DirectorID) Then
MsgBox "Please choose a director.", vbExclamation
This code displays a message to the user if certain fields haven’t been filled in. This represents some basic but essential form validation.
Else cfilms.FilmName = .FilmName cfilms.YearOfRelease = .YearOfRelease cfilms.RottenTomato = .RottenTomatoes cfilms.Director = .DirectorID End If
If all the relevant fields contain values, we take the values in the fields and overwrite the properties in clsFilms.
cfilms.SaveRecord
We now call the SaveRecord method to write the properties to the database.
MsgBox "Record Saved"
Always keep your users informed of what is happening.
Private Sub cmdUndo_Click() cfilms.UndoRecord Call FillInForm MsgBox "Changes undone" End Sub
Private Sub cmdUndo_Click()
This code will run when the cmdUndo button is clicked.
cfilms.UndoRecord
In order to undo any changes we have made on the form (but not committed as we haven’t clicked the save button), we run the cfilms.UndoRecord method of the class.
Call FillInForm
Now we have called the cfilms.UndoRecord method we can use the FillInForm method to enter “refreshed” values in the form.
MsgBox "Changes undone"
Keeping the users informed.
Private Sub cmdDelete_Click() cfilms.DeleteRecord MsgBox "Record Deleted" DoCmd.Close acForm, "frmFilmsDataEntry", acSaveNo End Sub
Private Sub cmdDelete_Click()
This code will run when the cmdDelete button is clicked.
cfilms.DeleteRecord
In order to delete the record, we call the cfilms.DeleteRecord method of the class.
MsgBox "Record Deleted"
Keeping the users informed.
DoCmd.Close acForm, "frmFilmsDataEntry", acSaveNo
As the record has been deleted, we will close the form.
Conclusion
Class modules can be used as an interface to a table so that users can simply reference methods of the class, rather than getting direct access to the table. In the unbound form scenario we have been covering, the creator of frmFilmsDataEntry need not have actually written the clsFilms class. He or she could have been tasked with creating the form and simply given a list of methods available in clsFilms. How these methods work are of no concern in this case; the creator of the form only needs to know how to implement the methods in the form.
Class modules are clearly more difficult to learn than standard modules but they are genuinely worth the effort. They shouldn’t be used all the time as the coding can become extensive but if you have a form that is used constantly in a multi-user environment, class modules could be a good solution.
Use the code covered in this mini-series on unbound forms with class modules as the template for any future class modules you may wish to create.
