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

__________________
D. Bartelt

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
http://www.pnwadg.org

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.

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

D. Bartelt