Checking for duplicates

[John Cole, Jr.]

I have searched all the groups I can, and I still haven't been able
to
come up the solution I need. I have the following problem.

In my form named sbfrmSpoolList, I am entering a job, spool and
revision number. My table is indexed properly to not allow
duplicates, but I would like teh user to be notified that they are
typing a duplicate via a message box, then I woulld the update of the
record to be cancelled. I have tried the DLookup, but I'm not sure
how to input it with multiple fields. I have set the the
beforeupdate
event on the form, not the control. I would like to know the syntax
for checking for duplicates in the following fields txtBaseJob2,
txtSpoolNo, txtSpoolRev.


Any help would appreciated.


Thanks, JC

 

[Larry Linson]

"Not allow duplicates" of what... all three fields, or specific fields? Are
txtBaseJob2, txtSpoolNo, and txtSpoolRev the names of Fields in your Table,
or of Controls in your Form, or both? If those are the names of Controls on
your Form, what are the names of the corresponding Fields in the Table?

Assuming the names starting with "txt" refer to Text Boxes on your Form, and
the Fields are the same but without the "txt" prefix, e.g., BaseJob2, with
BaseJob2 being a Text Field in the Table, and SpoolNo and SpoolRev being
numeric (to test, I used Long Integer) the following code in the
BeforeUpdate event of the Form should do what you want:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset 'Dimension a Recordset Object
variable
If Form.NewRecord = True Then
Set rs = Me.RecordsetClone 'Set to the Form's Recordset
Clone
' Try to find Record with same BaseJob2, SpoolNo, and SpoolRev
rs.FindFirst "[BaseJob2] = """ & Me!txtBaseJob2 & """ AND [SpoolNo]
= " & Me!txtSpoolNo & " AND [SpoolRev] = " & Me!txtSpoolRev
If Not rs.NoMatch = True Then 'Already a record with same
Job/Spool/Rev
MsgBox "Attempting to Add Duplicate -- Cancelled"
Cancel = True
Else 'This is
a new record, let it update
Cancel = False
End If
End If
Set rs = Nothing 'Destroy Recordset Object
Exit Sub 'Leave Sub Procedure
End Sub

You'll need to substitute your DB's actual names, and, you may want to Undo
the previous entries. I typically do not Undo if there's more than one Field
involved because chances are good that changing just one Control will
correct the problem and I don't want to clear all of them, so the user has
to reenter everything.

Larry Linson
Microsoft Office Access MVP