Check for duplicate entries, if unique allow new record, if duplicate close form and ask user to select from list

Molly152
Molly152 used Ask the Experts™
on
Hi

I have a customer record database that, when the user adds a new contact, I would like to run a check on telephone number to see if the record exists, if the record is unique the record can be added, if the telephone number is a duplicate I would like to discard the record and ask the user to chose from a list of contacts.

Is this possible, or maybe someone could suggest a better way around this problem?

Help appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Molly152,
Why would have a 'list' if you are not allowing duplicates?  

Do you just want to go to the record with that number?


Pete

Author

Commented:
Yes - sorry my explanation wasn't clear
OK - so given that this is a tel no, are you imposing any formatting rules for data entry, or can users enter anything they like.

(123) 456 7890
will not match with
123 456 7890

Assuming that numbers can be matched then use the afterupdate event procedure of the Phone no entry.(use your own phone no field name)


Private Sub Phone_AfterUpdate()

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
rs.FindFirst "Phone = '" & Me.Phone & "'"     ' your field name
If rs.NoMatch Then
' no duplicate
Exit Sub
Else
'duplicate found
MsgBox "Duplicate found - moving to existing record"
Me.Undo
Me.Bookmark = rs.Bookmark
End If

End Sub

Pete
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Hi Pete

Please excuse me coming back to you on this - I wonder is it possible to adapt the code to use on a sub form.

frmDetails contain details of company/organisation, the code works great on [OrgSwitchboard].  SubfrmContacts as a stand alone form - works great on [Telephone], but when I enter a telephone number as part of frmDetail it does not work.  Is there any way in which I can check for duplicate [Telephone] in the subform.

Is there a way in which I can award extra points for this?
Are you wanting to check just in the subform records for that main record, or in all records in the subform recordsource?

Pete

Author

Commented:
Hi Pete

I want to check all records in the subform recordsource

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial