on exit code help 1

[patrichek]

Hi,
i use this code to tell my users they have entered a duplicate record. The problem is that this saves the record and the user "Must" delete it but normally they just move on which creates a bunch of duplicates.
How do i turn save "off" ?

Dim Db As DAO.Database
Dim rs As DAO.Recordset


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Contacts", dbReadOnly)

rs.FindFirst "Homephone=" & Chr(34) & HomePhone & Chr(34)

If Not rs.NoMatch Then
   MsgBox "You Have Added a duplicate record, please DELETE this record and run a search.", 64, "Warning"
End If

thanks!

 

[Remou]

Why not check the HomePhone in an event that can be cancelled, Before Insert, or Before Update for example?

 

[patrichek]

I'd love to but i don't know vba. could you show an example?

thanks

 

[Remou]

More or less the same as you have:

Private Sub HomePhone_BeforeUpdate(Cancel As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Contacts", dbReadOnly)

rs.FindFirst "Homephone=" & Chr(34) & HomePhone & Chr(34)

If Not rs.NoMatch Then
   MsgBox "You Have Added a duplicate record, please try again.", 64, "Warning"
    Cancel = True
    Me.Undo
End If
    
End Sub

 

[acent]

Checking the home phone may not catch all duplicates. take for instance, the the customer's phone changes due to moving, and the user enters the new phone, this wouldn't catch the true duplicate.

Is there another way to check the duplicate, say customerID or some other identifier. I'm assuming you're not using real data for the primary key.

Hope this helps.


"If you say you can, or you say you can't, you're right!"
-- Henry Ford

 

[patrichek]

great works like a charm!

no i don't use real data as my primary key, i use contactId autonumber.

thanks again!

 

[patrichek]

strange thing. it works on 2 of my 3 phone number fields.
i use home phone, work phone and cell. In the workphone field the code doesnt' work. no error or anything?

any idea why that would be?

here's my code on workphone:

Private Sub WorkPhone_Exit(Cancel As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Contacts", dbReadOnly)

rs.FindFirst "Workphone=" & Chr(34) & WorkPhone & Chr(34)

If Not rs.NoMatch Then
 MsgBox "You Have Added a duplicate record, please try again.", 64, "Warning"
    Cancel = True
    Me.Undo
End If
End Sub

 

[Remou]

This is a little more awkward that I thought. Are you checking each of the three number controls? If so, you need something in, say, the Before Insert event of the form. You can check each of the three numbers and cancel the insert if any of them exist.