I am working on passing the test: WhenTwoTextFieldsChangeBeforeAndAfterValuesAreReturned.

Right now it is getting a compile error in the FieldChanged function because in the loop over the controls I am trying to pass the variant Fld variable, which the function required an Access.Control type variable.

This is a somewhat annoying problem. In order to use the For Each x in y, x must be a variant type.

I think I want my function to remain specific to Access Controls, so I will Dim a new variable with the Access.Control Type, set that to the variant Fld, then pass the new variable to the function.

Now I’ve got this code in my BeforeUpdate event:

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    Dim Fld As Variant, ChangeDictionary As Scripting.Dictionary, Ctl As Access.Control
    For Each Fld In FormToAudit
        Set Ctl = Fld
        If FieldChanged(Ctl) Then ChangeDictionary.Add CreateAuditFieldChange(Ctl)
    Next Fld
    If ChangeDictionary.Count > 0 Then pListOfChanges.Add CreateAuditEventDetails(ChangeDictionary)
End Sub

And now this doesn’t compile because the Dictionary Add method requires the key and value. That’s easy to fix:

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    Dim Fld As Variant, ChangeDictionary As Scripting.Dictionary, Ctl As Access.Control
    For Each Fld In FormToAudit
        Set Ctl = Fld
        If FieldChanged(Ctl) Then ChangeDictionary.Add Ctl.Name, CreateAuditFieldChange(Ctl)
    Next Fld
    If ChangeDictionary.Count > 0 Then pListOfChanges.Add CreateAuditEventDetails(ChangeDictionary)
End Sub

And the code compiles, but fails with a runtime error because I didn’t initialize the dictionary variable, so let’s do that:

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    Dim Fld As Variant, ChangeDictionary As New Scripting.Dictionary, Ctl As Access.Control
    For Each Fld In FormToAudit
        Set Ctl = Fld
        If FieldChanged(Ctl) Then ChangeDictionary.Add Ctl.Name, CreateAuditFieldChange(Ctl)
    Next Fld
    If ChangeDictionary.Count > 0 Then pListOfChanges.Add CreateAuditEventDetails(ChangeDictionary)
End Sub

So now I go past that hurdle and have tripped up on another hurdle. The loop is also looping over the label controls and those controls don’t have the OldValue property. This throws an error: 2427 You entered an expression that has no value. I could work around this in a couple of ways.

1. Use error handling to trap for the error. I’m not sure what other conditions might trip this error based on the message. Not sure if I like this option, although if Access adds new types down the road that include an OldValue item… Maybe it would make sense.
2. Check the control types I’m looking at. Currently I am only concrned about text boxes and combo boxes so I could check the ControlType property before I operate on it. I guess for now i feel like this is quicker and more stable, so I’ll do this method.
3. I also thought about checking the binding of the control. Not sure if labels can be bound, but even if they can, they can’t be changed by the user, right?

Anyway, I’m going to use method 2 for now.

Ok, here’s the new code for the BeforeUpdate event:

Private Sub FormToAudit_BeforeUpdate(Cancel As Integer)
    Dim Fld As Variant, ChangeDictionary As New Scripting.Dictionary, Ctl As Access.Control
    For Each Fld In FormToAudit
        Set Ctl = Fld
        If Ctl.ControlType <> acComboBox And Ctl.ControlType <> acTextBox Then GoTo SkipLoop
        If FieldChanged(Ctl) Then ChangeDictionary.Add Ctl.Name, CreateAuditFieldChange(Ctl)
SkipLoop:
    Next Fld
    If ChangeDictionary.Count > 0 Then pListOfChanges.Add CreateAuditEventDetails(ChangeDictionary)
End Sub

And it passes!

Next we refactor! Next time!