Find last (most recent) order and add 'one' to OrderID

[stonward]

Hi folks,

Happily installing my new system, when the buyer pipes up saying he needs to continue using his old invoice number sequence.

So I've had to remove my autonumber sequence for Customer Orders (OrderID). When the orders form opens it needs to add one to the last (most recent) orderid and use as orderid.

I can't just find the largest orderid and add one, 'cause the numbers the user has in the past are a mess (and he doesn't want to change them!).

I've tried adding a field to the orders table (mycount) and adding one to that, but couldn't get it to work....I feel a query to find the most recent is the way to go, but I wanna find just the most recent one - not a list.

Not sure how to go about this (normally I'd just graft at it for weeks - but I'm really pushed today!).

Thanks

RPE

[Seth Schrock]

You could query your table setting the Order By to have the newest on top and have the query just get the first record using the SELECT TOP 1 ... FROM .... You could then use DLookup() to get the OrderID and add 1.

Just curious, do you have a time field so that the records can be sorted not just by date (as I would assume that there could be many orders on the same day), but also include the time so that you could have more assurance that you did get the newest record?

Also something to consider would be that if the previous order numbers are a mess, are you sure that you can always just add 1 and not conflict with another order ID? If you can't just use the highest Order ID, then you will eventually run into a situation where adding 1 to the previous number will duplicate the Order ID. So I would suggest possibly using a loop to add 1 to the previous Order ID and then check for a match. If one is found, then loop back through and add 1 again and then check again, and so on. A loop might not be exactly what you want, but possibly a "Manual loop" where you use a GoTo to loop back up might be. I haven't come up with an exact solution, but I think something along this line needs to be considered.

[TheSmileyCoder]

Why exactly can't you simply add 1 to largest current ID? How will you prevent duplicates if you just add 1 to the most recent and the currently used order numbers are a mess?


My suggestion would still be to use autonumber and insert the existing numbers (the autonumber will then automatically continue from the largest number).

Of course this can be accomplished in code, but I don't see a good reason to apply a code based custom solution for something autonumber handles so efficiently.

[stonward]

The user has added numbers to the usual 6 digit number to account for returns and such like. I've now also found gaps in the usual sequence which also complicates updating as an autonumber sequence. It's just nasty.
Thanks for your time.

RPE

[NeoPa]

Many IDs are not specifically numeric. People often want to see information within their IDs such as, for instance, a year value and/or something to indicate a parent for sub-tables.

The usual way to handle this is to use a DMax() call with a filter specifying the pattern that it needs to match. Sorting then selecting TOP 1 is an innovative approach, but possibly more complicated than necessary.

[Seth Schrock]

I never remember those domain functions (except for DLookup). DMax would probably work better since my idea already had a domain function in it and DMax just combines the two steps.

Thanks for pointing this out NeoPa.

[stonward]

Hi Guys,

NeoPa's is the best (most correct?!) answer methinks, but i have used Seth's purely because I know and understand those particular domain functions rather better.

But neoPa's idea will find its way into my first update, I feel certain.

As always, great thanks for your time and patience,



Stonward

[NeoPa]

Great attitude. I'm going to reset Best Answer for you, but only in order to choose the post of Seth's where he gave his answer.