DLookup for duplicate entry

[Hafa1961]

I have a form After Update event for a field that checks to see if a Supplier Ref has been used before. If so, it alerts the user of a duplicate entry.

But what I need the code to do is to check the Supplier Ref against the SupplierID for that record. If the duplicate Supplier Ref was not used for the same SupplierID then go ahead and allow the user to enter the Supplier Ref. If the Supplier Ref was previously used for the same SupplierID Then alert the user of a duplicate entry.

Private Sub SupplierRef_AfterUpdate()
Dim SupplierRef As Variant

SupplierRef = DLookup("[BoughtInChargeID]", "Bought In Charges", "[SupplierRef] = Forms![Bought In Charges]![SupplierRef]")
If Not IsNull(SupplierRef) Then
MsgBox "A bought in charge invoice "
Me.SupplierRef.SetFocus
Me.SupplierRef = ""


End If

End Sub

Fields in the Bought In Charges Table:
BoughtInChargeID
SupplierRef
SupplierID
etc..

Any help would be appreciated....

 

[TheAceMan1]

How are ya Hafa1961 . . .

Perhaps this . . .

[blue]   Dim ctl As Control, SupplierRef, Criteria As String
   
   Set ctl = Forms![Bought In Charges]![SupplierRef]
   
   Criteria = "[SupplierRef] = " & ctl & " AND " & _
              "[SupplierID] = " & ctl
   SupplierRef = DLookup("[BoughtInChargeID]", "Bought In Charges", Criteria)
   
   If Not IsNull(SupplierRef) Then
      MsgBox "This is a duplicate in Charge Invoice "
   End If
   
   Set ctl = Nothing[/blue]

See Ya! . . . . . .

 

[Hafa1961]

Thanks for the reply AceMan,
I figured it out....

Private Sub SupplierRef_AfterUpdate()
Dim intAnswer As Integer
Dim txtSupplierRef As Variant

txtSupplierRef = DLookup("[BoughtInChargeID]", "Bought In Charges", "[SupplierRef] = Forms![Bought In Charges]![SupplierRef]")
If Not IsNull(SupplierRef) Then
intAnswer = MsgBox("This Supplier Reference has already been allocated to Bought In Charge Record: " & " BICID " & [txtSupplierRef] & "" & Chr(13) & Chr(10) & "Do you want to continue using this Reference No?", vbYesNo + vbQuestion, "Duplicate Supplier Reference Found!")

Select Case intAnswer
Case vbYes
MsgBox "Please be sure this Reference Number is being allocated to a different Supplier before continuing.", vbInformation, "Allowing duplicate entry!"
Me.OurRef.SetFocus

Case vbNo
MsgBox "Removing current entry please enter another Reference Number", vbOKOnly, "Enter a new Reference Number."
Me.SupplierRef.SetFocus
Me.SupplierRef = ""
End Select

End If

End Sub