Solved Form Record Cloning In MS Access (2 Viewers)

N

nector

Member
Local time
Tomorrow, 00:46
Joined
Jan 21, 2020
Messages
559
Hi all,

The code below is coming from the solution previously given, fair enough that was excellent, but last night when I was now doing an extensive testing, I discovered that the VBA Code below sometimes will start numbering rows in the subform from 0,1,2,3 ,4 etc instead of always starting from 1,2,3,4 etc depending to the number of rows in a subform.

Here is the original code

Code: 
Option Compare Database
Option Explicit

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long

    Set rst = Me.RecordsetClone
    If rst.RecordCount = 0 Then
        LastSequenceNumber = 0
       Else
        rst.MoveFirst
        Do While Not rst.EOF
            If Nz(rst!SequenceNumber, 0) > LastSequenceNumber Then
                LastSequenceNumber = Nz(rst!SequenceNumber, 0)
            End If
            rst.MoveNext
        Loop
    End If
    Me!SequenceNumber = LastSequenceNumber + 1

Exit_Error_Handler:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing

End Sub


Now I'm trying to amend it to ALWAYS start numbering rows no matter the situation is from 1,2,3, 4 etc depending to the number of rows that are in the subform.

VBA amended version, but not sure whether it will achieve the above requirements

Code: 
Option Compare Database
Option Explicit

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long

    Set rst = Me.RecordsetClone
    If rst.RecordCount = 0 Then
        LastSequenceNumber = 0
       ElseIf rst.RecordCount <> 0 Then
        LastSequenceNumber = 1
        rst.MoveFirst
        Do While Not rst.EOF
            If Nz(rst!SequenceNumber, 0) > LastSequenceNumber Then
                LastSequenceNumber = Nz(rst!SequenceNumber, 0)
            End If
            rst.MoveNext
        Loop
    End If
    Me!SequenceNumber = LastSequenceNumber + 1

Exit_Error_Handler:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing

End Sub

It should never start from 0 but from 1 incrementing up to the end of the last row
 
Under what circumstances does the original code not work?

Sometimes it starts with 0 ,1,2,3 etc instead of starting from 1,2,3 etc if it finds that there is no record in the subform it will start from 0,1,2,3 etc instead of 1,2,3,4 etc, but I thought the reason we are putting NZ is to ensure that whenever there is no record at least Zero will be returned to make the code start from 1 to whatever number of lines.

1759301916046.png


Just look at the sequenceNumber field above, that is the correct way to number the rows as opposed to starting from 0 ,1,2,3,4
 
Last edited:
Do you have code elsewhere that can change or re-order your sequence numbers?

The only way that your original code can give a sequence number of 0 would be if there is a SequenceNumber of -1 in your table.

Also, rather than looping the RecordsetClone, it might be easier/quicker to use DMax() against the table directly.

Eg. (Adjust field names as required!)
Code: 
Private Sub Form_BeforeInsert(Cancel As Integer)

  Me.SequenceNumber = Nz(DMax("SequenceNumber", "tblInvoiceItems", "InvoiceFK = " & Me.txtInvoiceFK), 0) + 1

End Sub
 
Last edited:
you can also try this
note that I added code on the After_DelConfirm, so that it will renumber correctly
when you delete a record.

Code: 
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long

If Status <> 2 Then

    Set rst = Me.RecordsetClone
    With rst
        If .RecordCount = 0 Then
            GoTo Exit_Error_Handler
        Else
            .MoveFirst
            LastSequenceNumber = 1
            While Not .EOF
                .Edit
                !SequenceNumber = LastSequenceNumber
                .Update
                LastSequenceNumber = LastSequenceNumber + 1
                .MoveNext
            Wend
        End If
    End With
Exit_Error_Handler:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
End If
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long

    Set rst = Me.RecordsetClone
    With rst
        If .RecordCount = 0 Then
            Me!SequenceNumber = 1
        Else
            .MoveFirst
            LastSequenceNumber = 1
            While Not .EOF
                .Edit
                !SequenceNumber = LastSequenceNumber
                .Update
                LastSequenceNumber = LastSequenceNumber + 1
                .MoveNext
            Wend
            Me!SequenceNumber = LastSequenceNumber
        End If
    End With
Exit_Error_Handler:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
End Sub
 
arnelgp said:
you can also try this
note that I added code on the After_DelConfirm, so that it will renumber correctly
when you delete a record.

Code: 
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long

If Status <> 2 Then

    Set rst = Me.RecordsetClone
    With rst
        If .RecordCount = 0 Then
            GoTo Exit_Error_Handler
        Else
            .MoveFirst
            LastSequenceNumber = 1
            While Not .EOF
                .Edit
                !SequenceNumber = LastSequenceNumber
                .Update
                LastSequenceNumber = LastSequenceNumber + 1
                .MoveNext
            Wend
        End If
    End With
