Check for Duplicate Records

[Rillo]

I have a table in which there are records containing serial numbers and other
data describing items in the record.
The serial number is supposed to be unique to each record and is the record
key.

When I try to enter a record with a duplicate serial number, Access displays
two messages when I leave my data entry form.

The messages are

“The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship"
(which is apparently Error 3022).

and a message to the effect that I cannot save the record at this time.

This is exactly what I want to happen , however

a. could you please advise me on how I can create some code that would
inform the user in a user-friendly message that he/she is attempting to
allocate a serial number that has already been used ?
b. to which event the code should be attached?
c. do I need to use VB or can I create a macro to achieve this?

I tried the following code in various events linked to the serial number
field, but this did not work. However, I am not very good at creating VBcode!!

If (Me.RecordsetClone.RecordCount) > 0 Then
MsgBox "This serial number has been used previously", , "TEST"
Cancel = True
End If

End Sub

I am using Access 2002 with Access 2000 file formats

 

[Jeanette Cunningham]

Hi Rillo,
good place to put code to check for duplicates is the Before Update event
for the form (note this is not the same as the control's before update
event).

Here is some sample code for a number field/control called ClientID -->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[ClientID] = " & Me.ClientID
'Debug.Print strCriteria
If Me.NewRecord Or Me.ClientID <> Me.ClientID.OldValue Then
If DCount("*", "tblRef", strCriteria) > 0 Then
Cancel = True
End If
End If

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

This is exactly what I want to happen , however

a. could you please advise me on how I can create some code that would
inform the user in a user-friendly message that he/she is attempting to
allocate a serial number that has already been used ?
b. to which event the code should be attached?
c. do I need to use VB or can I create a macro to achieve this?

You should use the BeforeUpdate event of the serial number control to do so
(or, in some circumstances, the BeforeUpdate event of the Form). I would use
VBA code, though in 2007 it may be ok to use macros - I'm not well versed in
them, though.

The VBA code for a textbox named txtSerialNo, to check for duplicates in the
field SerialNo in table MyTable, would be

Private Sub txtSerialNo_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("SerialNo", "MyTable", _
"SerialNo = " & Me!txtSerialNo) Then
MsgBox "This serial number has already been entered", vbOKOnly
Cancel = True
End If
End Sub

If the serial number is a Text datatype, you need some quotemarks:

If Not IsNull(DLookUp("SerialNo", "MyTable", _
"SerialNo = '" & Me!txtSerialNo & "'") Then

You can get fancier, offering to jump to the record for that serial number,
etc. if you wish - post back for more details.

 

[Rillo]

Hi Jeanette

Thanks for this.
Please excuse my ignorance - as I said Vb code is not something with which I
am very familiar.

I assume that in your code I can replace ClientID with SerialNumber (the
name of my field). However, what is tblRef and where do I place the code re
my MsgBox?

Thanks again

Hi Rillo,
good place to put code to check for duplicates is the Before Update event
for the form (note this is not the same as the control's before update
event).

Here is some sample code for a number field/control called ClientID -->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[ClientID] = " & Me.ClientID
'Debug.Print strCriteria
If Me.NewRecord Or Me.ClientID <> Me.ClientID.OldValue Then
If DCount("*", "tblRef", strCriteria) > 0 Then
Cancel = True
End If
End If

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a table in which there are records containing serial numbers and
other
data describing items in the record.
The serial number is supposed to be unique to each record and is the
record
key.

When I try to enter a record with a duplicate serial number, Access
displays
two messages when I leave my data entry form.

The messages are

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship"
(which is apparently Error 3022).

and a message to the effect that I cannot save the record at this time.

This is exactly what I want to happen , however

a. could you please advise me on how I can create some code that would
inform the user in a user-friendly message that he/she is attempting to
allocate a serial number that has already been used ?
b. to which event the code should be attached?
c. do I need to use VB or can I create a macro to achieve this?

I tried the following code in various events linked to the serial number
field, but this did not work. However, I am not very good at creating
VBcode!!

If (Me.RecordsetClone.RecordCount) > 0 Then
MsgBox "This serial number has been used previously", , "TEST"
Cancel = True
End If

End Sub

I am using Access 2002 with Access 2000 file formats

 

[Rillo]

John

I tried the following code in the BeforeUpdate event on the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("SerialNumber", "Property Items", _
"SerialNumber = '" & Me!txtSerialNumber & "'")) Then
MsgBox "This serial number has already been entered", vbOKOnly
Cancel = True
End If
End Sub

