Link to home
Start Free TrialLog in
Avatar of Fred Fisher
Fred FisherFlag 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.
Avatar of Juan Ocasio
Juan Ocasio
Flag of United States of America image

In Table Design, set the type to AutoNumber
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
Avatar of 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.
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

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

Sequential Rows in Microsoft Access
Do I understand your issue?
Please check this demo database.
Run frm_a_main
sequence.accdb