Access: Problem with "Auto numbering" (not autonumeric) after "Compact and Repair"

Question

Hi everyone!

After performing a "Compact and Repair" in Access 2016 I have had the problem that adding a new record generates an error:

"The requested changes to the table were unsuccessful 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 it to allow duplicate entries, and try again "Obviously the idea is not to have duplicate fields ... so I don't know why this error is generated.

I comment that to generate the Self-healing of records, use this code (Not the Autonomous Access code)

Private Sub Form_Current ()

If Nz (Me.Numeroregistro) = 0 Then 'if it does not have an invoice number it is new

Me.Numeroregistro = rs.RecordCount + 1 'we put one more than the number of records in the table

Modified = True 'we mark it as modified

End If

End Sub

Private Sub Form_Dirty (Cancel As Integer)

Modified = True 'if an existing record is modified it is marked as Modified

End Sub

Private Sub Form_BeforeUpdate (Cancel As Integer)

If Modified Then 'Before updating the registry we check if it meets the requirements

If MsgBox ("CURRENT RECORD MODIFIED" & vbCr & vbCr & "Do you want to Save Changes?", VbCritical + vbDefaultButton2 + vbYesNo) = vbNo Then

Cancel = True

Modified = Not Modified

Me.Undo

DoCmd.GoToRecord,, acLast

Else

If Nz (Me.N ° _Note) = 0 Then

MsgBox "The Note Number is a Required Field"

Cancel = True

End If

End If

End If

End Sub

I hope you can guide me in this problem.

Thanks!

Answer 1

Setting the value of a column to an integer one greater than the number of rows in the table is risky, as it does not cater for any breaks in the sequence arising from the prior deletion of one or more rows. To increment a sequence of numbers by 1 the usual method is by setting the value for a new record to 1 greater than the highest current value in the table.

You might like to take a look at CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how to assign a sequential value to a column when a new row is inserted by means of a form. It caters for possible conflicts in assigning the value if two or more users are inserting a row simultaneously, and allows the next number to be used to be seeded.

The method illustrated in the demo assigns a value to a primary key column, so if any rows other than that with the highest number have been deleted, a gap will be left in the sequence. The demo also illustrates how to fill such gaps if desired. As the sequence will the have no semantic significance, however, it would be better to return the sequential numbers at runtime in a query. RowNumbering.zip in the same OneDrive folder illustrates a number of queries which do this.