Create a sequential number in Access; counting from 1 to a given "Qty"

Question

Hello,

So this is what I am trying to do:

Create a serial number using Microsoft Access. This serial number is based off of an appended: [sales order number]&"-"&[Line Item]&"-"[Sequential Number (counting from 1 to the quantity of items in the sales order)]

I have looked into several different methods, such as trying to use the auto number field in a new table and append it to another table, but have not found a good clean solution to what I am trying to do.

Does anyone have a suggestion, including VBA code to do this?

I would really appreciate any tips that you might be able to give.

Thanks again

Answer 1

See my blog on Sequential Numbering (blog address in sig). It has what you need to do what you want.

Using an autonumber is definitely not the way.

Answer 2

Take a look at CustomNumber.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file includes an option for 'Sequential Numbering by Group' which computes the values for a NameID column which is sequential per gender in a NamesByGender table.  In your case you'd do the same in an OrderDetaills table per OrderID.

You'll also find something similar in the ParentActivities demo in the same OneDrive folder, where the number is computed in code in the module of the activities subform within the parents form.

However, you almost certainly don't need to insert the number into a column in a table, but merely return it in a computed column in a query at runtime.  You'll find examples of queries which number rows per group in the RowNumbering demo in the same OneDrive folder.

In a report you can do this very easily by including a text box in the detail section of a report grouped by order, setting its Controlsource property to =1 and its RunningSum property to 'Over Group'.