I then tried the code in the BeforeUpdate event of the Serial Number box on
the form i.e. Private Sub SerialNumber_BeforeUpdate(Cancel As Integer)

However, I still cannot get the message to display.

What have I done wrong?

 

[Jeanette Cunningham]

Here are the explanations.

Assuming that SerialNumber is a text field in the table-->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[SerialNumber ] = """ & Me.SerialNumber & """"
'Debug.Print strCriteria
If Me.NewRecord Or Me.SerialNumber <> Me.SerialNumber .OldValue Then
If DCount("*", "[NameOfTable]", strCriteria) > 0 Then
Cancel = True
Msgbox "This serial number has already been used. " _
& "Correct your entry or press Esc to cancel."
End If
End If

End Sub


Replace NameOfTable with the name of the table that has the field
SerialNumber.
If your table has spaces or other odd characters in its name, use square
brackets like this
[NameOfTable].



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette

Thanks for this.
Please excuse my ignorance - as I said Vb code is not something with which
I
am very familiar.

I assume that in your code I can replace ClientID with SerialNumber (the
name of my field). However, what is tblRef and where do I place the code
re
my MsgBox?

Thanks again

Hi Rillo,
good place to put code to check for duplicates is the Before Update event
for the form (note this is not the same as the control's before update
event).

Here is some sample code for a number field/control called ClientID -->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[ClientID] = " & Me.ClientID
'Debug.Print strCriteria
If Me.NewRecord Or Me.ClientID <> Me.ClientID.OldValue Then
If DCount("*", "tblRef", strCriteria) > 0 Then
Cancel = True
End If
End If

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a table in which there are records containing serial numbers and
other
data describing items in the record.
The serial number is supposed to be unique to each record and is the
record
key.

When I try to enter a record with a duplicate serial number, Access
displays
two messages when I leave my data entry form.

The messages are

"The changes you requested to the table were not successful because
they
would create duplicate values in the index, primary key, or
relationship"
(which is apparently Error 3022).

and a message to the effect that I cannot save the record at this time.

This is exactly what I want to happen , however

a. could you please advise me on how I can create some code that would
inform the user in a user-friendly message that he/she is attempting to
allocate a serial number that has already been used ?
b. to which event the code should be attached?
c. do I need to use VB or can I create a macro to achieve this?

I tried the following code in various events linked to the serial
number
field, but this did not work. However, I am not very good at creating
VBcode!!

If (Me.RecordsetClone.RecordCount) > 0 Then
MsgBox "This serial number has been used previously", , "TEST"
Cancel = True
End If

End Sub

I am using Access 2002 with Access 2000 file formats

 

[Rillo]

Thank You

Here are the explanations.

Assuming that SerialNumber is a text field in the table-->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[SerialNumber ] = """ & Me.SerialNumber & """"
'Debug.Print strCriteria
If Me.NewRecord Or Me.SerialNumber <> Me.SerialNumber .OldValue Then
If DCount("*", "[NameOfTable]", strCriteria) > 0 Then
Cancel = True
Msgbox "This serial number has already been used. " _
& "Correct your entry or press Esc to cancel."
End If
End If

End Sub


Replace NameOfTable with the name of the table that has the field
SerialNumber.
If your table has spaces or other odd characters in its name, use square
brackets like this
[NameOfTable].



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette

Thanks for this.
Please excuse my ignorance - as I said Vb code is not something with which
I
am very familiar.

I assume that in your code I can replace ClientID with SerialNumber (the
name of my field). However, what is tblRef and where do I place the code
re
my MsgBox?

Thanks again

Hi Rillo,
good place to put code to check for duplicates is the Before Update event
for the form (note this is not the same as the control's before update
event).

