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
270 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now