Problem with Yes, No Duplicates

[Uschi via AccessMonster.com]

I have created a database for a homeowners association where the owners each
own a share in the corporation. Whenever there is a change in ownership,
title, lost certificate, etc. a new certficate is issued.

When I set the Certificate Index to Yes, No Duplicates the following prompt
apppears:

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entires and try again."

The Certificate field is a number. It is not the primary key. I have scrolled
through the 1,616 records and have not found any duplicates-hopefully.

Any suggestions on how I can be assured that a certificate number will not be
duplicated?

Uschi

 

[Jeff Boyce]

Access appears to believe that there ARE duplicates.

Have you tried using the query wizard to construct a "find duplicates"
query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Uschi via AccessMonster.com]

No I haven't. I'll do that and get back to you.

Many thanks for prompt reply,
Uschi

Access appears to believe that there ARE duplicates.

Have you tried using the query wizard to construct a "find duplicates"
query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a database for a homeowners association where the owners
each

[quoted text clipped - 20 lines]

Uschi

 

[Uschi via AccessMonster.com]

I used the "find duplicates" wizard and, yes, there is one duplicate
certificate - only one of which is "Current". When I questioned a Board
Member about this I was informed that there is ONE situation where a
certficate will be a duplicate and that is when an owner sells their
"property" and buys another "property" within the corporation - meaning they
still own that certificate in the corporation.

The database keeps a history of all the certificates. It cannot have
duplicate certificates that are marked "Current".

Can you help me with a code for this?

Your help will be greatly appreciated.

Uschi

Access appears to believe that there ARE duplicates.

Have you tried using the query wizard to construct a "find duplicates"
query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have created a database for a homeowners association where the owners
each

[quoted text clipped - 20 lines]

Uschi

 

[John W. Vinson]

I used the "find duplicates" wizard and, yes, there is one duplicate
certificate - only one of which is "Current". When I questioned a Board
Member about this I was informed that there is ONE situation where a
certficate will be a duplicate and that is when an owner sells their
"property" and buys another "property" within the corporation - meaning they
still own that certificate in the corporation.

The database keeps a history of all the certificates. It cannot have
duplicate certificates that are marked "Current".

Can you help me with a code for this?

How is a record marked "current"? Might there be two or more non-current
certificates?

 

[Uschi via AccessMonster.com]

There is a field "Current" (checkbox). There can only be one current
certificate for each of the 533 shareholders. Since the database keeps the
history of the association there will be two or more non-current certificates.

I used the "find duplicates" wizard and, yes, there is one duplicate
certificate - only one of which is "Current". When I questioned a Board

[quoted text clipped - 7 lines]

Can you help me with a code for this?

How is a record marked "current"? Might there be two or more non-current
certificates?

 

[John W. Vinson]

There is a field "Current" (checkbox). There can only be one current
certificate for each of the 533 shareholders. Since the database keeps the
history of the association there will be two or more non-current certificates.

That makes it tough, since these two fields do not between them uniquely
identify a record. Is there any other field (for example an effective-date
field?) that would let you distinguish one record from another?

Since the "Current"ness of a record isn't a proper attribute of the record
(its value depends on the existance of other records), you will probably need
some VBA code in the Form's BeforeUpdate event to search for another current
record with the same certificate, and warn the user and cancel the addition if
it finds one. Or would you prefer to warn the user and turn off the Current
checkbox in the other record?

 

[jim1016]

The key needs to be more unique and you need to add a second column.
AutoNumber should be the first column and the second column would be your
Certificate Index. Try that and see.

 

[Uschi via AccessMonster.com]

I feel a VBA code in the Form's BeforeUpdate event to search for another
current record with same certificate number, warn the user and cancel the
addition is the way to go. Can you help me with this?

 

[John W. Vinson]

I feel a VBA code in the Form's BeforeUpdate event to search for another
current record with same certificate number, warn the user and cancel the
addition is the way to go. Can you help me with this?

Sure:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns as Integer
Dim strMsg as String
If Not IsNull(DLookUp("CertNo", "tablename", "CertNo = '" & Me!Certno _
& "' AND Current = True")) Then
strMsg = "There is already a current certificate. Click OK to add anyway."
iAns = MsgBox(strMsg, vbOKCancel)
If iAns = vbCancel Then
Cancel = True
End If
End If
End Sub

This should probably have some error handling, needs your actual table and
fieldnames, etc.