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
278 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

809 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