• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

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

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
0
Molly152
Asked:
Molly152
  • 3
  • 3
1 Solution
 
peter57rCommented:
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
0
 
Molly152Author Commented:
Yes - sorry my explanation wasn't clear
0
 
peter57rCommented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Molly152Author 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?
0
 
peter57rCommented:
Are you wanting to check just in the subform records for that main record, or in all records in the subform recordsource?

Pete
0
 
Molly152Author Commented:
Hi Pete

I want to check all records in the subform recordsource
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now