Exit_Error_Handler:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
End If
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim LastSequenceNumber As Long

    Set rst = Me.RecordsetClone
    With rst
        If .RecordCount = 0 Then
            Me!SequenceNumber = 1
        Else
            .MoveFirst
            LastSequenceNumber = 1
            While Not .EOF
                .Edit
                !SequenceNumber = LastSequenceNumber
                .Update
                LastSequenceNumber = LastSequenceNumber + 1
                .MoveNext
            Wend
            Me!SequenceNumber = LastSequenceNumber
        End If
    End With
Exit_Error_Handler:
    If Not rst Is Nothing Then rst.Close
    Set rst = Nothing
End Sub
Click to expand...
This is excellent job and well done arnelgp it was not easy to think in those lines I'm sure it will help others well
 
Is Barcode tied to SequenceNumber?

If yes, and it is also stored in the table, then you will need to modify Arnel's suggestion to handle amending that field too.

I'm still not sure why you have to store the SequenceNumber - does it make a difference if Sriracha Chili Sauce comes after Sriracha Mayo?
 
One of the main purposes of storing the sequence number on a table field is that you can reorder the items the way you want (if needed or wanted).
 
nector said:
Now I'm trying to amend it to ALWAYS start numbering rows no matter the situation is from 1,2,3, 4 etc depending to the number of rows that are in the subform.
Click to expand...
If by that you mean that you want to number the rows in the subform as an unbroken sequence regardless of the insertion to or deletion from the table of any rows, then storing the values is inadvisable as it leaves the table open to the risk of update anomalies. The values can be computed at runtime by means of a query such as the following, which numbers the transactions sequentially from 1 upwards per customer:

SQL: 
SELECT
    COUNT(*) AS RowNumber,
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
    AND (T2.TransactionDate <= T1.TransactionDate)
    AND (T2.CustomerID = T1.CustomerID)
GROUP BY
    T1.CustomerID,
    T1.TransactionDate,
    T1.TransactionAmount,
    T1.TransactionID;

Note that this uses the primary key TransactionID as the tie breaker in the event of two or more transactions per customer being undertaken on the same day. Should the sort order of the query be on distinct values per customer, the need for the tie breaker would be eliminated of course.

The above query, employing a join of two instances of the Transactions table, is the most efficient method for computing row numbers, but returns a non-updatable result table. The following alternative, calling the VBA DCount function returns an updatable result table, but is less efficient:

Code: 
SELECT
    DCOUNT (
        "*",
        "Transactions",
        "CustomerID = " & CustomerID & "
        AND TransactionDate  <=  #" & Format(TransactionDate, "yyyy-mm-dd") & "#
        AND (TransactionID <= " & TransactionID & "
           OR TransactionDate <> #" & Format(TransactionDate, "yyyy-mm-dd") & "#)"
    ) AS RowNumber,
    CustomerID,
    TransactionDate,
    TransactionAmount
FROM
    Transactions
ORDER BY
    CustomerID,
    TransactionDate,
    TransactionID;

Should you wish to number the rows in date of insertion order, include a DateTimeStamp column in the table and assign the return value of the Now() function to the column when a new row is inserted. The DateTimeStamp column can then be used to order the rows returned in place of the TransactionDate column in the above queries. Should you wish to order the rows in some arbitrary order unconnected to the values in a column or columns, include a RowIndex column or similar of single precision number data type. If a new row is inserted, or a row's ordinal position in the sequence changes, a value of 12.5 could be added to place the row between the current rows 12 and 13. The sequence can then be recalculated as integer values. I do this with a set of regular monthly transactions in a current account table, using the following code:

Code: 
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim n As Integer
  
    Me.Dirty = False
  
    strSQL = "SELECT PaymentIndex FROM RegularPayments ORDER BY PaymentIndex"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    With rst
        .MoveLast
        n = 1
        .MoveFirst
        Do While Not .EOF
            .Edit
            .Fields("PaymentIndex") = n
            .Update
            n = n + 1
            .MoveNext
        Loop
    End With
    Set rst = Nothing
                  
    Me.Requery
 
Last edited:
You must log in or register to reply here.

Similar threads

N
Solved How to include line sequence in MS Access Line details table
Replies
18
Views
421
Pat Hartman
P
W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
7
Views
910
Ken Sheridan
Ken Sheridan
zelarra821
Solved Loop through records in a table that gives an error
Replies
4
Views
598
zelarra821
zelarra821
C
Form doesn't show new record
Replies
12
Views
903
MarkK
MarkK
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
654
Minty
Minty

Users who are viewing this thread

Total: 3 (members: 0, guests: 3)
Back
Top Bottom