Duplicate value in index and object moved or renamed problems

[sleake]

I have spent hours trying to resolve these two problems;

1) In a Contacts database, I am repeatedly getting "changes requested to table not successful because they would create duplicate values, etc. I have checked the table, and there was only one field, ContactID, the primary key, that was indxed. There are no other indexed fields. I finally removed the primary key, but left it indexed, but dups OK. Same error. Removed the index completely. Same error.

Another problem that may be related:
2) I have Alan Browne's audit routine running on the form. I have been repeatedly getting errors about the object having been removed or renamed. It starts with the first line -- Audit Begin.Call AuditEditBegin("tbl_Contacts", "tbl_AudTemp_Contacts", "ContactID", Nz(Me.ContactID, 0), bWasNewRecord)

I've check the form carefully and the ContactID is on the form (not visible). ContaactID is the name of the data source, as well as the name under Other. I deleted and replaced the ContactID object, thinking somethng might be wrong with it, but no change.

I have repeatedly compacted and repaired the back end, but I am now beginning to wonder if something is wrong with the table. ContactID seems to be the common thread. I have also decompiled and recompiled the front end twice in the last two days.



I'm using Office 2010 as of a week ago, but the database is a mdb. I recreated both the front and back ends in fresh datbases. Any thoughts? I'm getting a little desperate at this point.

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

[sleake]

I very much appreciate your offer. I'll make a clone without the ULS, and only a few records minus confidential data plus linked tables that you'll be able to open on your C drive. (Unless you tell me different.) It will take me some time.

[sleake]

I've created a clone with three company records and ther contacts. I restored the ContactID field to primary key. I deleted all confidential data and created fictitious names, phone numbers and e-mail addresses for contacts. I had commented out the audit lines, but removed the comment so they would be active. There are some commented items that I will delete --made a note to you in an additional comment.

The tables are linked to the following path: C: SFTP\SampleDatabase_BE_sleake. I zipped it in order to send it to my personal computer. My company strips unzipped databases.

[June7]

Which objects are involved in this issue? What form should I work with to observe the described behavior?

[sleake]

My apologies. I wasn't thinking clearly. The form is the Contacts form, accessible by clicking Partner Profiles on the SFTP Navigator form, then Contacts. Form name: frm_Contacts_Data_Entry. Object is the ContactID on that form that seems to be generating both errors below. To observe behavior, add a new contact and click Save.

1) In a Contacts database, I am repeatedly getting "changes requested to table not successful because they would create duplicate values, etc. I have checked the table, and there was only one field, ContactID, the primary key, that was indxed. There are no other indexed fields. I finally removed the primary key, but left it indexed, but dups OK. Same error. Removed the index completely. Same error.

Another problem that may be related:
2) I have Alan Browne's audit routine running on the form. I have been repeatedly getting errors about the object having been removed or renamed. It starts with the first line -- Audit Begin.Call AuditEditBegin("tbl_Contacts", "tbl_AudTemp_Contacts", "ContactID", Nz(Me.ContactID, 0), bWasNewRecord)

[June7]

Opening frm_Contacts_Data_Entry triggers VBA error: 'Microsoft Access cannot find the referenced form 'frm_SFTP_Add_Partner_Profile'. If you want code to populate PartnerID for a new record, need to change code to:
If IsNull(Me.txtPartnerIDContacts) Then
Me.txtPartnerIDContacts = Forms!frm_SFTP_Add_Partner_Profile.txtSFTPPartnerI D
End If

Odd, txtPartnerNameContacts is not displaying value in new record. It should as soon as txtPartnerIDContacts is populated. Baffled by this.

Textbox bound to SFTPPartnerName should be Locked Yes, TabStop No and PartnerID should be a combobox to select partner, if you want to allow different partner to be selected. Apply the same settigns to ContactID

Should probably change the join type for qry_Contacts_Data_Entry from INNER to LEFT:

SELECT tbl_Contacts.*, tbl_SFTP_Partners.SFTPPartnerName, [FirstName] & " " & [LastName] AS FullName, tbl_SFTP_Partners.SFTPProgramArea
FROM tbl_Contacts LEFT JOIN tbl_SFTP_Partners ON tbl_Contacts.PartnerID = tbl_SFTP_Partners.PartnerID;

1. Not getting the error, maybe because of the above edits.

2. Also did not get this error with frm_Contacts_Data_Entry.

[sleake]

Thanks so much for all of your input. I finally abandoned hope of getting the contacts form to open already filled with the partner ID and name, and took your suggestion to make the Partner control a combo box. Thanks for the suggestion to change the join on the query. I don't know why I didn't set it up that way in the beginning, because it was a one-to-many! Maybe that had something to do with the index conflict message. Any how, it's up and running now and the primary users report no problems.