MS Access incrementing a number per value in a field-CodePudding

PO_D_Temp contains several fields 2 of which are [InvNo] and [Detail_Line].There will be many rows that belong with same [InvNo]. I would like each [Detail_Line] to begin with 1, 2 etc for each [InvNo]

Example: [InvNo]1 [Detail_Line] 1 [InvNo]1 [Detail_Line] 2 [InvNo]2 [Detail_Line] 1 [InvNo]3 [Detail_Line] 1 ETC!

All I have been able to figure out is a loop that adds an incrementing number to ALL records - not renumbering at each different InvNo.

Here is what I have (I am at kindergarten level, sorry):

    Private Sub Command1_Click()

    Dim db As Database
    Set db = CurrentDb()

    Dim rstPO_D_Temp As Recordset
    Dim strSQL As String
    Dim intI As Integer
    Dim DetailNum As Integer

    'Open a recordset on all records from the PO_D_Temp table
       strSQL = "SELECT * FROM PO_D_Temp"
       Set rstPO_D_Temp = db.OpenRecordset(strSQL, dbOpenDynaset)
       DetailNum = 0

    '   If the recordset is empty, exit.
       If rstPO_D_Temp.EOF Then Exit Sub

       intI = 1
       With rstPO_D_Temp
          Do Until .EOF
             .Edit
             ![Detail_Line] = DetailNum   intI
             .Update
             .MoveNext
             intI = intI   1
          Loop
       End With

       rstPO_D_Temp.Close
       Set rstPO_D_Temp = Nothing

    End Sub

CodePudding user response：

Reset the detail no. for each invoice no.:

Private Sub Command1_Click()

    Dim db As DAO.Database
    Set db = CurrentDb()

    Dim rstPO_D_Temp As DAO.Recordset
    Dim strSQL As String
    Dim DetailNum As Integer
    Dim LastInvoice As Long

    'Open a recordset on all records from the PO_D_Temp table
    strSQL = "SELECT * FROM PO_D_Temp"
    Set rstPO_D_Temp = db.OpenRecordset(strSQL, dbOpenDynaset)

    With rstPO_D_Temp
        Do Until .EOF
            If LastInvoice <> !InvNo.Value Then
                DetailNum = 1
                LastInvoice = !InvNo.Value
            Else
                DetailNum = DetailNum   1
            End If
            .Edit
            ![Detail_Line].Value = DetailNum
            .Update
            .MoveNext
        Loop
        .Close
    End With

    Set rstPO_D_Temp = Nothing

End Sub