title: String acting abnormally in a For Each loop in VBA code in MSAccess
body: <p>Okay so I started with <a href="https://stackoverflow.com/questions/391991/for-each-not-working-properly-in-vba-code">a question</a> and have made a lot of changes to the code based on suggestions from this site and others so I figured I should create a new question.</p>
  
  <p>Even though my code is shorter and more efficient the same problem persists; I am using a string called strSQL that contains and INSERT statement that I want executed.  I have a FOR EACH loop that goes through each control on my MSAcess form (ensures they are either a text box, drop down list or checkbox) and determines if the field has been changed.  If it has it generates a query string to log the change -- and stores it in strSQL.</p>
  
  <p>The problem is that the same query is being executed again and again.  I have added a DEBUG.PRINT statement before and after the line that executes the query string and the debugger shows that the string has CHANGED!  Yes you read that right, this seems to be impossible but I have taken screenshots.</p>
  
  <p>Firstly my code:</p>
  
  <pre><code>Private Sub Form_BeforeUpdate(Cancel As Integer)
      Dim C As Control
      For Each C In Controls
          Select Case C.ControlType
              Case acTextBox, acComboBox, acCheckBox
                  Dim strOriginalValue, strCurrentValue, strSQL As String
  
                  strOriginalValue = IIf(IsNull(C.OldValue), "", IIf(C.OldValue = vbTrue Or C.OldValue = vbFalse, IIf(C.OldValue = vbTrue, "Yes", "No"), C.OldValue))
                  strCurrentValue = IIf(IsNull(C.Value), "", IIf(C.Value = vbTrue Or C.Value = vbFalse, IIf(C.Value = vbTrue, "Yes", "No"), C.Value))
  
                  If strOriginalValue &lt;&gt; strCurrentValue Then
                      strSQL = "INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'" &amp; ThisUserName() &amp; "','Edit'," &amp; [id] &amp; ",0,'" &amp; C.ControlSource &amp; "','Administrator','" &amp; Replace(strOriginalValue, "'", "") &amp; "','" &amp; Replace(strCurrentValue, "'", "") &amp; "')"
                      Debug.Print "Before: " &amp; strSQL
                      CurrentDb.Execute strSQL, dbFailOnError
                      Debug.Print "After: " &amp; strSQL
                  End If
          End Select
      Next
  End Sub
  </code></pre>
  
  <p>Here is the results of the debugger:</p>
  
  <pre><code>Before: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'indoor_performers_tab','Administrator','No','Yes')
  After: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'indoor_performers_tab','Administrator','No','Yes')
  Before: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'volunteers_tab','Administrator','No','Yes')
  After: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'volunteers_tab','Administrator','No','Yes')
  </code></pre>
  
  <p>Secondly my screenshots...  firstly the debugger: <a href="http://www.andrew-g-johnson.com/access1.jpg" rel="nofollow noreferrer">1</a> &amp; <a href="http://www.andrew-g-johnson.com/access2.jpg" rel="nofollow noreferrer">2</a> and secondly the results: <a href="http://www.andrew-g-johnson.com/access3.jpg" rel="nofollow noreferrer">here</a> -- note that the debugger is too wide so I took two screenshots and the results have a row colored out as it has nothing to do with this problem.</p>
  
  <p>Note that the only column that should be different in the example I gave should be the "field_affected" column.</p>
  
  <p>I am at a complete loss, I have no idea why it would output the correct string to the debugger and execute something else.</p>
  
  <p><strong>EDIT: Before using CurrentDb.Execute, I was using DoCmd.RunSQL but this required me to disable and then re-enable warnings.  Since the result was the same for both (same error) I used the one line solution instead of the three line solution.</strong></p>
  
  <p><strong>UPDATE: Shoutout to <a href="https://stackoverflow.com/questions/396848/string-acting-abnormally-in-a-for-each-loop-in-vba-code-in-msaccess#396989">shahkalpesh</a> for helping me realize that the data is indeed being inserted correctly and I can see it in SQL Server's management studio but MS Access is still displaying it incorrectly... the question is why?</strong></p>
  
  <p><strong>FINAL EDIT: Fixed by adding an identity/auto-increment integer column to the table, I suspect that the lack of differing field values was confusing MSAccess (although it really has no reason to) -- the morale here is M$ is stupid</strong></p>