Nested Dlookup VBA

Question

I'm trying to display the Vendor Name and a message that a selected PO Number (from a combo box on the form) does not require QA in a message box. The DLookup to get the vendor number from the PO Number works, but I can't seem to nest it to get the Vendor Name string from a separate table. What am I missing in this nested DLookup?

PO Number, Vendor Number, and Vendor Name are all strings.

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

Why inline everything? Capture the inner `DLookup` result into a local variable, then use that variable in the outer one - capture the result of the outer result into its own variable, and send *that* to `MsgBox`. You'll have a much easier time debugging if each instruction does only one thing at a time... — Mathieu Guindon, Aug 29 '19 at 19:09
I'm pretty new at VBA, but I'm all for making things simple to debug! Can you show me what that would look like? — Newbie, Aug 29 '19 at 19:25
`Dim vendorNumber As String`, then `vendorNumber = DLookup("VendorNumber", ...)`; then `Dim vendor As String`, and `vendor = DLookup("Vendor", "Vendors", "VendorNumber = " & vendorNumber)` -- then you can know which one is failing, and possibly more easily see why, too. — Mathieu Guindon, Aug 29 '19 at 19:28

Lee Mac · Answer 1 · 2019-08-29T21:28:36.280

You state:

PO Number, Vendor Number, and Vendor Name are all strings.

As such, you'll need to surround the DLookup criteria with single quotes, e.g. as you have here:

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

You'll also need single quotes here:

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

But I would also echo @Mathieu Guindon's comments regarding the use of local variables in lieu of inline DLookup expression.

Consider how much easier the following code is to read -

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

You could also surround the DLookup expressions with Nz expressions to catch null values, and test for empty strings before proceeding.

score 0 · Answer 2 · answered Aug 30 '19 at 07:44

0

It is little known, but you can also use SQL in the filter:

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

answered Aug 30 '19 at 07:44

Gustav

53,498
7
29
55

Nested Dlookup VBA

2 Answers2