Solved How to include line sequence in MS Access Line details table

[nector]

Hi

Yesterday I came to learn that the access database does not have a row line numbering system, for example if an invoice has ten rows, how can we also number those rows from one to ten. I tried the small database below I cannot get write. See if you can figure it out.

EG

1
2
3
4

I want each row in the child table to be properly numbered as above , I have a control called sequenceNumber where this numbering is suppose to be stored in the linedetail table but per each transaction

 

[cheekybuddha]

Is the ID field of tblLineDetails really a text datatype?

Generally, you would need to add to your criteria to find how many records came before the current one. You will also need to include the InvoiceID in your filter.

So, it may look like:

Dim strCritieria As String
strCriteria = "InvoiceID = " & Me.txtInvoiceID & " AND ID <= " & Me.txtCotrolID
Debug.Print strCriteria     ' check it's correct
.SequenceNumber = DCount("*", "tblLineDetails", strCriteria)

However, using a text ID could skew things.

 

[Minty]

If this is just displaying existing data then use your MySQL database to generate the row number, why would you try and do it locally when there is inbuilt functionality in the back end DB?

If you are simply adding a line item then the sequence number will be 1 more than the last one for that order which is equally easy to obtain?

 

[nector]

Is the ID field of tblLineDetails really a text datatype?

Generally, you would need to add to your criteria to find how many records came before the current one. You will also need to include the InvoiceID in your filter.

So, it may look like:

Dim strCritieria As String
strCriteria = "InvoiceID = " & Me.txtInvoiceID & " AND ID <= " & Me.txtCotrolID
Debug.Print strCriteria     ' check it's correct
.SequenceNumber = DCount("*", "tblLineDetails", strCriteria)

However, using a text ID could skew things.

Is the ID field of tblLineDetails really a text datatype?

The ID is a foreign key and Me.txtInvoiceID does not exist anywhere

strCriteria = "InvoiceID = " & Me.txtInvoiceID & " AND ID <= " & Me.txtCotrolID

 

[nector]

If this is just displaying existing data then use your MySQL database to generate the row number, why would you try and do it locally when there is inbuilt functionality in the back end DB?

If you are simply adding a line item then the sequence number will be 1 more than the last one for that order which is equally easy to obtain?

Sorry

I'm using MS Access in this case not MYSQL

 

[cheekybuddha]

The ID is a foreign key and Me.txtInvoiceID does not exist anywhere

You can get it from the parent form then, but easier to add a control using the invoice FK on to the subform. It should populate automatically from the linked master/child fields

 

[cheekybuddha]

Additionally, pay consideration to whether you really want to store the sequence number.

If an item is removed (eg mistake) then you will have to go and re-order the numbering of all the other items or you will have gaps.

Does the order sequence have any special meaning? If not, you can use a different strategy just to number the items on the subform and use their ID or date entered to sort.

 

[xavier.batlle]

If you really want to number the items on the subform, I use this approach: I set this function in the BeforeInsert subform event. It numbers the lines form 10 to 10 in case I want to insert any later:

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 + 10

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

 

[Ken Sheridan]

If you want to store the sequential numbers the attached CustomNumber demo illustrates a number of methods for doing so, incorporating error handling to cater for two or more users' inserting new rows simultaneously in a multi-user environment. The method for numbering rows per group would be appropriate in your case.

If you want to generate, but not store, the numbers at runtime, so that an unbroken sequence is maintained in the event of a row being deleted, the attached RowNumbering demo illustrates a number of queries for numbering or ranking rows. Again the options for numbering rows per group would be appropriate in your case. The most efficient method is by means of a join of two instances of the table, but this returns a non-updatable result table, so can be used as the RecordSource for a report for instance. The options for returning an updatable result table call the VBA DCount function, and would be suitable as the RecordSource for a bound form in which data is entered or edited.

 

[nector]

If you really want to number the items on the subform, I use this approach: I set this function in the BeforeInsert subform event. It numbers the lines form 10 to 10 in case I want to insert any later:

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 + 10

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

