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!

 

[Pat Hartman]

You can use DLookup() or DCount() in the BeforeUpdate event of the combo. If you find the recipient already exists and so you don't want to enter him again, cancel the update.

If DCount("*","mytable", "RecipientID = " & Me.cboRecipientID > 0 Then
    Msgbox "Recipient already exists.  Please choose another.",vbokOnly
    Cancel = True
    Me.cboRecipientID.Undo
    Me.cboRecipientID.SetFocus
    Exit Sub
End If

 

[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"

 

[Pat Hartman]

I would never use a domain function in a query or a code loop. But, one-off in a form is acceptable and you would never notice the difference in performance. Keep in mind that most posters here have little VBA experience so unless VBA is actually required, I would go with what Access gives us when efficiency is not a concern.

 

[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!

 

[Pat Hartman]

"Me." is only used in the class modules of forms and reports and refers to properties of a form/report. You are trying to use it to qualify the the column name in the [Recipient Information] table. Two other problems - whenever you use embedded spaces or special characters in a name, you MUST encapsulate the name in square brackets. Best practice is to not use spaces and special characters at all. Use only a-z, A-Z, 0-9, and the underscore. Third, if the Entity_Name field is text, it's value needs to be enclosed in single quotes. If the name might include single quotes such as O'Brien, then you must use double quots.

If DCount("*", "Recipient_Information", "Entity_Name = '" & Me.Entity_Name & "'")

 

[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

 

[Pat Hartman]

The code should only run if the Entity_Name field is modified. Do you have code in some other event that is modifying Entiry_Name? Obviously, if the code runs for existing records it will raise the error.