How do I get Access to recognize duplicate values immediately?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
Click to expand...

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
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.
 
Thank you very much. This was very helpful. It worked perfectly!
--
Glenn


fredg said:
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.
Click to expand...

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
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.
Click to expand...
 
Hi,

My field is a Date/Time format. When I enter this expression you gave :
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
MsgBox "This item already exists in the table."
Cancel = True
End If
Click to expand...
Click to expand...

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,

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


fredg said:
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.
Click to expand...

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
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.
Click to expand...
Click to expand...
 
If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


sebgou said:
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
Click to expand...
Click to expand...

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,

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


fredg said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Douglas J Steele said:
If DCount("*","TableName","[FieldName] = " & Format(Me![ControlName],
"\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


sebgou said:
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
Click to expand...

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,

Glenn said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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.

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


sebgou said:
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
Click to expand...

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,

Glenn said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

Dan S. said:
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.

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


sebgou said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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

Alp Bekisoglu said:
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

Dan S. said:
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.

Douglas J Steele said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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


fredg said:
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.
Click to expand...

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
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.
Click to expand...
 
Never mind.... I solved my problem by browsing other posts. I added:
Me.Undo
after the "Cancel = True" statement.

This forum is wonderful!
--
Julie


fredg said:
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.
Click to expand...

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
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.
Click to expand...
 
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!
Click to expand...

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.
 
allo
Julie said:
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


fredg said:
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.
Click to expand...

Is the Field a Text datatype?
Code that control's BeforeUpdate event:
If DCount("*","TableName","[FieldName] = '" & Me![ControlName] & "'")
0 Then
Click to expand...
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.
Click to expand...
Click to expand...
 
Back
Top