对于每一个不正常的VBA code [英] For each not working properly in VBA code

问题描述

嗯，我几乎完成了最后确定我的申请，我讨论的<一的审计部分href="http://stackoverflow.com/questions/390567/form-level-event-for-when-data-is-changed-on-a-msaccess-form">here.我做的方式是遍历所有文本字段，下拉框和复选框，并在Form_Load事件存储其值。然后我做了form_afterUpdate事件同样的事情，两相比较。如果是有区别的，我记录了，如果不是我继续前进。这里是code：

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

正如你可以看到有一个注释行，其中我插入到changesTable将搭起查询输入框，所以我可以复制/粘贴，看看它。当我取消这条线，一切都很好。如果是评论它生成的第一个变化细微，但不会改变它的其他控件。所以，如果我改变FIELD1和FIELD2它会插入域1变两次。

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.

此外，我知道我使用了错误的注释语法，但如果我用正确的语法所以code色呃无法正​​常显示。

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.

推荐答案

我不知道我的全部答案，但一些观察。

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

您可以通过使用CurrentDB.Execute STRSQL消除code一些行。此elminates需要对SetWarnings呼叫。它直接执行对数据库没有与通常的界面机制进行交互。

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.

有关调试的目的，它可能是最好使用Debug.Print把你的SQL字符串输出到调试窗口。它避免涉及用户界面仍然会将SQL，你可以，如果你想抓住它，并使用它复制到剪贴板。

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.

我觉得有一个渺茫的机会，在的DoCmd方法调用执行SQL，甚至与调用SetWarnnigs，可能会翻倒东西在界面拉断集中的形式，像shahkalpesh建议。我已经做了这样的事情，并没有看到你遇到的问题，所以我对问题本身唯一的建议就是做和我一样，切换到CurrentDB.Execute和消除调用的DoCmd内循环。

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.

只是好奇 - 为什么你使用一个数组为previous值，而不是使用上的控件的OldValue属性

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

这篇关于对于每一个不正常的VBA code的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！