Here is some sample code for a number field/control called ClientID -->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

strCriteria = "[ClientID] = " & Me.ClientID
'Debug.Print strCriteria
If Me.NewRecord Or Me.ClientID <> Me.ClientID.OldValue Then
If DCount("*", "tblRef", strCriteria) > 0 Then
Cancel = True
End If
End If

End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a table in which there are records containing serial numbers and
other
data describing items in the record.
The serial number is supposed to be unique to each record and is the
record
key.

When I try to enter a record with a duplicate serial number, Access
displays
two messages when I leave my data entry form.

The messages are

"The changes you requested to the table were not successful because
they
would create duplicate values in the index, primary key, or
relationship"
(which is apparently Error 3022).

and a message to the effect that I cannot save the record at this time.

This is exactly what I want to happen , however

a. could you please advise me on how I can create some code that would
inform the user in a user-friendly message that he/she is attempting to
allocate a serial number that has already been used ?
b. to which event the code should be attached?
c. do I need to use VB or can I create a macro to achieve this?

I tried the following code in various events linked to the serial
number
field, but this did not work. However, I am not very good at creating
VBcode!!

If (Me.RecordsetClone.RecordCount) > 0 Then
MsgBox "This serial number has been used previously", , "TEST"
Cancel = True
End If

End Sub

I am using Access 2002 with Access 2000 file formats

 

[Xiaoli]

Here are the explanations.

Assuming that SerialNumber is a text field in the table-->

Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String

    strCriteria = "[SerialNumber ] = """ & Me.SerialNumber & """"
    'Debug.Print strCriteria
    If Me.NewRecord Or Me.SerialNumber <> Me.SerialNumber .OldValue Then
        If DCount("*", "[NameOfTable]", strCriteria) > 0 Then
            Cancel = True
            Msgbox "This serial number has already been used." _
            & "Correct yourentryor press Esc to cancel."
        End If
    End If

End Sub

Replace NameOfTable with the name of the table that has the field
SerialNumber.
If your table has spaces or other odd characters in its name, use square
brackets like this
[NameOfTable].

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Jeanette

Thanks for this.
Please excuse my ignorance - as I said Vb code is not something with which
I
am very familiar.

I assume that in your code I can replace ClientID with SerialNumber (the
name of my field). However, what is  tblRef and where do I place the code
re
my MsgBox?

Thanks again

Hi Rillo,
good place to put code to check for duplicates is the Before Update event
for the form (note this is not the same as the control's before update
event).
Here is some  sample code for a number field/control called ClientID-->
Prviate Sub Form_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String
    strCriteria = "[ClientID] = " & Me.ClientID
    'Debug.Print strCriteria
    If Me.NewRecord Or Me.ClientID <> Me.ClientID.OldValue Then
        If DCount("*", "tblRef", strCriteria) > 0 Then
            Cancel = True
        End If
    End If
End Sub
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
I have a table in which there are records containing serial numbers and
other
data describing items in the record.
The serial number is supposed to be unique to each record and is the
record
key.
When I try to enter a record with aduplicateserial number, Access
displays
two messages when I leave my dataentryform.
The messages are
"The changes you requested to the table were not successful because
they
would createduplicatevalues in the index, primary key, or
relationship"
(which is apparently Error 3022).
and a message to the effect that I cannot save the record at this time.
This is exactly what I want to happen , however
a. could you please advise me on how I can create some code that would
inform the user in a user-friendly message that he/she is attemptingto
allocate a serial number that has already been used ?
b. to which event the code should be attached?
c. do I need to use VB or can I create a macro to achieve this?
I tried the following code in various events linked to the serial
number
field, but this did not work. However, I am not very good at creating
VBcode!!
If (Me.RecordsetClone.RecordCount) > 0 Then
       MsgBox "This serial number has been used previously",, "TEST"
       Cancel = True
   End If
End Sub
I am using Access 2002 with Access 2000 file formats

- Show quoted text -

Jeanne,

I have the same question as Rillo, however, I have more than one
primary keys. How I can modify your codes so I can get the warning
message about the duplicates after the cursor leaves the field of the
last primary key?

Thanks,
Shirley