Avatar of Fred Fisher
Fred Fisher
Flag for United States of America asked on

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.
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
Hamed Nasr

8/22/2022 - Mon
Juan Ocasio

In Table Design, set the type to AutoNumber
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scott McDaniel (EE MVE )

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.
Gustav Brock

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Fred Fisher

ASKER
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 Brock

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 Nasr

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Fred Fisher

ASKER
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 Nasr

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 Brock

I turned this into an article:

Sequential Rows in Microsoft Access
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Hamed Nasr

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