Hi JN:
Amy's suggestions are right... key and index fields are really wonderful in
keeping duplicates from being entered. There are other methods that I have
collected over the years, though, that appeared in these discussion groups
which I thought were neat.
1) In method 1 you would need to put into the BeforeInsert event on the text
control. It could use DLookUp to find, say, the entered SSN in a table,
something like
txtSSN_BeforeInsert(Cancel as Integer)
Cancel = FALSE
If Me!txtDupSSNOK Then
Exit Sub ' because you don't care if it's a dup
Else
If IsNull(DLookUp("SSN","YourTableName","SSN='" & Me!txtSSN & "'") Tjem
Exit Sub ' No dup found
End If
End If
Cancel=TRUE ' Cancel the insertion
Msgbox "This SSN already exists, please reenter"
End Sub
2) You can also use the UNDO function to cancel (and reverse) an event if
you put your code into the AfterInsert event.
3) Another rather sophisticated way is to capture the actual error code that
occurs if you use an indexed field and use the undo event. Here's a sample
of such code- an example I used to answer a question where the person wanted
the option of deleting the entry based on which has the lowest value in
another non-key field. You can alternatively check for the one with the
newest entry date, etc:
Option Compare Database
Dim dbs As Database
Dim rst As Recordset
Dim ii As Long, iii As Long
Dim SQLStmt As String
Option Explicit
Private Sub CheckIndex()
On Error GoTo CheckIndex_Error
' first you gotta get the values of the new record that you'll work with
later on
If Not IsNull(Me!TransID) And Not IsNull(Me!Value) Then
ii = Me!TransID
iii = Me!Value
DoCmd.RunCommand acCmdSaveRecord
End If
CheckIndex_Error:
' here we trap the error that there exist 2 similar records (I take it that
the [ID] field is a key field!)
If Err.Number = 3022 Then
MsgBox "Duplicate record. We'll delete the lower valued record." ' a
white lie....
Me.Undo
Set dbs = CurrentDb()
SQLStmt = "SELECT ExampleTable.* FROM ExampleTable WHERE
ExampleTable.[TransID]= ii;"
Set rst = dbs.OpenRecordset(SQLStmt, dbOpenDynaset)
With rst
If !Value < iii Then
!Value = iii
End If
.Close
End With
Else
MsgBox Err.Number & " " & Err.Description, , _
"Private Sub CheckIndex()"
End If
Exit Sub
End Sub
Regards,
Al
