Autonumber without autonumberId

Hi, I've got orders and orderlines. Quite a classical problem.
For instance

Ordernumber 15
Orderline
1
2
3

Ordernumber 16
Orderline
1
2
3
4

With each new order the autonumbering of field orderline should restart and automatically be refilled.
What's the easiest way getting There?

thanks

Not a classical problem. Build it with a modular function, which can be initialized.

Pat Hartman said:

Don't go at all. You know, you reallllly don't need to have the orderlines restart for every order. All you realllly need is a unique identifier. Why not just use a simple autonumber. It will be unique and will keep the line items in their entered order.

Click to expand...

I have a table orders and a table orderlines. The ordernumber is FK in orderlines, and within orderlines there's an autonumber. So I don't have a primary key issue anyway. The client wants to see how many orderlines each order has by simply automatically fill each new order number. So I need a routine (was thinking about something like Dmax(Lastfield)+1) which automatically increases the number when a new line per order is being entered. But the hazard is, what if the client types 7 lines and deletes line number 3 of 7? All orderlines should be renumbered.....
quite a messy problem, although placing an update query in an event might help even for the deleted lines

Found a solution by modifying the solution within this thread
http://www.access-programmers.co.uk/forums/showthread.php?p=338980#post338980

Within the default value of the orderline I've put
=DMax("ORDERLINE";"SQ_Salesline";"ORDERID= Forms![FRM_SALES_ORDERS]![FRM_SALESORDE]![Subform_Salesline]![ORDERID]")+1
As I use tabs/pages I first had to reference the main form, sub form, and then
the sub sub form
But it works

SQ_Salesline is a select query used as a recordset

Only prob is the sequence should be repopulated when a deletion takes place.

For instance

Line

1
2
3

by deletion of numb 2 has to become
1
2
instead of
1
3

Hi Pat, the new field will automatically be updated by Dmax.
But the old values of the already filled records should be recalculated, so requery won't do the job.

For instance
Order A
1
2
3 <--value of the new orderline

In an event a user deletes number 1, so my sequence will become
2
3<--value of new orderline

Instead of
1 -->number 2 revalued to number 1
2 <--the new value for eventually inserted orderline

So I would have to use a for next loop in code to recalculate all records 'after' the deleted record. In weekend, I've got time to figure that out as it is a complex but challenging rule also

In general language the event should be this
When :after deletion of an orderline
Condition : all records with a higher orderlinenumber than the deleted line
Action : diminish all orderline numbers higher than the deleted number with 1

Oopppss...just figured out that orderlinenumber is metadata and could be done by adding a running total (better say a running count), I guess Dcount should be able to do the job . I got fixated on this problem because it's imported data, in the old database they decided to use a database field (probably because of performance) while 'a virtual field ' is ok with nowadays technology

But..my line number is part of the primary key so it won't work

Hi pat, I will use an autonumber as my primary key.
The rulenumber will be calculated by creating a running count, will look for the code

FOund the code for the running total in here

http://www.access-programmers.co.uk/forums/showthread.php?t=71569&highlight=running+count

great query, only needed to add an extra condition (because I reset it for each
changing order)