How do I get Access to recognize duplicate values immediately?

[Glenn]

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

 

[Klatuu]

You will get an error if you enter a value for a no duplicates field in a
form and do anything to attempt to update the database.

 

[fredg]

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Glenn]

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[sebgou]

Hi,

My field is a Date/Time format. When I enter this expression you gave :

If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

I have an error of Data type mismatch. I tested with a ''Text'' field and
it works, but I really need the field to be ''Date/Time. Can it be possible ?

THanks,

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Douglas J Steele]

If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

My field is a Date/Time format. When I enter this expression you gave :

If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

I have an error of Data type mismatch. I tested with a ''Text'' field and
it works, but I really need the field to be ''Date/Time. Can it be possible ?

THanks,

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn

On Wed, 10 Aug 2005 11:56:48 -0700, Glenn wrote:

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Dan S.]

If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

My field is a Date/Time format. When I enter this expression you gave :

If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

I have an error of Data type mismatch. I tested with a ''Text'' field and
it works, but I really need the field to be ''Date/Time. Can it be possible ?

THanks,

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn


:

On Wed, 10 Aug 2005 11:56:48 -0700, Glenn wrote:

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Dan S.]

I have a very similar problem: I have a field in a table & form called
"EmailAddress". I want to check for previous record with the same email
before advancing with form completion. I Have tried all the sample code that
has been posted to no avail. I am a newbie so that may be the issue. Any
help would be appreciated.

If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

My field is a Date/Time format. When I enter this expression you gave :

If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

I have an error of Data type mismatch. I tested with a ''Text'' field and
it works, but I really need the field to be ''Date/Time. Can it be possible ?

THanks,

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn


:

On Wed, 10 Aug 2005 11:56:48 -0700, Glenn wrote:

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Alp Bekisoglu]

Dan,

I'm no expert but if I got you right, you can avoid entering duplicate
values to the field by setting the field's Indexed option to Yes (No
Duplicates) in table design.

Hope it helps a little.

Alp

I have a very similar problem: I have a field in a table & form called
"EmailAddress". I want to check for previous record with the same email
before advancing with form completion. I Have tried all the sample code
that
has been posted to no avail. I am a newbie so that may be the issue. Any
help would be appreciated.

If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

My field is a Date/Time format. When I enter this expression you gave :

If DCount("*","TableName","[FieldName] = '" & Me![ControlName] &
"'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

I have an error of Data type mismatch. I tested with a ''Text'' field
and
it works, but I really need the field to be ''Date/Time. Can it be possible ?

THanks,

:

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn


:

On Wed, 10 Aug 2005 11:56:48 -0700, Glenn wrote:

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] &
"'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Arvin Meyer [MVP]]

Unfortunately, this does not give immediate feedback. You have to move off
of the record before the duplicate becomes apparent. Using a Domain function
(DLookup, Dcount) or building a recordset to check for the duplicate in the
control's BeforeUpdate event is the only way to get immediate feedback.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Dan,

I'm no expert but if I got you right, you can avoid entering duplicate
values to the field by setting the field's Indexed option to Yes (No
Duplicates) in table design.

Hope it helps a little.

Alp

I have a very similar problem: I have a field in a table & form called
"EmailAddress". I want to check for previous record with the same email
before advancing with form completion. I Have tried all the sample code
that
has been posted to no avail. I am a newbie so that may be the issue.
Any
help would be appreciated.

If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

My field is a Date/Time format. When I enter this expression you gave
:

If DCount("*","TableName","[FieldName] = '" & Me![ControlName] &
"'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

I have an error of Data type mismatch. I tested with a ''Text'' field
and
it works, but I really need the field to be ''Date/Time. Can it be
possible ?

THanks,

:

Thank you very much. This was very helpful. It worked perfectly!
--
Glenn


:

On Wed, 10 Aug 2005 11:56:48 -0700, Glenn wrote:

Can Access recognize a duplicate value in an indexed (Yes, no
duplicates)
field as soon as you type it in and exit that field? Currently,
Access lets
you enter all data on a form and then when you try to move to
the
next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] &
"'")
0 Then
MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Julie]

Fredg: Your solution worked fine, however, after I click OK on the msgbox, I
get Access's error message that says: "The value in the field or record
violates the validation rule for the record or field....." How do I suppress
this message?
Thanks for your help!
--
Julie

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[Julie]

Never mind.... I solved my problem by browsing other posts. I added:
Me.Undo
after the "Cancel = True" statement.

This forum is wonderful!
--
Julie

Can Access recognize a duplicate value in an indexed (Yes, no duplicates)
field as soon as you type it in and exit that field? Currently, Access lets
you enter all data on a form and then when you try to move to the next form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.

 

[fredg]

Fredg: Your solution worked fine, however, after I click OK on the msgbox, I
get Access's error message that says: "The value in the field or record
violates the validation rule for the record or field....." How do I suppress
this message?
Thanks for your help!

Julie,
You're there and I'm here. I can't see your database.
Is there an actual Error number included in the message?
Please re-post the exact (copy and paste) code you are using,
including the event that it is in.
Also, copy and paste any code in the Form's Error event.

 

[mustang2]

allo

Fredg: Your solution worked fine, however, after I click OK on the
msgbox, I
get Access's error message that says: "The value in the field or record
violates the validation rule for the record or field....." How do I
suppress
this message?
Thanks for your help!
--
Julie

Can Access recognize a duplicate value in an indexed (Yes, no
duplicates)
field as soon as you type it in and exit that field? Currently, Access
lets
you enter all data on a form and then when you try to move to the next
form
you get the error message stating you have a duplicate value.

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")

0 Then

MsgBox "This item already exists in the table."
Cancel = True
End If

If it is a Number datatype:

If DCount("*","TableName","[FieldName] = " & Me![ControlName]) > 0
Then
etc.