Autonumber without autonumberId

J

JohnGo

Registered User.
Local time
Today, 08:15
Joined
Nov 14, 2004
Messages
70
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.
 
What's the easiest way getting There?
Click to expand...
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.

The problem I see is that if you don't know how to code, you reallly shouldn't be messing around trying to create a custom sequence number. There are lots of posts here on the topic and I'm sure you can find something that seems to work, at least in a test environment.
 
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
 
If all he needs is a count, add a footer to the subform and add a Count(*) that way you won't need to store a value that may need to be recalculated. Also, if you search here, you should find examples of sequence numbers generated in queries. This will renumber the lines on the fly which is probably more of what the user had in mind.
 
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
 
Try requerying the form after a record is deleted.

Me.Requery
 
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

:eek: 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 :o
 
But..my line number is part of the primary key so it won't work
 
Primary keys should not change. They should not be composed from "intelligent" parts. Use an autonumber as your primary key. Displaying a running count is quite simple in a report since it is only a property setting. Displaying a running count in a query is more difficult but if you insist, there is code here that will do it but you'll need to search for it. If the count is calculated by the query rather than stored, requerying will cause the running count to be recalculated.
 
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
 
You must log in or register to reply here.

Similar threads

D
Darshan Hiranandani : Can anyone suggest how to format ascending numbers for varying input types?
Replies
2
Views
250
GPGeorge
GPGeorge
T
Ticket ordering system
Replies
4
Views
427
The_Doc_Man
The_Doc_Man
N
Add RowNumber Problem
Replies
11
Views
601
CJ_London
C
adhoustonj
Revising order detail lines with between Lot number
Replies
7
Views
516
adhoustonj
adhoustonj
N
Main Form with 3 Sub Forms.
Replies
6
Views
290
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom