Access Error 3022. (Update of question) Access is creating duplicate Autonumber PK.
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:
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?
'delete original records and this works 100% of the timestrDelete = "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 addedstrMulti = "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 LoopEnd If