Access: Problem with "Auto numbering" (not autonumeric) after "Compact and Repair"
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!
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!
