Preventing duplicates?

[Guest]

I am aware of the process of preventing duplicates at Table level by "Yes (No
Duplicates) however it is at form level where my question relates. The first
field on my form contains the Primary key with no duplicates. Following this
there are many other fields on the form. What I want is a message saying that
"this is a duplicate value" at time of entry to the Primary field on the
form, not when you attempt to save the form. I have tried using a "Dlookup"
function in the validation rule but it is not working. Can anyone help?

 

[Ken Snell [MVP]]

You have your users entering data into the control that is bound to the
Primary Key field? You can use the BeforeUpdate event of that control to
test for uniqueness:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableName", "PrimaryKeyFieldName = " & _
Me.ControlName.Value) > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.ControlName.Undo
End If
End Sub

The above code assumes that the primary key field is a numeric data type. If
it's text, use this DCount expression:

If DCount("*", "TableName", "PrimaryKeyFieldName = '" & _
Me.ControlName.Value & "'") > 0 Then

 

[Guest]

I am still having trouble. My table is called 'TM - Client Details'. My
Primary Name Field is called 'Name Code'. My Control on my form is a combo
box called 'Name Code'. The Primary key is a text file. There are spaces
between the words in the respective names.

 

[Ken Snell [MVP]]

Post the code that you're trying to use... I can't see it from here < grin

!

 

[Guest]

Here is the code. It is probably incorrectly entered from your previous
answer. It assumes that the Primary key is text, the Control is a combo box
called "Name Code", the table is "TM - Client Details". Thanks for your help.

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM_-_Client_Details", "Name_Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub

 

[Ken Snell [MVP]]

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client_Details", "Name Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

Sorry --- try this one instead:

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client Details", "Name Code = '" & _
Me.[Name Code].Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.[Name Code].Undo
End If
End Sub


