嵌套的Dlookup VBA

Question

我正在尝试显示供应商名称和一条消息，表明选定的PO编号(从表单上的组合框中)不需要在消息框中提供QA。从PO编号中获取供应商编号的DLookup可以工作，但是我似乎不能嵌套它来从单独的表中获取供应商名称字符串。我在这个嵌套的DLookup中遗漏了什么？

采购订单编号、供应商编号、供应商名称都是字符串。

Private Sub VendorName_AfterUpdate()

    Debug.Print Me.VendorName.Value

    If DCount("*", "ZeroAudit", "PONumber='" & Me.PONumber & "' and VendorName='" & Me.VendorName & "'") = 0 Then
        MsgBox "Needs QA"
    Else
        MsgBox "Vendor " & DLookup("Vendor", "Vendors", "VendorNumber = " & (DLookUp("VendorNumber","POHeader","PONumber= [Forms]!QAChecker2!PONumber.Value"))) & " — Does NOT Need QA — DIM and RETAIL CHECK REQUIRED"
    End If
    Me.PONumber.SetFocus

End Sub

Answer 1

你声明：

PO号、Vendor Number和Vendor Name 都是字符串。

因此，您需要用单引号将DLookup条件括起来，例如，如下所示：

DCount("*", "ZeroAudit", "PONumber='" & Me.PONumber & "' and VendorName='" & Me.VendorName & "'")

您还需要在此处使用单引号：

DLookup("Vendor", "Vendors", "VendorNumber = '" & DLookUp("VendorNumber","POHeader","PONumber= [Forms]!QAChecker2!PONumber.Value") & "'")
                                             ^                                                                                        ^
                                             |                                                                                        |
                    Added single quotes -----+----------------------------------------------------------------------------------------+

但我也要回应@Mathieu Guindon关于使用局部变量代替内联DLookup表达式的评论。

考虑一下下面的代码是多么容易读懂-

Private Sub VendorName_AfterUpdate()
    Dim strVen As String
    Dim strVNo As String

    If DCount("*", "ZeroAudit", "PONumber='" & Me.PONumber & "' and VendorName='" & Me.VendorName & "'") = 0 Then
        MsgBox "Needs QA"
    Else
        strVNo = DLookup("VendorNumber", "POHeader", "PONumber = [Forms]!QAChecker2!PONumber")
        strVen = DLookup("Vendor", "Vendors", "VendorNumber = '" & strVNo & "'")
        MsgBox "Vendor " & strVen & " — Does NOT Need QA — DIM and RETAIL CHECK REQUIRED"
    End If
    Me.PONumber.SetFocus
End Sub

您还可以使用Nz表达式将DLookup表达式括起来，以捕获空值，并在继续之前测试空字符串。

Answer 2

虽然鲜为人知，但您也可以在过滤器中使用SQL：

DLookup("Vendor", "Vendors", "VendorNumber = (Select VendorNumber From POHeader Where PONumber = [Forms]!QAChecker2!PONumber.Value)")