DCount using two Criteria

Question

I have an Access Form with two combo boxes, PO Number and Vendor Number. The Vendor Number will automatically filter to show only those who match the chosen PO Number (PO Numbers are sadly not unique; multiple unrelated vendors may have 2180965 as their PO Number, for example).

Some POs are exempt from our inspection process, and we keep a list of them in a table called ZeroAudit. That table contains both the PO Number and Vendor Number for the exempt delivery.

Before I found out that PO Numbers weren't unique, I was using this code to tell our team whether a given PO requires inspection. How can I update it to require that both the PO Number and Vendor Number match the ZeroAudit table entry to be exempt from QA?

Private Sub VendorNumber_AfterUpdate()
Dim IntX As Integer
IntX = DCount("*", "ZeroAudit", "PONumber=" & Chr(34) & Me.PONumber.Value & Chr(34))
Debug.Print Me.PONumber.Value

If IntX = 0 Then
  MsgBox "Needs QA"
  Me.PONumber.SetFocus
ElseIf IntX > 0 Then
  MsgBox "Does NOT Need QA — DIM and RETAIL CHECK REQUIRED"
  Me.PONumber.SetFocus

End If
End Sub

Lee Mac Lee Mac · Accepted Answer · 2019-08-27T21:38:33

Assuming that your table/query ZeroAudit contains a field called VendorNumber, and that your form also contains a field called VendorNumber, then the below present two possible methods:

Using DCount:

If DCount("*", "ZeroAudit", "PONumber='" & Me.PONumber & "' and VendorNumber='" & Me.VendorNumber & "'") = 0 Then
    MsgBox "Needs QA"
Else
    MsgBox "Does NOT Need QA — DIM and RETAIL CHECK REQUIRED"
End If
Me.PONumber.SetFocus

Using DLookup:

If IsNull(DLookup("PONumber", "ZeroAudit", "PONumber='" & Me.PONumber & "' and VendorNumber='" & Me.VendorNumber & "'")) Then
    MsgBox "Needs QA"
Else
    MsgBox "Does NOT Need QA — DIM and RETAIL CHECK REQUIRED"
End If
Me.PONumber.SetFocus

Note that the ElseIf is not required since the return of DCount will either be positive or zero; hence if you are testing for zero, all other possibilities are covered by the Else statement.

DCount using two Criteria

1 Answers