troubleshooting Question

Access Error 3022. (Update of question) Access is creating duplicate Autonumber PK.

Avatar of PBLack
PBLack asked on
Microsoft Access
20 Comments1 Solution2370 ViewsLast Modified:
I will leave the rest of the question as is BUT I have confirmed that Access 2007 is generating a duplicate PK when it is set to:

Autonumber
Long
Increment
No Duplicates Allowed
Field name is SL_PK and it is selected as PK and is indexed.

Screen shot attached. Any ideas how to overcome this?

My code works 99% of the time but once and awhile I get Access Error 3022: The changes you requested to the table were not successful because they would create duplicate values in the primary index, primary key, or relationship...

The code is an edit mode that I created that deletes all records from the table where criteria are found. There can be unlimited records based on what is in tblMulti.

There is a typical PK in each and it is set to autonumber no duplicates allowed.

I can not find where I went wrong. Can you please look at this and point me in a direction to fix it?

Thanks

PBLack

'delete original records and this works 100% of the time
strDelete = "DELETE * FROM tblStandImprovement WHERE SL_BLNUM = '" & Trim(Me.txtBlNum) & "' AND SL_SECT = '" & Format(Me!txtSect, "00") & "' AND SL_INV = '" & Me.txtInv & "';"
db.Execute (strDelete)

'cycle through tblMulti and add values from it into tblStandImprovement. A unique record is created in tblStandImprovement for each record in tblMulti and this is where the error is thrown and none of the new records were added
    
strMulti = "SELECT * FROM tblMulti;"
Set recMulti = db.OpenRecordset(strMulti, dbOpenDynaset)
    
If recMulti.EOF = False Then
    recMulti.MoveFirst
    Do Until recMulti.EOF = True
        strSave = "SELECT * FROM tblStandImprovement;"
        Set recSave = db.OpenRecordset(strSave, dbOpenDynaset)
        With recSave
            .AddNew
                !SL_TRTP = Trim(Me.txtTranType)
                !SL_ERCOR = Me.cboCorType
                !SL_CORBL = Me.txtCorBlkNum
                !SL_LIC = Me.cboLic.Column(0)
                !SL_REGNO = Me.cboRegion.Column(0)
                !SL_UPNUM = Me.txtUp
                !SL_BLNUM = Trim(Me.txtBlNum)
                !SL_SECT = Trim(Format(Me.txtSect, "00"))
                !SL_RTCD = Me.cboRteCd.Column(0)
                !SL_RATE = Me.txtRate
                !SL_TRTCD = Me.cboTrtCd.Column(0)
                !SL_MAPNO = Trim(Me.txtMapNo)
                !SL_STDTE = Me.cboStYr & Me.cboStMt & Me.cboStDy
                !SL_ENDTE = Me.cboEdYr & Me.cboEdMt & Me.cboEdDy
                !SL_STYR = Me.cboStYr
                !SL_STMT = Me.cboStMt
                !SL_STDY = Me.cboStDy
                !SL_EDYR = Me.cboEdYr
                !SL_EDMT = Me.cboEdMt
                !SL_EDDY = Me.cboEdDy
                !SL_HA = Trim(Me.txtHa)
                !SL_TOTST = Trim(Me.txtPreSW)
                !SL_PREDN = Trim(Me.txtDen)
                !SL_SPEC = recMulti!MU_SPECIES
                !SL_AVHGT = recMulti!MU_HGT
                !SL_PSTDN = recMulti!MU_DENSITY
                !SL_AVDBH = recMulti!MU_DBH
                !SL_TRTID = Trim(Me.txtCodeID)
                !SL_COM = Trim(Me.txtCom)
                If Not IsNull(Me.txtRqd) = True Then
                  !SL_FILE = Trim(Me.txtFile)
                  !SL_FOLD = Trim(Me.txtFolder)
                Else
                  !SL_FILE = "NA"
                End If
                !SL_INV = Me.txtInv
                !SL_PID = Me.txtPID
                !SL_STAT = True
            .Update
            .Close
        End With
        Set recSave = Nothing
        recMulti.MoveNext
    Loop
End If
screen-shot-dup-pk.JPG
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros