The last test I wrote which I thought would pass actually doesn’t.

I wrote a test to add 2 (the many condition) dictionary entries. I think I decided to use a dictionary for saving the changes by the field name, however, the same field name would be changed multiple times and this is what makes the many condition fail. I am trying to add the same key twice to a dictionary which results in an error.

To use a dictionary then will require me to have a unique key for each time the BeforeUpdate event is run. I could change this to a collection object. This would not require a key. OR I could try to think of a unique key for the instance. Perhaps the current time. I could review what granularity the current time (say the Now() function) resolves to and mess around with that, but will I really be accessing that as a key in a dictionary? No, not really. I think I should actually change the dictionary to a collection. This would store each set of changes. Perhaps for a particular BeforeUpdate event I can use the field names as the dictionary keys for which field got changed.

So, first thing will be to change the dictionary to a collection. One nice thing about this is that it doesn’t force a dependency on the Microsoft Scripting Runtime. The collection object I am going to use is included with VBA.

Here’s my complete new FormAuditor class code:

Option Compare Database
Option Explicit

Private WithEvents FormToAudit As Access.Form
Private pListOfChanges As New Collection

Public Property Get ListOfChanges() As Collection
    Dim retVal As Collection
    Set retVal = pListOfChanges
    Set ListOfChanges = retVal
End Property

Private Sub Class_Initialize()
    Set FormToAudit = Forms("TestForm")
    FormToAudit.BeforeUpdate = "[Event Procedure]"
End Sub

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    pListOfChanges.Add FormToAudit.TestText.Value
End Sub

Now I will need to update the tests to use Collections instead of dictionaries.

'@TestMethod("Count Changes")
Private Sub WhenNoFieldIsChangedThenReturnEmptyListOfChanges()
    Dim testFormAuditor As New FormAuditor
    Dim testCollection As New Collection
    Set testCollection = testFormAuditor.ListOfChanges
    Assert.AreEqual CLng(0), testCollection.Count
End Sub

'@TestMethod("Count Changes")
Private Sub WhenOneFieldIsChangedThenReturnSingleListOfChanges()
    Dim testFormAuditor As FormAuditor
    Dim testCollection As New Collection
    Set testFormAuditor = New FormAuditor
    Randomize Timer
    NewForm.TestText = "New Thing " & Rnd()
    NewForm.Dirty = False
    Set testCollection = testFormAuditor.ListOfChanges
    Assert.AreEqual CLng(1), testCollection.Count
End Sub

'@TestMethod("Count Changes")
Private Sub WhenTwoFieldsAreChangedThenReturnTwoEntryListOfChanges()
    Dim testFormAuditor As FormAuditor
    Dim testCollection As New Collection
    Set testFormAuditor = New FormAuditor
    Randomize Timer
    NewForm.TestText = "New Thing " & Rnd()
    NewForm.Dirty = False
    NewForm.TestText = "New Thing " & Rnd()
    NewForm.Dirty = False
    Set testCollection = testFormAuditor.ListOfChanges
    Assert.AreEqual CLng(2), testCollection.Count
End Sub

The database compiles and the tests… all pass! Woo-hoo!

That’ll do pig… That’ll do.

James Cromwell Shares Story Behind His Final Line in ‘Babe’