vba 从 Access 中的表中删除记录

Delete record from table in Access

提问by William

I am working with two tables Mitigationsand New Control IDsin an Access database. The Mitigationstable has a form. As part of that form, a control IDcan be added to the New Control IDstable. My code for that works fine. I am trying to add the ability to delete a record from the New Control IDstable via the Mitigationsform.

我正在使用Access 数据库中的两个表Mitigations和New Control IDs。该缓解措施表有一个形式。作为该表单的一部分，控件ID可以添加到新控件 ID表中。我的代码工作正常。我正在尝试添加通过缓解表单从新控件 ID表中删除记录的功能。

The user will enter a string in Text55and after updating that field, the corresponding record in the New Control IDstable should be deleted.

用户将在Text55 中输入一个字符串，更新该字段后，应删除New Control IDs表中的相应记录。

Here is the code I have for that:

这是我的代码：

Private Sub Text55_AfterUpdate()

'removing record with Archer Control ID from New Control IDs table
Dim dbNewInitiatives As DAO.Database
Dim rstMitigations As DAO.Recordset
Dim strSQL As String

    Set dbNewInitiatives = CurrentDb
    strSQL = "SELECT * FROM [New Control IDs] WHERE ([Mitigation ID] = " & Me.[Mitigation ID].Value & ") AND ([Archer Control ID] = '" & Me.[Text55].Value & "') ORDER BY [ID]"
    Set rstMitigations = dbNewInitiatives.OpenRecordset(strSQL, dbOpenDynaset)

    Do While Not rstMitigations.EOF
        rstMitigations.Delete
        rstMitigations.MoveNext
    Loop

rstMitigations.Close

Set rstMitigations = Nothing
Set dbNewInitiatives = Nothing

End Sub

The above code successfully finds all of the records in New Control IDstable that meet the criteria and deletes them. Thank you!

上面的代码成功地找到了New Control IDs表中符合条件的所有记录并删除它们。谢谢！

回答by Parfait

Consider running a DELETEaction query using the Database.Executemethod without the need of a DAO recordset:

考虑在不需要 DAO 记录集的情况下DELETE使用Database.Execute方法运行操作查询：

Set dbNewInitiatives = CurrentDb
strSQL = "DELETE FROM [New Control IDs]" _
           & " WHERE ([Mitigation ID] = " & Me.[Mitigation ID].Value & ")" _
           & " AND ([Control ID] = '" & Me.[Text55].Value & "')"

dbNewInitiatives.Execute strSQL, dbFailOnError

回答by C Perkins

Definitely consider Parfait's answer, but it is always good to understand what the problem was in the first place. To me it looks like the logic in your loop is probably incorrect.

绝对考虑 Parfait 的答案，但首先了解问题所在总是好的。在我看来，循环中的逻辑可能不正确。

Unless you provide more detail, I'll first assume that [New Control IDs].[ID] is a unique key on that table. If that is the case, then you are storing that unique ID in the variable strID but then moving to the next record before actually attempting to delete the record. In this case, the If statement in the loop will always be falsesince the current record's [ID] would not equal the previous record's [ID] value.

除非您提供更多详细信息，否则我将首先假设 [New Control IDs].[ID] 是该表上的唯一键。如果是这种情况，那么您将该唯一 ID 存储在变量 strID 中，然后在实际尝试删除记录之前移动到下一条记录。在这种情况下，循环中的 If 语句将始终为假，因为当前记录的 [ID] 将不等于前一条记录的 [ID] 值。

...
Do While Not rstMitigations.EOF
    '* This compares the previous row's [ID] with the current row's [ID]  '
    If rstMitigations![ID] = strID Then
        rstMitigations.Delete
...

If the [ID] values are not unique, then your code would only delete subsequent rows that matched the first in a series of identical [ID] values. It would leave the first row of each unique [ID] value untouched (i.e. not deleted). If there is only one of a particular [ID] value, that row will never be deleted. Is that what you want?

如果 [ID] 值不是唯一的，那么您的代码只会删除与一系列相同 [ID] 值中的第一行匹配的后续行。它将保留每个唯一 [ID] 值的第一行不变（即不删除）。如果只有一个特定的 [ID] 值，则永远不会删除该行。那是你要的吗？

Notice that Parfait's action query will delete all of the same records returned by your SELECT query. Just for the record, to accomplish this with the recordset, just simplify it to be

请注意，Parfait 的操作查询将删除您的 SELECT 查询返回的所有相同记录。只是为了记录，要通过记录集完成此操作，只需将其简化为

....
Set dbNewInitiatives = CurrentDb
strSQL = "SELECT * FROM [New Control IDs] WHERE ([Mitigation ID] = " _
        & Me.[Mitigation ID].Value & ") AND ([Control ID] = " _
        & Me.[Text55].Value & ") ORDER BY [ID]"
Set rstMitigations = dbNewInitiatives.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rstMitigations.EOF
    rstMitigations.Delete
    rstMitigations.MoveNext
Loop
....