Yes(No Duplicates)

[Jerry]

Say a table has four fields:
ID (PK)
Field1
Field2
Field3
Field 1 is indexed as Yes(No Duplicates)
When a duplicate is entered in Field1, nothing happens until you try to save
the record then this error message appears:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship......"
Can this error message be trapped and somehow say what field the duplicate
is in?

Jerry

[Allen Browne]

If you enter data through a form, you can use the Error event of the form to
trap the error and replace it with your own.

This kind of thing:

Public Function FormError(frm As Form, DataErr As Integer, Response As
Integer) As Integer
If DataErr = 3022 Then
strMsg = "Duplicate value for Field1."
MsgBox strMsg, vbCritical, "Invalid data"
Response = acDataErrContinue
End If
End Function
If you have multiple possible duplicate fields, you will need to use a
DLookup() to find which one already exists.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jerry" <js*****@nospam.please> wrote in message
news:Jt*******************@newsread3.news.atl.eart hlink.net...

Say a table has four fields:
ID (PK)
Field1
Field2
Field3
Field 1 is indexed as Yes(No Duplicates)
When a duplicate is entered in Field1, nothing happens until you try to
save
the record then this error message appears:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship......"
Can this error message be trapped and somehow say what field the duplicate
is in?

Jerry

[Rog]

In addition, it's a good practice to create an extra form where the
user enters only the record key for added records, so that you can trap
any duplicate keys before all the other fields are entered.

[Trevor Best]

Allen Browne wrote:

If you enter data through a form, you can use the Error event of the form to
trap the error and replace it with your own.

This kind of thing:

Public Function FormError(frm As Form, DataErr As Integer, Response As
Integer) As Integer
If DataErr = 3022 Then
strMsg = "Duplicate value for Field1."
MsgBox strMsg, vbCritical, "Invalid data"
Response = acDataErrContinue
End If
End Function
If you have multiple possible duplicate fields, you will need to use a
DLookup() to find which one already exists.

I like the approach of using the Form's error event for this, I would
suggested the counts in Form_BeforeUpdate, which would do the counts
every time a record is saved. Doing it in the form's error event means
it's only done if there's a problem.

--
[Oo=w=oO]

[Allen Browne]

Not sure I understood that, Rog.

If you wanted to alert the user before the filled in all the rest of the
fields, couldn't you use the AfterUpdate event of the control itself on the
normal form to DLookup() the table and see if the record was a duplicate?
This kind of thing (assuming a Text type field named F1 in Table1):

Private Sub F1_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant

