p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Sequential numbering a non auto number field (http://p2p.wrox.com/showthread.php?t=28480)

dbartelt April 22nd, 2005 06:11 PM

Sequential numbering a non auto number field
I am trying to develope an order database at work Access 97 at work. I need to make a command button that will automatically add +1 to the previous order number. Along with the order number, I need to automatically date the order. I have created the command button to create the new record and to add the date. I can not figure out how to have the order field add +1 to the previous order. Our order number will begin at 1000.

D. Bartelt

rjweers April 23rd, 2005 10:07 AM

In my (not so) humble opinion, you're doing this the hard way. Nevertheless,

NewOrderNumber = DMax("OrderNumber","OrderTable") + 1

Easier way...

Design the Table.
Set the OrderNumber field to AutoNumber type with "New Values" set to Increment.
Set the OrderDate field's Default Value to either Now() or Date depending on whether or not you want to know the precise time the order was created.

Whenever a new order is created, you get what you want. NO CODING REQUIRED! In fact, you don't even need the command button. Just let the user go to a new Order record.

As far as beginning at 1000... first of all, why not 1001? Secondly, if it has to be 1000, you could simply add 999 wherever you display the OrderNumber. Optionally, you could add 999 records to the Order table and delete them. As long as you don't Compact and Repair before you record your first real order, numbering will start at 1000.

If you've already started adding orders, you may have to deal with the fact that your OrderNumber field isn't already AutoNumber. Access doesn't like to change to AutoNumber if values are already stored in the field.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group

rjweers April 23rd, 2005 10:08 AM

BTW, I'm assuming Access 97 works like Access 2000 and up in regard to what I just described. I've never used Access 97.

dbartelt May 5th, 2005 02:56 PM

Thanks for your input. That formula works in Access 97.

D. Bartelt

All times are GMT -4. The time now is 08:35 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.