Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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.
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:09 AM.


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