The other day I was coding an Access Form which displays a file list to choose files to import into the database. I had created the following components:

• An Access Form named: ImportForm
• A Class Module to handle a full list of files available for importing and containing individual ImportFile objects. This Class Module is named: Importer
• A second Class Module to handle an individual file. This will manage the file properties and the actual import process for that file type. This Class Module is named: ImportFile

So everything was built with pretty good separation and encapsulation meaning I’m not passing the Access Form into the Importer Class module. The Form creates an Importer and handles configuration of the Importer and asking it for a list of files based on the configuration.

The Form also has an Import button on it which passes the selected files to the Importer object ImportFile method, which in turn calls the ImportFile object Import method which does all the actual work of importing the file and has to iterate over multiple lines.

I wanted to display a status message counting the number of lines processed along with the total number of lines. My initial thought was that I could pass the form object or status field object down through the classes so the ImportFile Class would know about the top level form and update it’s status field. This would have worked, but would couple all these classes and objects together in a way that would require a lot of changes to use it elsewhere. I really just wanted the Form to receive notification that it should update it’s message.

The technique I ended up using was to add a public event to the Importer called StatusMessage.

' In the Class Importer
Public Event StatusMessage(msg As String)

Public Sub RaiseMessage(msg As String)
    RaiseEvent StatusMessage(msg)
End Sub

Then in the Form, I declare the Importer Object using the WithEvents keyword and created the code to run when the StatusMessage event is triggered:

' In the top level form
Dim WithEvents FileImporter As Importer

Private Sub FileImporter_StatusMessage(msg As String)
    Me.Label4.Caption = msg
    Me.Repaint
End Sub

This has the top level form listening to the importer object for the StatusMessage event.

I was having trouble re-using the ImportFile object events in the same way, so I did end up coupling the Importer and the ImportFile classes by putting a reference to the “Parent” of the ImportFile which references the Importer object so it can call the Importer method “RaiseMessage”. This will trigger the Form to run the code it uses for the StatusMessage event and update the status dialog.

' In Class ImportFile
Public Parent As Importer

Parent.RaiseMessage "Importing " & idx & " of " & UBound(aLines) & " for " & Me.FileName

The advantage to this again is that the Import Form that displays the list doesn’t need to know anything about how to actually import a file or get the file list. It simply creates it’s importer object and tells the importer the file it should act on based on what the user has selected. This can be moved to any form without changing any code in the Importer Class or the ImportFile Class. Woo-hoo!