Many thanks sir, I think you are a genius, this exactly what I was battling with for many days

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

 

[arnelgp]

use Stephen Lebans RowNum() function.
you do not need to save the value to a table.

 

[isladogs]

In addition to the various methods of adding row numbers to queries and forms, Access does have a built-in system for row numbering in reports

 

[NauticalGent]

Access does have a built-in system for row numbering in reports

Say more...

 

[isladogs]

Just add an unbound text box to the report and set the properties below
Control Source: =1
Running Sum: Over Group

That’s all. The unbounded text box will automatically give a sequential number for each record

For queries & forms, my preferred solution is to use the Serialize function:

Rank Order In Queries

This article describes a method of creating a rank order in Access queries using a Serialize function

isladogs.co.uk

 

[spaLOGICng]

Most if not all Applications do not have a Sequencing Method within a set of records. You would need to add the functionality.

The Access Report does have a Report-level method using Running Sum across Groups or the Report, but Forms do not.

For sorting, I simply use a Date Created Column that defaults to the date/time stamp when created.

I have worked with Applications where a Sequence is necessary in which I would add custom functionality, either as you have or by utilizing a Function for the Default Value for the Sequence Column.

 

[Ken Sheridan]

For sorting, I simply use a Date Created Column that defaults to the date/time stamp when created.

Me too, but with one caveat. If this is done by calling the Now() function as the DefaultValue property, and the row is manually inserted in a form, the date/time value will be that at which the user moves the form to an empty new record. This could significantly differ from the time the row is actually inserted. Rather than using the DefaultValue property, therefore, I would assign the value returned by the Now() function to the column in question. Whether this is done in the form's BeforeInsert or BeforeUpdate event procedure depends on what is regarded as the time the row is inserted, i.e. when the user begins to insert the row or when they complete doing so.

The image below is from one of my little demo files. As you can see, the difference between the two methods is nearly two minutes, which was the time between my opening the form at a new record and when I began to insert data. The pause was while I googled Darwin to find his date of birth.

 

[spaLOGICng]

Me too, but with one caveat. If this is done by calling the Now() function as the DefaultValue property, and the row is manually inserted in a form, the date/time value will be that at which the user moves the form to an empty new record. This could significantly differ from the time the row is actually inserted. Rather than using the DefaultValue property, therefore, I would assign the value returned by the Now() function to the column in question. Whether this is done in the form's BeforeInsert or BeforeUpdate event procedure depends on what is regarded as the time the row is inserted, i.e. when the user begins to insert the row or when they complete doing so.

The image below is from one of my little demo files. As you can see, the difference between the two methods is nearly two minutes, which was the time between my opening the form at a new record and when I began to insert data. The pause was while I googled Darwin to find his date of birth.

View attachment 121655

Yes, that is an issue. I do not write a Function to get the Date. I use the NOW() as you do, at the table level.

That would be food for thought for others who read this in the future.

It is my policy to use as much of the built-in features and functions as possible until no longer makes sense to do so.

The function I recommend is in reference to the sequencing requirement.

 

[Ken Sheridan]

Yes, that is an issue. I do not write a Function to get the Date. I use the NOW() as you do, at the table level.

The point is not the use of the Now() function or a custom function. The key thing is when the Now() function is called. It can certainly be called as the column's DefaultValue property in the table definition, to cater for a row being inserted other than manually in a bound form, but it should also be assigned to the column by means of code in a form's module if it is to accurately reflect the time when the row is inserted via the form.

 

[Pat Hartman]

Many thanks sir, I think you are a genius, this exactly what I was battling with for many days

Generating empty records is just plain wrong and if you had proper validation would fail.

Here is a sample that shows how to generate two types of sequence numbers. It also included code to renumber simple sequences.

You need to define in your mind whether this is just a visual or if you need the sequence to define order and be permanent. Because if you don't need the sequence number to be permanently associated with a particular row, you can generate the sequence on the fly.

Custom Sequence Numbers

Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below

www.access-programmers.co.uk