ACCESS VBA puts the _ character in place of spaces in the code because a
space is not allowed in variable, field, control, or subroutine names (you
can surround field or control names with [ ] when they contain spaces, but
VBA won't do that automatically). Thus, in the DCount function, you need to
use the actual field names and not the ones that VBA "creates".

And now you know why experts always recommend that you not put spaces and
other "nonnormal" characters in field and control names.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

I certainly do. Thanks for your advice todate. I copied your text and pasted
it into my - Event Procedure - in Before Update but when testing it it comes
up with a compile error - "Method or Data Member not found" in Visual Basic
Screen. Highlighted in yellow is the first line and highlighted in blue is -
..Cancel =

The only other detail I have omitted to date is that the form runs through
a query to the main table. The query is QFM Client Details

Sorry I am not getting this to work to quickly

Stuart

Sorry --- try this one instead:

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client Details", "Name Code = '" & _
Me.[Name Code].Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.[Name Code].Undo
End If
End Sub


ACCESS VBA puts the _ character in place of spaces in the code because a
space is not allowed in variable, field, control, or subroutine names (you
can surround field or control names with [ ] when they contain spaces, but
VBA won't do that automatically). Thus, in the DCount function, you need to
use the actual field names and not the ones that VBA "creates".

And now you know why experts always recommend that you not put spaces and
other "nonnormal" characters in field and control names.
--

Ken Snell
<MS ACCESS MVP>

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client_Details", "Name Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>


to level relates. field

 

[Ken Snell [MVP]]

It definitely was time for sleep last night when I made my second
correction..... so here's another correction for something that I'd missed
in your posted code!

Change
Me.Cancel = True

to
Cancel = True


As for the fact that the form's RecordSource is a query and not the table,
I'm not sure that that is important to what you want to achieve. The primary
key is "primary" to the table, not to the query. Thus, its presence in the
table is what you want to ascertain.
--

Ken Snell
<MS ACCESS MVP>

I certainly do. Thanks for your advice todate. I copied your text and pasted
it into my - Event Procedure - in Before Update but when testing it it comes
up with a compile error - "Method or Data Member not found" in Visual Basic
Screen. Highlighted in yellow is the first line and highlighted in blue is -
.Cancel =

The only other detail I have omitted to date is that the form runs through
a query to the main table. The query is QFM Client Details

Sorry I am not getting this to work to quickly

Stuart

Sorry --- try this one instead:

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client Details", "Name Code = '" & _
Me.[Name Code].Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.[Name Code].Undo
End If
End Sub


ACCESS VBA puts the _ character in place of spaces in the code because a
space is not allowed in variable, field, control, or subroutine names (you
can surround field or control names with [ ] when they contain spaces, but
VBA won't do that automatically). Thus, in the DCount function, you need to
use the actual field names and not the ones that VBA "creates".

And now you know why experts always recommend that you not put spaces and
other "nonnormal" characters in field and control names.
--

Ken Snell
<MS ACCESS MVP>

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client_Details", "Name Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>


Here is the code. It is probably incorrectly entered from your previous
answer. It assumes that the Primary key is text, the Control is a combo
box
called "Name Code", the table is "TM - Client Details". Thanks for your
help.

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM_-_Client_Details", "Name_Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub

:

Post the code that you're trying to use... I can't see it from here <
grin
!

--

Ken Snell
<MS ACCESS MVP>

I am still having trouble. My table is called 'TM - Client Details'.
My
Primary Name Field is called 'Name Code'. My Control on my form is a
combo
box called 'Name Code'. The Primary key is a text file. There are
spaces
between the words in the respective names.

:

You have your users entering data into the control that is

bound
to

the
Primary Key field? You can use the BeforeUpdate event of that
control to
test for uniqueness:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableName", "PrimaryKeyFieldName = " & _
Me.ControlName.Value) > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the
table!"
Me.ControlName.Undo
End If
End Sub

The above code assumes that the primary key field is a numeric data
type. If
it's text, use this DCount expression:

If DCount("*", "TableName", "PrimaryKeyFieldName = '" & _
Me.ControlName.Value & "'") > 0 Then

--

Ken Snell
<MS ACCESS MVP>

I am aware of the process of preventing duplicates at Table level
by
"Yes
(No
Duplicates) however it is at form level where my question relates.
The
first
field on my form contains the Primary key with no duplicates.
Following
this
there are many other fields on the form. What I want is a message
saying
that
"this is a duplicate value" at time of entry to the Primary field
on
the
form, not when you attempt to save the form. I have tried

using
a

"Dlookup"
function in the validation rule but it is not working. Can anyone
help?

 

[Ken Snell [MVP]]

Ugh... my posted code had that Me.Cancel in it, not your code.... my
apologies for this perpetuated error on my part!
--

Ken Snell
<MS ACCESS MVP>

I certainly do. Thanks for your advice todate. I copied your text and pasted
it into my - Event Procedure - in Before Update but when testing it it comes
up with a compile error - "Method or Data Member not found" in Visual Basic
Screen. Highlighted in yellow is the first line and highlighted in blue is -
.Cancel =

The only other detail I have omitted to date is that the form runs through
a query to the main table. The query is QFM Client Details

Sorry I am not getting this to work to quickly

Stuart

Sorry --- try this one instead:

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client Details", "Name Code = '" & _
Me.[Name Code].Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.[Name Code].Undo
End If
End Sub


ACCESS VBA puts the _ character in place of spaces in the code because a
space is not allowed in variable, field, control, or subroutine names (you
can surround field or control names with [ ] when they contain spaces, but
VBA won't do that automatically). Thus, in the DCount function, you need to
use the actual field names and not the ones that VBA "creates".

And now you know why experts always recommend that you not put spaces and
other "nonnormal" characters in field and control names.
--

Ken Snell
<MS ACCESS MVP>

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client_Details", "Name Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>


Here is the code. It is probably incorrectly entered from your previous
answer. It assumes that the Primary key is text, the Control is a combo
box
called "Name Code", the table is "TM - Client Details". Thanks for your
help.

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM_-_Client_Details", "Name_Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub

:

Post the code that you're trying to use... I can't see it from here <
grin
!

--

Ken Snell
<MS ACCESS MVP>

I am still having trouble. My table is called 'TM - Client Details'.
My
Primary Name Field is called 'Name Code'. My Control on my form is a
combo
box called 'Name Code'. The Primary key is a text file. There are
spaces
between the words in the respective names.

:

You have your users entering data into the control that is

bound
to

the
Primary Key field? You can use the BeforeUpdate event of that
control to
test for uniqueness:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableName", "PrimaryKeyFieldName = " & _
Me.ControlName.Value) > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the
table!"
Me.ControlName.Undo
End If
End Sub

The above code assumes that the primary key field is a numeric data
type. If
it's text, use this DCount expression:

If DCount("*", "TableName", "PrimaryKeyFieldName = '" & _
Me.ControlName.Value & "'") > 0 Then

--

Ken Snell
<MS ACCESS MVP>

I am aware of the process of preventing duplicates at Table level
by
"Yes
(No
Duplicates) however it is at form level where my question relates.
The
first
field on my form contains the Primary key with no duplicates.
Following
this
there are many other fields on the form. What I want is a message
saying
that
"this is a duplicate value" at time of entry to the Primary field
on
the
form, not when you attempt to save the form. I have tried

using
a

"Dlookup"
function in the validation rule but it is not working. Can anyone
help?

 

[Guest]

Ken

It has been a short while since I looked at this problem however I still
cannot get it to work. the code I am using is as follows:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client Details", "Name Code = '" & _
Me.[Name Code].Value & "'") > 0 Then
Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.[Name Code].Undo
End If
End Sub

The error message I now get is

Run Time Error 3075 - Syntax error (missing Operator) in Name Code = '"

Any further help would be appreciated


Stuart

Ugh... my posted code had that Me.Cancel in it, not your code.... my
apologies for this perpetuated error on my part!
--

Ken Snell
<MS ACCESS MVP>

I certainly do. Thanks for your advice todate. I copied your text and pasted
it into my - Event Procedure - in Before Update but when testing it it comes
up with a compile error - "Method or Data Member not found" in Visual Basic
Screen. Highlighted in yellow is the first line and highlighted in blue is -
.Cancel =

The only other detail I have omitted to date is that the form runs through
a query to the main table. The query is QFM Client Details

Sorry I am not getting this to work to quickly

Stuart

Sorry --- try this one instead:

Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client Details", "Name Code = '" & _
Me.[Name Code].Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.[Name Code].Undo
End If
End Sub


ACCESS VBA puts the _ character in place of spaces in the code because a
space is not allowed in variable, field, control, or subroutine names (you
can surround field or control names with [ ] when they contain spaces, but
VBA won't do that automatically). Thus, in the DCount function, you need to
use the actual field names and not the ones that VBA "creates".

And now you know why experts always recommend that you not put spaces and
other "nonnormal" characters in field and control names.
--

Ken Snell
<MS ACCESS MVP>




Try this:

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM - Client_Details", "Name Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub


--

Ken Snell
<MS ACCESS MVP>


Here is the code. It is probably incorrectly entered from your previous
answer. It assumes that the Primary key is text, the Control is a combo
box
called "Name Code", the table is "TM - Client Details". Thanks for your
help.

Private Sub Name_Code_BeforeUpdate(Cancel As Integer)
If DCount("*", "TM_-_Client_Details", "Name_Code = '" & _
Me.Name_Code.Value & "'") > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the table!"
Me.Name_Code.Undo
End If
End Sub

:

Post the code that you're trying to use... I can't see it from here <
grin
!

--

Ken Snell
<MS ACCESS MVP>

I am still having trouble. My table is called 'TM - Client Details'.
My
Primary Name Field is called 'Name Code'. My Control on my form is a
combo
box called 'Name Code'. The Primary key is a text file. There are
spaces
between the words in the respective names.

:

You have your users entering data into the control that is bound
to
the
Primary Key field? You can use the BeforeUpdate event of that
control to
test for uniqueness:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableName", "PrimaryKeyFieldName = " & _
Me.ControlName.Value) > 0 Then
Me.Cancel = True
MsgBox "You have entered a value that is already in the
table!"
Me.ControlName.Undo
End If
End Sub

The above code assumes that the primary key field is a numeric
data
type. If
it's text, use this DCount expression:

If DCount("*", "TableName", "PrimaryKeyFieldName = '" & _
Me.ControlName.Value & "'") > 0 Then

--

Ken Snell
<MS ACCESS MVP>

I am aware of the process of preventing duplicates at Table
level
by
"Yes
(No
Duplicates) however it is at form level where my question
relates.
The
first
field on my form contains the Primary key with no duplicates.
Following
this
there are many other fields on the form. What I want is a
message
saying
that
"this is a duplicate value" at time of entry to the Primary
field
on
the
form, not when you attempt to save the form. I have tried using
a
"Dlookup"
function in the validation rule but it is not working. Can
anyone
help?