create Automatic number for each item in the Detail Order table

[Nhung]

Hello guru,



I have 2 tables: Order table and Order Detail table
(Order table has fields: OrderID, date)
Order Detail table has fields: OrderID, Sequence number, product, quantity)

Now I create a form along with a sub form to enter data for Order and Order detail.

My requirement: I need the "Sequence number field" is created Automatically with auto number for each Order (not for all order). It means that in the sub form, when user input data for the next record, system create the new number = above number +1 automatically

Order 1:

Sequence number	Product	Quantity
1	Shoes	20
2	Skirt	30
3	Hat	40

Order 2:

Sequence number	Product	Quantity
1	Book	40
2	Mug	20
3	Hat	10
4	Paper	6

[June7]

Generating custom sequential ID within a group and saving in field requires code. This is a common topic and has been answered many times. Search forum and web for examples.

Alternatively, textbox on report has RunningSum property which can be used to calculate this sequential number on report.

[CJ_London]

As June says, a common topic - refine your search by looking for 'dmax+1'

[orange]

nhung,
You have responses to your specific question, but there must be more to your database.
Order and OrderDetail makes me wonder where are Customer, Product, Supplier...?

I don't see Price in your OrderDetail table.
Good luck.