Checking for duplication when adding new data

[RuthSBT]

I am very new to Access 2016 and learning as I go along. I have transferred a fairly large amount of data from excel to access, and want to know if I can replicate one of the functionalities of Excel. The way we had the spreadsheet set up was that, if we were adding trainee details that included an email address already in the spreadsheet, Excel would automatically highlight it in red for us to check out. Is there a way of doing this in access?



I hope this makes sense!

Thanks

Ruth

[Bulzie]

You could use DLookUp function for this to look in your table for that email address. In the AfterUpdate Event of that email field on the form, use something llike.

If Not Isnull(DLookup("RecID", "YourTable", "TraineeEmail = '" & Me.TraineeEmail & "'")) Then

Msgbox "Email is a duplicate"
me.TraineeEmail.Forecolor = vbRed
Me.TraineeEmail.SetFocus
End

End If

There is also a Find Duplicates Query Wizard that can help you find these in your table after the fact if you choose to do that.

[CJ_London]

yes. A number of ways

nearest to the excel functionality is in the form, use conditional formatting on the email control combined with a dcount function to produce a red background -

something like
Expression is...dcount("*","myTable","[Email]='" & [Email] & "'")>1
and set the backcolor to red

other alternatives include setting the index property of the field to indexed, no duplicates which will prevent users adding a duplicate

or in code behind the email control after update event you can put something which outputs a message which can tell the user not only there is a duplicate, but also which record the duplicate can be found - even filter the form temporarily so a direct comparison can be made