Solved serial number in subform product list (1 Viewer)

[accessonly11]

how can i add sr no automatically starting from 1 for every order i enter

 

[Pat Hartman]

The second example is what you want.

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

 

[June7]

Simplest approach is to not save this sequence number in table and to use RunningSum property of textbox on report to calculate. Anything else will require code.

 

[accessonly11]

The second example is what you want.

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

i tried the code priveded in attached db. but there is some type of error which i am unable to configure.
after hours of research i found an artical on this link

7 Ways To Do Sequential Numbering - Access Experts

This blog post is a how-to guide exploring a couple different ways you can do sequential numbering in Microsoft Access on a set of data.

accessexperts.com

which includes method no.5 .

5. But I Need it for Multiple Forms; I Can’t Have Several VBA.Collection Instances!​

The problem with the previous example is that it’s OK since you only need it in one place but if you will have several places where it is used, you now have to qualify the instance of VBA.Collection to each place – not insurmountable but too easy to get messy and hairy very fast.

However, there is a property we can use to get ourselves out of this problem and that is the AbsolutePosition property of the recordset. We can create a textbox, bind it to expression “=RowNumber()” and then create a function on the module behind the form that references the recordset:
Public Function RowNumber() As Variant
On Error Resume Next
If Me.NewRecord Then
If Me.Dirty Then
RowNumber = Null
Exit Function
End If
End If

With Me.RecordsetClone
.Bookmark = Me.Bookmark
RowNumber = .AbsolutePosition + 1
End With

If Err.Number Then
RowNumber = Null
End If
End Function
This requires no pre-knowledge of a primary key and only assumes that when you load the form initially, it is already sorted. Any records newly inserted will get the next bigger number. However, if you requery and it causes the newly inserted rows to be re-sorted, they will be now given a new number that relates to their actual position within the set, which can be either good or bad, depending on your specific requirements.

You might find it useful to ensure that the RowNumber() is called after insert of records, perhaps with this code:
Private Sub Form_AfterInsert()
'Assumes there's a textbox named txtRowNumber
'and it is bound to expression "=RowNumber()"
Me.txtRowNumber.Requery
End Sub
You also will find the code much easier to re-use, perhaps by packaging into a class module and thus encapsulate the implementation while sharing among several forms without worrying about VBA.Collections bloating since you are just using an innate property of the Recordset object.

working smooth for me.

 

[accessonly11]

Simplest approach is to not save this sequence number in table and to use RunningSum property of textbox on report to calculate. Anything else will require code.

i wish to to make coding simple as much i can, i found on above said artical, there is a simple way of rownumber available on report. like this

2. Use a Report with Running Sum Property​

If your objective is to generate a report, then you don’t want numbering to be a part of your query but rather a part of the report itself. You can use the “Running Sum” property of a textbox on a report to do all the work for you:

 

[Pat Hartman]

but there is some type of error which i am unable to configure.

It is hard enough to debug remotely but without even knowing what the error is or seeing YOUR version of the code it is impossible. Does the code work in the database sample?

If you are OK generating the number in the report (item numbers may change if things are added or deleted), then no code is required. Just set the Running Sum Property to yes and put the value. Choose OverAll or OverGroup if you want the count to reset on a group break.
=1
into the ControlSource of the field where you want to see the count.

If you want to make my example work, you are going to have to post back with actual information.

Of course, the code in the sample that generates the number is just a single line.

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtItemNumber = Nz(DMax("ItemNumber", "[tblDetailItems]", "SalesID = " & Me.SalesID), 0) + 10
End Sub

The example code increments by 10. Just change the 10 to 1 if that is what you want. The controlling group is SalesID. I don't know what your column names are so you have to work out the substitutions yourself. Your group will be the Order.

There is additional code (and a query) that shows how to renumber a list if you want to move items around.