Solved

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

Posted on 2006-11-27
6
279 Views
Last Modified: 2008-03-03
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
Comment
Question by:Molly152
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 18020574
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
 

Author Comment

by:Molly152
ID: 18020638
Yes - sorry my explanation wasn't clear
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 18020865
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Molly152
ID: 18044542
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
 
LVL 77

Expert Comment

by:peter57r
ID: 18044599
Are you wanting to check just in the subform records for that main record, or in all records in the subform recordsource?

Pete
0
 

Author Comment

by:Molly152
ID: 18044732
Hi Pete

I want to check all records in the subform recordsource
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question