From: David Barger on
Greetings,

I am using Access 2007 for a business contact database. On the form I use
to add/edit company information I have several tabs. The first tab contains
the company data. Part of that is a true/false field indicating whether the
company has prequalification information. If this field is true, then
another tab is set to visible containing a subform for the prequalification
data (drawn from a table that has the company ID as its key.) Each company
may or may not have information in the prequalification table.

My problem arises when I try to enter data on the prequalification tab on a
company that didn't previously have prequalification data. Once I try to
navigate away from the prequalification tab, I get the following error:

“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 entries and try again.”

I don't know where the subform would be trying to create another line in the
prequalification table.

I have the following code behind the Prequalification control on the company
form:

____________________________________________________________________
Private Sub Prequalification_AfterUpdate()

'Check the prequalification field
If Me!Prequalification Then
'check to see if the prequal data exists
Dim db As Database, pq As Recordset
Set db = CurrentDb
Set pq = db.OpenRecordset("tblPrequalificationData")
pq.FindFirst "[CompanyID] = " & Me!CompanyID
'if the prequal data doesn't exist, then make it.
If pq.NoMatch Then
pq.AddNew
pq!CompanyID = Me!CompanyID
pq.Update
End If
'clean up
pq.Close
Set pq = Nothing
Set db = Nothing
End If

'Set the Pre-Qualification Information and License tabs visible based on the
Prequalification field
Me.[Pre-Qualification Information].Visible = Me!Prequalification
Me.License.Visible = Me!Prequalification

End Sub
___________________________________________________________________
So, why would the subform be trying to create another line? This only
happens when I have new data. Once I close the form, reopen it, and go back
to the prequalification data, I can enter the new data the error does not
recur.
Any assistance will be greatly appreciated (by both me and the wall I keep
banging my head against!)