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

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.
Fred FisherPhotographerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Juan OcasioApplication DeveloperCommented:
In Table Design, set the type to AutoNumber
ste5anSenior DeveloperCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Gustav BrockCIOCommented:
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 FisherPhotographerAuthor 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.
Gustav BrockCIOCommented:
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 ProfessionalCommented:
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 FisherPhotographerAuthor 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 ProfessionalCommented:
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.
Gustav BrockCIOCommented:
I turned this into an article:

Sequential Rows in Microsoft Access
Hamed NasrRetired IT ProfessionalCommented:
Do I understand your issue?
Please check this demo database.
Run frm_a_main
sequence.accdb
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.