Insert record and re-sequence Primary Key - Access 2013 VBA

Question

I'm very much an Visual Basic noob... I'm trying to create a button on my Access form that can create a record and re-sequence my non-auto-incremented Primary Key to insert the record where I want it in the record list.

Currently, to do this I 1) Open the table, 2) use an AutoHotKey keyboard macro to manually edit the Primary Key number from the last record to where I want to insert my new record, 3) create a new record (with an appropriate value in my Primary Key column) at the bottom of the list, then 4) Right-click the column and "Sort from smallest to largest", and finally 5) use a second AutoHotKey macro to re-sequence the Primary Key from the new record to the end of the list.

I'd like a Command Button that will allow me to do the same with just one click.

The database currently has 897 records- adding a new record at #185 using the method I currently use seems tedious when it seems a FOR / NEXT (Step -1) Loop in VBA would be so much more efficient.

Some names I use:

Form Name: [Anime Input Form]
Table Name: [DVDs and Blu Rays]
Primary Key Name: [AIN#]

Gustav Gustav · Accepted Answer · 2015-11-14T12:53:50

You can do like this - where the field is named Priority and you are using the AfterUpdate event of this:

Private Sub Priority_AfterUpdate()

    Dim rst             As DAO.Recordset
    Dim lngId           As Long
    Dim lngPriorityNew  As Long
    Dim lngPriorityFix  As Long

    ' Save record.
    Me.Dirty = False

    ' Prepare form.
    DoCmd.Hourglass True
    Me.Repaint
    Me.Painting = False

    ' Current Id and priority.
    lngId = Me!Id.Value
    lngPriorityFix = Nz(Me!Priority.Value, 0)
    If lngPriorityFix <= 0 Then
        lngPriorityFix = 1
        Me!Priority.Value = lngPriorityFix
        Me.Dirty = False
    End If

    ' Rebuild priority list.
    Set rst = Me.RecordsetClone
    rst.MoveFirst
    While rst.EOF = False
        If rst!Id.Value <> lngId Then
            lngPriorityNew = lngPriorityNew + 1
            If lngPriorityNew = lngPriorityFix Then
                ' Move this record to next lower priority.
                lngPriorityNew = lngPriorityNew + 1
            End If
            If Nz(rst!Priority.Value, 0) = lngPriorityNew Then
                ' Priority hasn't changed for this record.
            Else
                ' Assign new priority.
                rst.Edit
                    rst!Priority.Value = lngPriorityNew
                rst.Update
            End If
        End If
        rst.MoveNext
    Wend

    ' Reorder form and relocate record.
    Me.Requery
    Set rst = Me.RecordsetClone
    rst.FindFirst "Id = " & lngId & ""
    Me.Bookmark = rst.Bookmark

    ' Present form.
    Me.Painting = True
    DoCmd.Hourglass False

    Set rst = Nothing

End Sub

Insert record and re-sequence Primary Key - Access 2013 VBA

2 Answers