Updating a record with an index set in the table

[bustercrabb]

Hello:
I cannot update a record in my table in Access using a custom form, and I believe that it is because I have the table indexed. It is indexed because I don't want duplicates and I need it to do random searches.
The error that I get is common: "...would not update because it would create a duplicate record..." I am lost, and cannot figure out another option. Here is my code, and thanks in advance for your time:
Set Database2 = CurrentDb()
Set rs2 = Database2.OpenRecordset("SELECT f2,f1, f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f14,f16,f17,f18,f19,f20,f21,f22 FROM 2016_Graded ORDER BY f2 ASC;")

rs2.MoveFirst
With rs2
.Edit
rs2![f11] = ef_ID
rs2![f2] = ef_tname
rs2![f3] = ef_dob
rs2![f18] = ef_Date
rs2![f9] = ef_ethnicity
rs2![f10] = ef_gender
rs2![f12] = ef_comments
rs2.Update
End With

 

[dhookom]

Maybe you could tell us which field is the primary/unique key?

Are you attempting to put a duplicate value into the field?

Can we assume you left off the code that Dim's the DAO.Database and DAO.Recordset?

Duane
Hook'D on Access
MS Access MVP

 

[bustercrabb]

Duane:
Thank you for your response.

The primary key and index is set on ef_tname which includes a six-digit number (that's just the system). Sometimes, I may need to edit the ef_tname, and I am sure to not duplicate another record. Also, I have not created any other relationships between this table and 6 others which do have the same primary key.

Editing ef_tname example: Doe,Jon,Bob,123456 may be changed to Doe,John,Bob,123456 (No spaces and no duplication). All of this is getting done from my form. Also, as a quick fix, I put a button on the form that opens the table which does let the user make changes, but I would like to do everything from the form.

Here is the code again with the DAO recordset:

Dim rs2 As DAO.Recordset
Set Database2 = CurrentDb()
Set rs2 = Database2.OpenRecordset("SELECT f2,f1, f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f14,f16,f17,f18,f19,f20,f21,f22 FROM 2016_Graded ORDER BY f2 ASC;")

Else
' Editing record
rs2.MoveFirst
With rs2
.Edit
rs2![f11] = ef_ID
rs2![f2] = ef_tname
rs2![f3] = ef_dob
rs2![f18] = ef_Date
rs2![f9] = ef_ethnicity
rs2![f10] = ef_gender
rs2![f12] = ef_comments
rs2.Update
End With
End If

 

[dhookom]

Are you running this code from a form? If so, is it bound to a record source? If so what is the record source?

Do you understand your code always attempts to edit the record in [2016_Graded] with the lowest value of [f2]. I would expect some filtering to return only one record.

Duane
Hook'D on Access
MS Access MVP