For each not working properly in VBA code

Question

Well I am almost done finalizing the auditing portion of my application that I discussed here. The way I am doing it is looping through all text fields, drop down boxes and checkboxes and storing their values in the form_load event. Then I am doing the same thing in the form_afterUpdate event and comparing the two. If there is a difference I am logging it, if not I move on. Here is the code:

Dim strValues(1 To 32) As String

Private Sub Form_AfterUpdate()
    Dim strCurrentValue, strSQL As String
    Dim intCurrentField As Integer
    intCurrentField = 1

    For Each C In Forms!frmVendorsManageVendors.Controls
        Select Case C.ControlType
            Case acTextBox, acComboBox, acCheckBox
                //Doing this because I don't want a NULL as it won't concatenate in the SQL query and don't want 0 or -1 for the boolean fields
                strCurrentValue = IIf(IsNull(C), "", IIf(C = vbTrue Or C = vbFalse, IIf(C = vbTrue, "Yes", "No"), C))

                If strValues(intCurrentField) <> strCurrentValue Then
                    strSQL = "INSERT INTO changesTable (change_time,user_affected,field_affected,old_value,new_value) VALUES (NOW()," & [id] & ",'" & C.ControlSource & "','" & strValues(intCurrentField) & "','" & strCurrentValue & "')"

                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSQL
                    //InputBox "", "", strSQL
                    strSQL = "WEEEE"
                    DoCmd.SetWarnings True

                    strValues(intCurrentField) = strCurrentValue
                End If

                intCurrentField = intCurrentField + 1
        End Select
    Next
End Sub

Private Sub Form_Open(Cancel As Integer)
    Call btnLock_Click

    Dim intCurrentField As Integer
    intCurrentField = 1

    For Each C In Forms!frmVendorsManageVendors.Controls
        Select Case C.ControlType
            Case acTextBox, acComboBox, acCheckBox
                //Doing this because I don't want a NULL as it won't concatenate in the SQL query and don't want 0 or -1 for the boolean fields
                strValues(intCurrentField) = IIf(IsNull(C), "", IIf(C = vbTrue Or C = vbFalse, IIf(C = vbTrue, "Yes", "No"), C))
                intCurrentField = intCurrentField + 1
        End Select
    Next
End Sub

As you can see there is a commented out line where I insert into the changesTable that will put up the query in an input box so I can copy/paste it and look at it. When I uncomment that line everything is fine. If it is commented it generates the first change fine, but then won't change it for the other controls. So if I change field1 and field2 it will insert the field 1 change twice.

It is quite confusing and I have NO CLUE as to why this is happening.

Also I know I am using the wrong comment syntax but if I use the correct syntax the SO "code color"er doesn't display properly.

Answer 1

Have you tried doing it with an execute statement (something like this)?

Dim db As DAO.Database    'Inside the transaction.
Set db = CurrentDB
strSQL = "INSERT INTO changesTable (change_time, user_affected, " & _ 
            "field_affected, old_value, new_value) VALUES (NOW()," & [id] & _
            ",'" & C.ControlSource & "','" & strValues(intCurrentField) & _
            "','" & strCurrentValue & "')"
db.Execute strSql

Answer 2

I am guessing that AfterUpdate might not be the right event to use.

Also, putting inputbox might be causing the existing control to loose focus (which is making it behave correctly).

I would suggest checking that each of your controls are being run over by putting a msgbox C.name inside the loop after select case.

Answer 3

I'm not sure I have the whole answer, but a couple of observations.

You can eliminate some lines of code by using CurrentDB.Execute strSQL. This elminates the need for the SetWarnings calls. It executes directly against the database without interacting with the usual interface mechanisms.

For debugging purposes, it might be better to use Debug.Print to put your SQL string out to the Debug window. It avoids involving the user interface still puts the SQL where you can copy it to the clipboard if you want to grab it and work with it.

I think there's a slim chance that the DoCmd method call to execute your SQL, even with the calls to SetWarnnigs, might be tipping something in the interface to pull focus off of the form, like shahkalpesh suggested. I've done things like this and not seen the problem you are having, so my only advice on the problem itself is to do as I do and switch to CurrentDB.Execute and eliminate calls to DoCmd inside the loop.

Just curious -- why did you use an array for the previous values rather than using the OldValue property on the controls?