循环VBA DLookup

Question

我编写了一个函数来循环自定义对象(C_Document)的数组。在循环中，如果文档号不存在，则应该在表tbl_docs中插入一个新记录。如果文档确实存在，则应该更新数据库中的适当记录。

Public Function updateDocuments(docs() As C_Document) As Double
Dim db As Object
Set db = Application.CurrentDb
Dim docIndex As Double

'Loop through all imported documents
For docIndex = 1 To UBound(docs)

    Dim strSQL As String

    Dim exists As Double
    exists = DCount("docNo", "tbl_docs", "docNo = '" & docs(docIndex).getDocNo() & "'" > 0)

    'Check if entry already exists
    If (exists > 0) Then
        'docNo entry already exists - update

         strSQL = "UPDATE tbl_docs SET " & _
                "docReviewStatus = " & docs(docIndex).getDocStatus() & "," & _
                "docRev = '" & docs(docIndex).getDocReview() & "'," & _
                "docDate = '" & docs(docIndex).getDocDate() & "'" & _
                " WHERE (" & _
                "docNo = '" & docs(docIndex).getDocNo() & "');"

    Else
        'docNo does not exist - insert

        strSQL = "INSERT INTO tbl_docs (docNo, docReviewStatus, docRev, docDate) " & _
                    "SELECT '" & docs(docIndex).getDocNo() & "'" & _
                    "," & docs(docIndex).getDocStatus() & _
                    ",'" & docs(docIndex).getDocReview() & "'" & _
                    ",'" & docs(docIndex).getDocDate() & "'" & _
                    ";"

    End If

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    MsgBox strSQL

    Next

updateDocuments = docIndex

端函数

但是，当函数被调用时( tbl_docs为空)，它只插入一条记录，之后的string成为update语句。

​

​

​

在循环中使用DCount()时是否存在常见问题？有人对这个逻辑错误有任何经验吗？

Answer 1

您的检查有一个轻微但重要的错误：

exists = DCount("docNo", "tbl_docs", "docNo = '" & docs(docIndex).getDocNo() & "'" > 0)

应该是

exists = DCount("docNo", "tbl_docs", "docNo = '" & docs(docIndex).getDocNo() & "'") > 0

或者如果exists不是bool，而是简单的计数，那么

exists = DCount("docNo", "tbl_docs", "docNo = '" & docs(docIndex).getDocNo() & "'")

Answer 2

您可以使用DAO简化和加快这一点，在这里您可以一次性完成搜索和更新/编辑：

Public Function updateDocuments(docs() As C_Document) As Long

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim docIndex As Long
    Dim strSQL As String

    strSQL = "Select * From tbl_docs"

    Set db = Application.CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    'Loop through all imported documents
    For docIndex = LBound(docs) To UBound(docs)

        rs.FindFirst "docNo = '" & docs(docIndex).getDocNo() & "'"
        If rs.NoMatch Then
            'docNo does not exist - insert
            rs.AddNew
            rs!docNo.Value = docs(docIndex).getDocNo()
        Else
            'docNo entry already exists - update
            rs.Edit
        End If
        rs!docReviewStatus.Value = docs(docIndex).getDocStatus()
        rs!docRev.Value = docs(docIndex).getDocReview()
        rs!docDate = docs(docIndex).getDocDate()
        rs.Update

    Next
    rs.Close

    updateDocuments = docIndex

End Function