Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 22nd, 2005, 06:11 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
__________________
D. Bartelt
  #2 (permalink)  
Old April 23rd, 2005, 10:07 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old April 23rd, 2005, 10:08 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.
  #4 (permalink)  
Old May 5th, 2005, 02:56 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

D. Bartelt


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access sequential numbering in a query ChadKempel Access 3 June 4th, 2007 12:49 PM
Sequential numbering help gear1 Access 1 April 9th, 2007 06:31 AM
Populate Auto-Number Field JeffGirard Access 2 August 26th, 2005 06:49 AM
Create Sequential Numbering That Does not Skip mrslockdown2003 Access 4 May 11th, 2005 11:45 AM
Sequential Row numbering in queriws civa Access 5 March 7th, 2005 02:13 AM





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