Prevent Duplicates

[Dave K]

Hello. I would like to find out how to use the Before
Update to prevent duplicate entries. I am presently
using "Yes (no duplicates)" but I enter the whole form
before finding the duplicate exists. I have found a
couple examples on the net but they don't work for me
probably because I'm not entering my specific details
correctly. The name of my Table is DK EI Grand Table and
the name of my Form is DK EI Main Entry. I would
immensely appreciate details on how to get this to work
for me. I assume that in the design view of my Form I
click on the first entry that I want to prevent
duplicates and then open properties, Before Update and
select Code Builder. Thanks!!
Dave K

 

[John Vinson]

Hello. I would like to find out how to use the Before
Update to prevent duplicate entries. I am presently
using "Yes (no duplicates)" but I enter the whole form
before finding the duplicate exists. I have found a
couple examples on the net but they don't work for me
probably because I'm not entering my specific details
correctly. The name of my Table is DK EI Grand Table and
the name of my Form is DK EI Main Entry. I would
immensely appreciate details on how to get this to work
for me. I assume that in the design view of my Form I
click on the first entry that I want to prevent
duplicates and then open properties, Before Update and
select Code Builder. Thanks!!
Dave K

You're very much on the right track. But... what constitutes a
duplicate in your case? Is it a single field, a combination of a
couple of fields, or what? The code will be possible in any case, but
simpler if it's one field.

For example, if there is one Text field DK_ID which is manually
entered and which must not be duplicated, you could use code like this
in the BeforeUpdate event of the textbox txtDK_ID which is bound to
the field DK_ID (note: I suggest not using blanks in table or
fieldnames, and renaming controls to a name other than the name of the
field):

Private Sub txtDK_ID_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[DK_ID]", [DK EI Grand Table], _
"[DK_ID] = '" & Me!txtDK_ID & "'") Then
MsgBox "This DK_ID has already been entered", vbOKOnly
Cancel = True
End If
End Sub

You can get fancier (e.g. having a Yes/No/Cancel button rather than an
OK button only and doing different things with it) but the key is that
you set the Cancel operand to True to cancel the update.

John W. Vinson[MVP]

 

[Dave K]

I really appreciate the information John! I'm steps
closer to figuring this out. I simply copied your code
into my Code Builder but it "didn't take". I probably
need to have an Access person take a look at my database
and Form. Thanks!
Dave K

-----Original Message-----

Hello. I would like to find out how to use the Before
Update to prevent duplicate entries. I am presently
using "Yes (no duplicates)" but I enter the whole form
before finding the duplicate exists. I have found a
couple examples on the net but they don't work for me
probably because I'm not entering my specific details
correctly. The name of my Table is DK EI Grand Table and
the name of my Form is DK EI Main Entry. I would
immensely appreciate details on how to get this to work
for me. I assume that in the design view of my Form I
click on the first entry that I want to prevent
duplicates and then open properties, Before Update and
select Code Builder. Thanks!!
Dave K

You're very much on the right track. But... what constitutes a
duplicate in your case? Is it a single field, a combination of a
couple of fields, or what? The code will be possible in any case, but
simpler if it's one field.

For example, if there is one Text field DK_ID which is manually
entered and which must not be duplicated, you could use code like this
in the BeforeUpdate event of the textbox txtDK_ID which is bound to
the field DK_ID (note: I suggest not using blanks in table or
fieldnames, and renaming controls to a name other than the name of the
field):

Private Sub txtDK_ID_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[DK_ID]", [DK EI Grand Table], _
"[DK_ID] = '" & Me!txtDK_ID & "'") Then
MsgBox "This DK_ID has already been entered", vbOKOnly
Cancel = True
End If
End Sub

You can get fancier (e.g. having a Yes/No/Cancel button rather than an
OK button only and doing different things with it) but the key is that
you set the Cancel operand to True to cancel the update.

John W. Vinson[MVP]
.

 

[John Vinson]

I really appreciate the information John! I'm steps
closer to figuring this out. I simply copied your code
into my Code Builder but it "didn't take". I probably
need to have an Access person take a look at my database
and Form. Thanks!

Air code, untested: what was the symptom indicating that it didn't
take?


John W. Vinson[MVP]

 

[Guest]

I'm very likely doing several things wrong. My
understanding of this is quite limited (obviously). The
only symptom I guess is that entering a duplicate in the
field and then tabbing to the next field doesn't trigger
a response. The name of the field that I am trying to
prevent duplicates is called "cert #". I understand I
shouldn't be naming fields or tables with names that
include spaces or symbols. I'm worried that I can't
change the names of fields or my table without
catastrophic results.

 

[John Vinson]

I'm very likely doing several things wrong. My
understanding of this is quite limited (obviously). The
only symptom I guess is that entering a duplicate in the
field and then tabbing to the next field doesn't trigger
a response. The name of the field that I am trying to
prevent duplicates is called "cert #". I understand I
shouldn't be naming fields or tables with names that
include spaces or symbols. I'm worried that I can't
change the names of fields or my table without
catastrophic results.

If you ALWAYS use [cert #] *with the brackets* you should be all right
(well, at least until you want to upsize to SQL/Server or MySQL and
find that they won't accept the name).

Please post the actual code that you're using, and the error you're
getting if any. This should be fixable!

John W. Vinson[MVP]