Validation check against another table

[RSS705]

Hi there,

I have a lookup which selects a 'Recipient'.

I need to add a check to see if this 'Recipient' (user selected) already exists within another table.

If so, I need a pop-up to appear.
If not, I want the user to continue unimpeded.

Thanks as always!

 

[michaeljryan78]

I may have to disagree here Pat. Domain functions may be a bit resource intensive espcially when selecting the entire table (assuming that the table is not indexed properly.) I would perhaps open a recordset with a ciriteria and see the recordset returns records, if ot does, pop the message.

DLOOKUP is great though, just "expensive"

 

[RSS705]

Edited: Deleted, follow up question not required (for now)

Thanks

 

[RSS705]

I'm having a bit of a struggle... Here's the code:

Private Sub Entity_Name_AfterUpdate()
If DCount("*", "Recipient Information", "Me.Entity Name = Me.Entity_Name") Then
MsgBox "Recipient already exists. Please choose another.", vbOKOnly
Cancel = True
Me.Entity_Name.Undo
Me.Entity_Name.SetFocus
Exit Sub
End If
End Sub

The problem is highlighted in red. I need the criteria to check the Entity Name field within the Recipient Information table, and compare it to the currently entered text in the Entity Name field on the current form, which is called Recipient Information.

To reiterate this once again for clarity:
Table - Recipient Information, Field - Entity Name

Compared to

Form - Recipient Information, Text Field - Entity Name

Thanks so much for your help!

 

[RSS705]

Hello again Pat, thank you for your help, but the form still isn't working as desired.

When you clear the Recipient Table, and enter the first recipient. The code works fine, and doesn't trigger. But, when you enter a second recipient, even with a completely different Entity Name, the code triggers and says there is a repeat. Do you have any idea why this would be happening?

Private Sub Entity_Name_AfterUpdate()
If DCount("*", "[Recipient Information]", "Entity_Name = '" & Me.Entity_Name & "'") Then
MsgBox "Recipient already exists. Please choose another.", vbOKOnly
Cancel = True
Me.Entity_Name.Undo
Me.Entity_Name.SetFocus
Exit Sub
End If
End Sub