For each does not work as expected in VBA code
Well, I've almost finished completing the audit portion of my application that I discussed here. The way I do it is to iterate over all textboxes, dropdowns and checkboxes and store their values in the form_load event. Then I do the same in the form_afterUpdate event and compare the two. If there is a difference, I log it if I don't 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 comment line that I am pasting into changeTable that will populate the request in the input field so I can copy / paste it and look at it. When I uncomment this line, everything is fine. If commented out, it generates the first change penalty, but then will not change it for other controls. So if I change field1 and field2, it inserts field 1 twice.
This is pretty confusing and I don't have ANY CLUE why this is happening.
Also I know I am using the wrong comment syntax, but if I use the correct syntax then the SO "code color" code is not displayed as expected.
source to share
I'm not sure if I have the entire answer, but a few observations.
You can eliminate some lines of code using CurrentDB.Execute strSQL. This removes the need for calls to SetWarnings. It runs directly against the database without interacting with the normal front-end mechanisms.
For debugging purposes, it is best to use Debug.Print to print the SQL string to the Debug window. This avoids using a UI that 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 is a subtle chance that calling DoCmd to execute your SQL, even with calls to SetWarnnigs, might roll something in the interface to pull focus out of the form, as shahkalpesh suggested. I've done this kind of thing and haven't seen the problem you're facing, so my only advice for the problem itself is to do as I did and switch to CurrentDB.Execute and eliminate the DoCmd calls inside the loop.
Just curious - why did you use an array for the previous values instead of using the OldValue property for the controls?
source to share
Have you tried to do this with the 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
source to share