If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with Yes, No Duplicates
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200904/1 |
#2
|
|||
|
|||
Problem with Yes, No Duplicates
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" u25116@uwe wrote in message news:954cc8ca9b9e1@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200904/1 |
#3
|
|||
|
|||
Problem with Yes, No Duplicates
No I haven't. I'll do that and get back to you.
Many thanks for prompt reply, Uschi Jeff Boyce wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200904/1 |
#4
|
|||
|
|||
Problem with Yes, No Duplicates
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 Jeff Boyce wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200904/1 |
#5
|
|||
|
|||
Problem with Yes, No Duplicates
On Thu, 30 Apr 2009 23:51:26 GMT, "Uschi via AccessMonster.com" u25116@uwe
wrote: 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? -- John W. Vinson [MVP] |
#6
|
|||
|
|||
Problem with Yes, No Duplicates
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. John W. Vinson wrote: 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? -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Problem with Yes, No Duplicates
On Fri, 01 May 2009 18:45:13 GMT, "Uschi via AccessMonster.com" u25116@uwe
wrote: 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? -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Problem with Yes, No Duplicates
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 wrote: 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 |
#9
|
|||
|
|||
Problem with Yes, No Duplicates
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 wrote: 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? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200905/1 |
#10
|
|||
|
|||
Problem with Yes, No Duplicates
On Fri, 15 May 2009 20:53:24 GMT, "Uschi via AccessMonster.com" u25116@uwe
wrote: 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? Su 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|