With Me.F1
If (.Value = .OldValue) Or IsNull(.Value) Then
'Do nothing
Else
strWhere = "[F1] = """ & .Value & """"
varResult = DLookup("F1", "Table1", strWhere)
If Not IsNull(varResult) Then
MsgBox "Duplicate"
End If
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Rog" <de********@state.gov> wrote in message
news:11********************@l41g2000cwc.googlegrou ps.com...

In addition, it's a good practice to create an extra form where the
user enters only the record key for added records, so that you can trap
any duplicate keys before all the other fields are entered.

[Ed Robichaud]

In the data entry form used for your app, put the following code in the On
Error event:

If DataErr = 3022 Then
MsgBox "Attempting to Add Item that already Exists. ", vbExclamation
Response = acDataErrDisplay
End If
This will display a custom message when a duplicate record is attempted to
be entered. You'd have to turn the standard warning Off, unless you want
both to appear, then turn warnings back On for all other cases/events.
You'd probably also want to present the user with options to fix or erase
the current record.
-Ed

"Jerry" <js*****@nospam.please> wrote in message
news:Jt*******************@newsread3.news.atl.eart hlink.net...

Say a table has four fields:
ID (PK)
Field1
Field2
Field3
Field 1 is indexed as Yes(No Duplicates)
When a duplicate is entered in Field1, nothing happens until you try to
save
the record then this error message appears:
"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key or
relationship......"
Can this error message be trapped and somehow say what field the duplicate
is in?

Jerry

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

"Rog" <de********@state.gov> wrote in message
news:11********************@l41g2000cwc.googlegrou ps.com...

In addition, it's a good practice to create an extra form where
the user enters only the record key for added records, so that
you can trap any duplicate keys before all the other fields are
entered.

Not sure I understood that, Rog.

I tend not to add records in the same form as I use to edit them.

I use an unbound form to collect the required values, check to see
if a record already exists that violates whatever rules I have about
duplicates, and give the user the opportunity to act on those.

For instance, adding people, I collect first name and last name (and
often company), then check against existing data. I then present a
list of close matches and allow the user to add the new record or
abandon it and go to an existing record (for cases where it turns
out the attempted new record was a real duplicate of an existing
record).

Once the new stump record is created (with the minimum required
fields), I then open the full data editing form with that new record
loaded in it.

I prefer to avoid an error rather than handling it once it occurs.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jerry]

David,

<<For instance, adding people, I collect first name and last name (and often
company), then check against existing data>>

Do you do this by icreating an index that includes all three fields and
setting Unique to Yes?

<< I then present a list of close matches>>

How do you do this?

<< I then open the full data editing form with that new record loaded in it.

Do you lock first name, last name and company at this point so they can not
be changed here? Any changes here would nullify the previous checks for
duplicates.

Thanks!

Jerry
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86... "Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

"Rog" <de********@state.gov> wrote in message
news:11********************@l41g2000cwc.googlegrou ps.com...

In addition, it's a good practice to create an extra form where
the user enters only the record key for added records, so that
you can trap any duplicate keys before all the other fields are
entered.

Not sure I understood that, Rog.

I tend not to add records in the same form as I use to edit them.

I use an unbound form to collect the required values, check to see
if a record already exists that violates whatever rules I have about
duplicates, and give the user the opportunity to act on those.

For instance, adding people, I collect first name and last name (and
often company), then check against existing data. I then present a
list of close matches and allow the user to add the new record or
abandon it and go to an existing record (for cases where it turns
out the attempted new record was a real duplicate of an existing
record).

Once the new stump record is created (with the minimum required
fields), I then open the full data editing form with that new record
loaded in it.

I prefer to avoid an error rather than handling it once it occurs.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Allen Browne]

David, thanks for sharing, and there are others (like Rog) who do that as
well.

Personally, I've never built a form like that (other than in a wizard-style
database for novices).

IMHO, the extra forms mean extra maintenance, concurrency issues, and
inflexibility, so I use a combination of events: the AfterUpdate of the
control(s) involved in the index if we need immediate feedback, the error
BeforeUpdate of the form to get a meaningful message, and the Error event of
the form in case I missed something or business rules/validation/indexes are
changed after the database is out of my hands.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

"Rog" <de********@state.gov> wrote in message
news:11********************@l41g2000cwc.googlegrou ps.com...

In addition, it's a good practice to create an extra form where
the user enters only the record key for added records, so that
you can trap any duplicate keys before all the other fields are
entered.

Not sure I understood that, Rog.

I tend not to add records in the same form as I use to edit them.

I use an unbound form to collect the required values, check to see
if a record already exists that violates whatever rules I have about
duplicates, and give the user the opportunity to act on those.

For instance, adding people, I collect first name and last name (and
often company), then check against existing data. I then present a
list of close matches and allow the user to add the new record or
abandon it and go to an existing record (for cases where it turns
out the attempted new record was a real duplicate of an existing
record).

Once the new stump record is created (with the minimum required
fields), I then open the full data editing form with that new record
loaded in it.

I prefer to avoid an error rather than handling it once it occurs.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Jerry" <js*****@nospam.please> wrote in
news:SV***************@newsread2.news.atl.earthlin k.net:

<<For instance, adding people, I collect first name and last name
(and often
company), then check against existing data>>

Do you do this by icreating an index that includes all three
fields and setting Unique to Yes?
No, because the fields can have Nulls, and unique indexes can't have
Nulls (and still remain unique).

I do it with SQL, and a set of complex algorithms that match names
loosely and rank them according to how close the match is. I use 2
varieties of Soundex for that (I store both in the database).
<< I then present a list of close matches>>

How do you do this?
In a form with a subform with a custom-assigned recordsource using
criteria defined based on what has been entered for the name/company
data. I rank according to closeness of match and use a color-coding
system to indicate closeness of match (red is exact, orange
near-match, yellow remote, etc.).
<< I then open the full data editing form with that new record
loaded in it.

Do you lock first name, last name and company at this point so
they can not be changed here? Any changes here would nullify the
previous checks for duplicates.

I don't understand the question.

The add form is unbound. Changing it does not alter any data
anywhere.

Actually, you can't add the record until you've clicked the CHECK
FOR DUPLICATES button. Changes to the name/company fields re-disable
the ADD button and require that you check for duplicates again.

Is that what you meant?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:42***********************@per-qv1-newsreader-01.iinet.net.au:

David, thanks for sharing, and there are others (like Rog) who do
that as well.

Personally, I've never built a form like that (other than in a
wizard-style database for novices).

IMHO, the extra forms mean extra maintenance, . . .
There is no datasource, there are only 3 fields and it's a form for
a specific task. There is *no* maintenance issue involved, so far as
I can see.
. . . concurrency issues, . . .
It's unbound. There are no concurrency issues.
and inflexibility, . . . .
How is it inflexible?
. . . so I use a combination of events: the
AfterUpdate of the control(s) involved in the index if we need
immediate feedback, the error BeforeUpdate of the form to get a
meaningful message, and the Error event of the form in case I
missed something or business rules/validation/indexes are changed
after the database is out of my hands.

I can never control the interaction of events sufficiently to not
end up with extremely Byzantine code in the form. The main reason
for that is that the user is not restricted to an order of events in
adding a record when you use the same form for both adding and
editing. Using an unbound dialog to collect the required fields and
then test them against existing data makes means you have strict
control over the fields that matter for the add record process.

Now, of course, I wouldn't create a separate form with 5 required
fields for a table that has only 6 fields total. What I'm talking
about is adding records to tables with a handful of required fields
in comparison to the total number of data controls on the main data
editing form. It's a lot easier to handle those three fields in the
add record form and then tailor the form with 35 fields and 3
subforms to do what it does best, edit existing records.

I think the main reason I approach it this way is simply because of
the cost of cancelling a record addition. If you're letting the
record be created, then you've got issues with your surrogate keys
(whether or not you're incrementing them yourself), as well as the
huge data hit of having updated indexes and then backing that out
(which requires index updates again). Using a unique index to
prevent duplicates is only possible for certain kinds of data
entities, and I'm philosophically opposed to the approach of "stick
the data in and do something about it if it throws and error"
because those errors are very expensive in terms of tying up the
database back end -- writes are more expensive than reads, and my
solution does no writing until it's already been confirmed that the
record is OK to add.

Now, all that said, I mostly only use this approach for adding
people, since that's the most common scenario where possible
duplicates are likely.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jerry]

David,

Thanks for the followup! Yes, that's what I meant in the last question.

Jerry
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

"Jerry" <js*****@nospam.please> wrote in
news:SV***************@newsread2.news.atl.earthlin k.net:

<<For instance, adding people, I collect first name and last name
(and often
company), then check against existing data>>

Do you do this by icreating an index that includes all three
fields and setting Unique to Yes?

No, because the fields can have Nulls, and unique indexes can't have
Nulls (and still remain unique).

I do it with SQL, and a set of complex algorithms that match names
loosely and rank them according to how close the match is. I use 2
varieties of Soundex for that (I store both in the database).

<< I then present a list of close matches>>

How do you do this?

In a form with a subform with a custom-assigned recordsource using
criteria defined based on what has been entered for the name/company
data. I rank according to closeness of match and use a color-coding
system to indicate closeness of match (red is exact, orange
near-match, yellow remote, etc.).

<< I then open the full data editing form with that new record
loaded in it.

Do you lock first name, last name and company at this point so
they can not be changed here? Any changes here would nullify the
previous checks for duplicates.

I don't understand the question.

The add form is unbound. Changing it does not alter any data
anywhere.

Actually, you can't add the record until you've clicked the CHECK
FOR DUPLICATES button. Changes to the name/company fields re-disable
the ADD button and require that you check for duplicates again.

Is that what you meant?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc