Solved Runtime error 3022, Why? (1 Viewer)

[5hadow]

Hello once again!

I am trying to add data from unbound text boxes to a sub-form but I get runtime 3022. Maybe you guys know why?

Can you see anything wrong with the code below?

Option Compare Database

Private Sub btnAddAudit_Click()

With CurrentDb.OpenRecordset("tblWIAudit")

If Not .BOF And Not .EOF Then


.MoveLast
.MoveFirst

'Add a new one
.AddNew
![fldAuditID] = Me.fldDocID
![fldAuditType] = fldAuditType.Value
![fldAuditDate] = fldAuditDate.Value
![fldAuditNotes] = fldAuditNotes.Value
![fldAuditByID] = fldAuditByID.Value


.Update

End If

End With

' Requery
DoCmd.RunCommand acCmdSaveRecord
subFrmWIAudit.Requery
End Sub

Next, here is the sub-form SQL

SELECT *
FROM tblWIAudit
ORDER BY tblWIAudit.fldAuditDate DESC;

Main form

SELECT A.*, B.fldSectionName AS [Section], [C_1].[fldFirstName] & " " & [C_1].[FLDLASTNAME] AS Owner, [C_2].[fldFirstName] & " " & [C_2].[FLDLASTNAME] AS [Approving Authority], tblWIProcess.fldProcessID
FROM (((tblWI AS A LEFT JOIN tblSection AS B ON A.fldSectionID = B.fldSectionID) LEFT JOIN tblMember AS C_1 ON B.fldDocOwner = C_1.fldMemberID) LEFT JOIN tblMember AS C_2 ON B.fldAppAuth = C_2.fldMemberID) INNER JOIN tblWIProcess ON A.fldProcessID = tblWIProcess.fldProcessID;

Red box contains my unbound text which by pressing "Enter Audit into History" button it's supposed to populate sub-form below.
Field values in the code are correct, btnAddAudit is the "Enter Audit into History" button.

If you need to see other parts of my database please let me know.

 

[The_Doc_Man]

Error 3022 is the one that says you are about to violate a key constraint - i.e. it is talking about duplicate data entry into a unique field. Your problem is EITHER you have tried to enter something that is already in the table, or you should not have declared the uniqueness constraint on that field in the first place because it CAN be duplicated. Unfortunately, that code and the SQL are convoluted enough that I don't see your cause right off the bat. I cannot tell you which of the two possible causes it could be.

If you have errors enabled, do you get a stoppage pointing to a particular line of code? That often helps narrow it down though I'm betting it is the recordset .Update in your code segment that you showed us.

 

[5hadow]

Error 3022 is the one that says you are about to violate a key constraint - i.e. it is talking about duplicate data entry into a unique field. Your problem is EITHER you have tried to enter something that is already in the table, or you should not have declared the uniqueness constraint on that field in the first place because it CAN be duplicated. Unfortunately, that code and the SQL are convoluted enough that I don't see your cause right off the bat. I cannot tell you which of the two possible causes it could be.

If you have errors enabled, do you get a stoppage pointing to a particular line of code? That often helps narrow it down though I'm betting it is the recordset .Update in your code segment that you showed us.

Hello,

Thanks for you reply.
I'm thinking I have an issue with

![fldAuditID] = Me.fldDocID

Now, I'm just copying code from another database and changing the values to work with this database, so I'm not 100% sure, however,

If I'm reading this correctly, fldAuditID becomes what ever the fldDocID is on the form. This would make a problem I'm thinking because fldAuditID is an auto number field. Having it copied would not work? Am I on the right track here?

Edit:
When pressing "Debug" after error, the code stops at

.Update

Edit 2:
Ok, I just figured out that ID DOES save the information in my tblWIAudit table as I want it, but It is not updated, or showing in my subform.

Edit 3: SOLVED!

Ok for anyone wondering, in my code,

![fldAuditID] = Me.fldDocID

![fldAuditID] was supposed to be ![fldDocID].

 

[The_Doc_Man]

I'm thinking because fldAuditID is an auto number field. Having it copied would not work? Am I on the right track here?

That is DEFINITELY not good if you are copying an autonumber into the same table.

However, I see that the problem was merely copying the wrong field. And TRUST me, we've all done that and felt silly afterwards.