DLookup and Message Box 2

[Hafa1961]

I have a textbox named SupplierRef and when a user enters a reference the code below checks to see if a duplicate reference has been entered. If so, a message box pops-up informing the user a duplicate reference as been entered and enter another.

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

txtSupplierRef = DLookup("[SupplierRef]", "Bought In Charges", "[SupplierRef] = Forms![Bought In Charges]![SupplierRef]")
If Not IsNull(txtSupplierRef) Then

GoTo refCheck
refCheck:
intAnswer = MsgBox("A bought in charge invoice has already been allocated to this supplier reference" & " -" & [txtSupplierRef] & "." & Chr(13) & Chr(10) & "Would you like to enter another Supplier Reference?", vbOKOnly + vbQuestion, "Duplicate Supplier Reference Found!")

Me.SupplierRef.SetFocus
Me.SupplierRef = ""
End If
End Sub

What I need this message box to also show is the original record number (BoughtInChargeID) for this SupplierRef. This way the user can refer/lookup the original invoice. Any help on how I would use the DLookup and Message Box to do this?

The table: Bought In Charges
BoughtInChargeID
BoughtInChargeDate
SupplierRef
Etc….


Any help would be appreciated….

 

[Remou]

I think that a small change is needed:

'You already have the Supplier ref, look up the BoughtInChargeID, 
'a result still means that a record exists, except you now have a useful answer.

txtSupplierRef = DLookup("[[b]BoughtInChargeID][/b]", "Bought In Charges", "[SupplierRef] = Forms![Bought In Charges]![SupplierRef]")
If Not IsNull(txtSupplierRef) Then

GoTo refCheck
refCheck:
intAnswer = MsgBox("A bought in charge invoice [b]" & txtSupplierRef & "[/b] has already been allocated to this supplier reference" & " -" & Forms![Bought In Charges]![SupplierRef].SupplierRef & "." & Chr(13) & Chr(10) & "Would you like to enter another Supplier Reference?", vbOKOnly + vbQuestion, "Duplicate Supplier Reference Found!")

If this is running on Forms![Bought In Charges], Me. is easier:
[tt]Me.SupplierRef[/tt]

As a ps, it may be a little confusing to have a question in the message box with an OKOnly button, people might wonder why there is no YesNo.

 

[Hafa1961]

Remou,
A very big thank you the help - that did the trick.