How can I avoid duplicate entries?

J

Jane

How can I make sure that users do not enter a person more than once on my
database?

Is it possible to programe a message box to pop up and warn a user that (for
example) a person's family name has already been entered in the database? And
if so, how can I go about doing it?

Any help greatly apreciated.
 
D

Dirk Goldgar

Jane said:
How can I make sure that users do not enter a person more than once
on my database?

Is it possible to programe a message box to pop up and warn a user
that (for example) a person's family name has already been entered in
the database? And if so, how can I go about doing it?

Any help greatly apreciated.
Click to expand...

It's certainly possible, so long as you can define what constitutes a
duplicate, or at least a probable duplicate. You can set a unique index
on a field or a combination of fields, which will raise an error if you
try to add a record that exactly duplicates the fields in the index.
However, you may need more flexibility. For example, it's unlikely you
would want to prohibit two people with the same family name from being
recorded in the database.

Here's some example code for a form's BeforeUpdate event that checks for
a duplicate name and offers the user the option of saving the record or
going to the apparent duplicate.

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("ID", "MyTable", _
"FamilyName = " & Chr(34) & Me!FamilyName & Chr(34))

If Not IsNull(varID) Then

If MsgBox( _
"A record was found for the same family name. " & _
"Do you want to cancel these changes and go to
that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "ID = " & varID
End If

End If

End If

End Sub
'----- end of example code -----

In this particular example, where we're only checking the family name,
it would probably be better to check in the AfterUpdate event of the
FamilyName text box, rather than waiting until a completed record is
about to be saved. But the above is a general example.
 
J

Jane

Thanks very much Dirk. That looks like it will solve my problem.
I'll just get working on it now.

Jane
 
J

Jane

Dirk
I've just used your code and it worked very well. it did exactly what I
wanted it to do. Thanks very much!
 
T

Telobamipada

Hi Dirk, I was in need of this same code and saw that it had worked for
someone else... each time I try to run it it finds a problem with the line

Me.Recordset.FindFirst "ID = " & varID

Any ideas why?
 
T

Telobamipada

Maybe this will help... My table name is "Emergency Dept Log" the form name
is "Data Entry Form" and the field is named "ID"...
This is what I changed the code to...

Private Sub ID_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("ID", "Emergency Dept Log", _
"ID = " & Chr(34) & Me!ID & Chr(34))

If Not IsNull(varID) Then

If MsgBox( _
"A record was found with the same Account Number. " & _
"Do you want to cancel these changes and go to that
" & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "ID = " & varID
End If

End If

End If

End Sub
 
D

Dirk Goldgar

Telobamipada said:
Maybe this will help... My table name is "Emergency Dept Log" the
form name is "Data Entry Form" and the field is named "ID"...
This is what I changed the code to...

Private Sub ID_BeforeUpdate(Cancel As Integer)

Dim varID As Variant

If Me.NewRecord Then

varID = DLookup("ID", "Emergency Dept Log", _
"ID = " & Chr(34) & Me!ID & Chr(34))

If Not IsNull(varID) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes
and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Possible Duplicate") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "ID = " & varID
End If

End If

End If

End Sub
Click to expand...

From your DLooklup expression, it looks like your ID field is text, not
numeric. If so, you'll have to change the FindFirst line like this:

Me.Recordset.FindFirst "ID = " & Chr(34) & varID & Chr(34)

Note: if your ID field won't contain the single-quote character ('),
you can make that line a little simpler:

Me.Recordset.FindFirst "ID = '" & varID & "'"
 
T

Telobamipada

Dirk, I was a little more optimistic when you mentioned the ID field being
text... it is. The account used here begins with an Uppercase Letter followed
by 5 numeric values and it does have an input mask. However, it still didn't
work. The error I'm getting is a "Compile Error, Method or data member not
found" and while debugging it goes to the code and highlights the word
"Recordset"... This is what I am now try to run here.

Private Sub Account_BeforeUpdate(Cancel As Integer)

Dim varAccount As Variant

If Me.NewRecord Then

varAccount = DLookup("Account", "Table1", _
"Account = " & Chr(34) & Me!Account & Chr(34))

If Not IsNull(varAccount) Then

If MsgBox( _
"A record was found with the same Account Number. " & _
"Do you want to cancel these changes and go to that " & _
"record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Account Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Account = '" & varAccount & "'"
End If

End If

End If

End Sub

Also, in testing, If I remove the "Recordset/FindFirst" line it acts
perfect... It will carry out the "Undo" but of course I'm not able to go to
the duplicate record with that account.

I really appreaciate your help on this, what a headache!
 
D

Dirk Goldgar

Telobamipada said:
Dirk, I was a little more optimistic when you mentioned the ID field
being text... it is. The account used here begins with an Uppercase
Letter followed by 5 numeric values and it does have an input mask.
However, it still didn't work. The error I'm getting is a "Compile
Error, Method or data member not found" and while debugging it goes
to the code and highlights the word "Recordset"... This is what I am
now try to run here.

Private Sub Account_BeforeUpdate(Cancel As Integer)

Dim varAccount As Variant

If Me.NewRecord Then

varAccount = DLookup("Account", "Table1", _
"Account = " & Chr(34) & Me!Account & Chr(34))

If Not IsNull(varAccount) Then

If MsgBox( _
"A record was found with the same Account Number.
" & _ "Do you want to cancel these changes and go
to that " & _ "record instead?", _
vbQuestion + vbYesNo, _
"Duplicate Account Found") _
= vbYes _
Then
Cancel = True
Me.Undo
Me.Recordset.FindFirst "Account = '" & varAccount &
"'" End If

End If

End If

End Sub

Also, in testing, If I remove the "Recordset/FindFirst" line it acts
perfect... It will carry out the "Undo" but of course I'm not able to
go to the duplicate record with that account.

I really appreaciate your help on this, what a headache!
Click to expand...

No problem, I don't think anything major is wrong. But I wonder if
perhaps you are running Access 97. The Recordset property of the Form
object was only made available with Access 2000, so if you're using
Access 97 you should modify the code by replacing this:

Me.Recordset.FindFirst "Account = '" & varAccount & "'"

with this:

With Me.RecordsetClone
.FindFirst "Account = '" & varAccount & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

If you're not using Access 97, on the other hand, we'll have to look
deeper.
 
T

Telobamipada

Dirk, Thanks Bro, your the man! I didn't think that in this case it would
matter but yes I'm running Office 97 on this atequated computer at home...
your solution worked perfect. However, I am running Office 2002 at work and
thus the original code you provided should work there right? Thanks again.
 
D

Dirk Goldgar

Telobamipada said:
Dirk, Thanks Bro, your the man! I didn't think that in this case it
would matter but yes I'm running Office 97 on this atequated computer
at home... your solution worked perfect. However, I am running
Office 2002 at work and thus the original code you provided should
work there right? Thanks again.
Click to expand...

You can use the code I originally provided or, to simplify things, you
could just use the Access 97 version at work, too. It'll still work in
that Access 2000 or later.
 
S

Sondra

I've reviewed this over and over again and its not working for me or I'm not
understanding. Here is my problem:

I have a field title WBNNumber. This field can not contain duplicates
within the database. I've set the index field to No Duplicates; however, I
want to change the "generic" error message to a "user friendly" message for
my users.

Please give me guidance...I am very novice when it comes to code.

Thanks.
 
Top