Results 1 to 2 of 2

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

  1. #1
    Neophyte dreamer's Avatar
    Join Date
    Oct 2021
    Posts
    2
    Articles
    0
    Excel Version
    2019

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Acolyte RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    27
    Articles
    0
    Excel Version
    2019
    If the field Numeroregistro is autonumeric, he last number does not necessarily be the number of records. Try to change Me.Numeroregistro = rs.RecordCount + 1 by the max number in Numeroregistro + 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •