How to start a field at "1" and increment the value by one on the following records.

Fred Fisher
Fred Fisher used Ask the Experts™
on
I am writing a database to track my player piano rolls.  My main form is frmTitles which on which the Piano Roll title information is entered and then the subform is frmSongs on which the song information is entered.  The fields on frmSongs are (in order) SongPosition, SongTitle, SongType and SongLength.  

When I first enter the subform I would like the default value for SongPosition to be 1.  Then for each song after that have the SongPosition increment by 1 so that after the initial 1 I would have 2,3,4,5,6,7 etc. until all of the songs for that title are entered.  It would help if there was a way to error check so that the numbers are all in sequential order.  I have access 2016.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Juan OcasioApplication Developer

Commented:
In Table Design, set the type to AutoNumber
Senior Developer
Commented:
If it is a single user application, then you can prepopulate the song position by using Nz(DMax("SongPosition", "Songs", "AlbumID = " & [AlbumID]), 0) + 1 in the TextBox.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Note that an AutoNumber field is guaranteed to be unique but not necessarily sequential. You can have gaps in the order in many cases. Better to use something like ste5an suggests, or use the DefaultValue property of a control to manage this.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use an editable Priority number:

' Set the priority order of a record relative to the other records of a form.
'
' The table/query bound to the form must have an updatable numeric field for
' storing the priority of the record. Default value of this should be Null.
'
' Requires:
'   A numeric, primary key, typical an AutoNumber field.
'
' Usage:
'   To be called from the AfterUpdate event of the Priority textbox:
'
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority
'       End Sub
'
'   and after inserting or deleting records:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority
'       End Sub
'
'   Optionally, if the control holding the primary key is not named Id:
'
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
' 2018-08-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub RowPriority( _
    ByRef TextBox As Access.TextBox, _
    Optional ByVal IdControlName As String = "Id")
    
    ' Error codes.
    ' This action is not supported in transactions.
    Const NotSupported      As Long = 3246

    Dim Form                As Access.Form
    Dim Records             As DAO.Recordset
    
    Dim RecordId            As Long
    Dim NewPriority         As Long
    Dim PriorityFix         As Long
    Dim FieldName           As String
    Dim IdFieldName         As String
    
    Dim Prompt              As String
    Dim Buttons             As VbMsgBoxStyle
    Dim Title               As String
    
    On Error GoTo Err_RowPriority
    
    Set Form = TextBox.Parent
    
    If Form.NewRecord Then
        ' Will happen if the last record of the form is deleted.
        Exit Sub
    Else
        ' Save record.
        Form.Dirty = False
    End If
    
    ' Priority control can have any Name.
    FieldName = TextBox.ControlSource
    ' Id (primary key) control can have any name.
    IdFieldName = Form.Controls(IdControlName).ControlSource
    
    ' Prepare form.
    DoCmd.Hourglass True
    Form.Repaint
    Form.Painting = False
    
    ' Current Id and priority.
    RecordId = Form.Controls(IdControlName).Value
    PriorityFix = Nz(TextBox.Value, 0)
    If PriorityFix <= 0 Then
        PriorityFix = 1
        TextBox.Value = PriorityFix
        Form.Dirty = False
    End If
    
    ' Disable a filter.
    ' If a filter is applied, only the filtered records
    ' will be reordered, and duplicates might be created.
    Form.FilterOn = False
    
    ' Rebuild priority list.
    Set Records = Form.RecordsetClone
    Records.MoveFirst
    While Not Records.EOF
        If Records.Fields(IdFieldName).Value <> RecordId Then
            NewPriority = NewPriority + 1
            If NewPriority = PriorityFix Then
                ' Move this record to next lower priority.
                NewPriority = NewPriority + 1
            End If
            If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then
                ' Priority hasn't changed for this record.
            Else
                ' Assign new priority.
                Records.Edit
                    Records.Fields(FieldName).Value = NewPriority
                Records.Update
            End If
        End If
        Records.MoveNext
    Wend
    
    ' Reorder form and relocate record position.
    ' Will fail if more than one record is pasted in.
    Form.Requery
    Set Records = Form.RecordsetClone
    Records.FindFirst "[" & IdFieldName & "] = " & RecordId & ""
    Form.Bookmark = Records.Bookmark
   
PreExit_RowPriority:
    ' Enable a filter.
    Form.FilterOn = True
    ' Present form.
    Form.Painting = True
    DoCmd.Hourglass False
    
    Set Records = Nothing
    Set Form = Nothing
    
Exit_RowPriority:
    Exit Sub
    
Err_RowPriority:
    Select Case Err.Number
        Case NotSupported
            ' Will happen if more than one record is pasted in.
            Resume PreExit_RowPriority
        Case Else
            ' Unexpected error.
            Prompt = "Error " & Err.Number & ": " & Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
            
            ' Restore form.
            Form.Painting = True
            DoCmd.Hourglass False
            Resume Exit_RowPriority
    End Select
    
End Sub

Open in new window

See the attached demo.

Sort on field Priority in the form, and edit the values as they like - they will rearrange to be in sequence.
PriorityDemo.accdb
Fred FisherPhotographer

Author

Commented:
This is for the sub form "frmTitlesSubformSongs".  Gustav your solution seems close but since it has been almost 10 years since I programmed anything in Access I am a bit rusty.  Each title will have a number of songs and the SongPositon needs to start with 1 and increment by 1 for the following records.  If say SongPosition 5 is deleted all of those records following 5 for that title will have to be reordered starting with 5.

I am confused by:
 
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority
'       End Sub

Is the Sub Priority in my case Sub SongPosition_AfterUpdate()?


'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority
'       End Sub

Is Priority here again SongPosition?

'   Optionally, if the control holding the primary key is not named Id:


The primary key for Songs is SongID so how does this relate to the following?

'
'       Private Sub Priority_AfterUpdate()
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'
'       Private Sub Form_AfterInsert()
'           RowPriority Me.Priority, NameOfPrimaryKeyControl
'       End Sub
'

The rest I get I just need to be sure I substitute the correct fields SongPosition, SongTitle and SongID in the proper places.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Is the Sub Priority in my case Sub SongPosition_AfterUpdate()?
Yes.
And SongID seems to be your PK of the songs, thus these lines would probably read:

    RowPriority Me.SongPosition, "SongID"

Open in new window

Hamed NasrRetired IT Professional

Commented:
Is the numbering n the subform a temporary one or has to be reflected in the corresponding table?

Upload a sample database, with only relevant objects, if more help is required.

The idea is:
In the subform, increment each entry by 1.
When a record is deleted, renumber those relevant records and refresh the subform.
Fred FisherPhotographer

Author

Commented:
Sorry it took a awhile to get back.  The relevant field is SongPosition.  Each media title (CD, LP, etc.) will have a number of songs from 1 to xx.  When entering the first song for a new title I would like it to default to 1, next song 2 and so on.  If there are songs 1 to 10 and I delete say number 5, then 6, 7, 8, 9, 10 should be renumbered 5. 6. 7. 8 and 9.

For a title with existing songs say 1 - 7 and I want to add more songs I would want it to start at 8 and continue on.
Hamed NasrRetired IT Professional

Commented:
This is for the sub form "frmTitlesSubformSongs".  Gustav your solution seems close but since ,,

I will wait until you exhaust Gustav's contribution.

If you need extra help, please upload a sample database.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I turned this into an article:

Sequential Rows in Microsoft Access
Hamed NasrRetired IT Professional

Commented:
Do I understand your issue?
Please check this demo database.
Run frm_a_main
sequence.accdb

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial