Drew Wutka
DWUTKA at Marlow.com
Wed Jul 1 22:01:25 CDT 2009
#2 is pretty easy to... INSERT INTO tblOrderNumber ([OrderNumber],[OrderID]) VALUES (20098000,0) Then DELETE * FROM tblOrderNumber WHERE tblOrderNumber=20098000 And whalla, your next OrderNumber will be 20098001 Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Wednesday, July 01, 2009 5:58 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Custom autonumbering Actually I don't want to mimic the AutoNumber field type. I want a custom autonumber which is 1) reliable sequential, and 2) customable. Your two-table solution provides the first requirement and I will consider it for future use. But sometimes I need to supply an easy way of creating a new offset for the autonumber: e.g. using the current year as a leading number the user should have the ability to change the number let's say 20098001 to 20100001, making this a new starting point for the sequential numbering (normally this custom autonumber field should be locked on the form and only editable providing a password). I still find the DMAX-solution easier to manage for the second requirement. But as said I appreciate your proposal for a reliable sequential numbering. Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Drew Wutka Sendt: 1. juli 2009 23:52 Til: Access Developers discussion and problem solving Emne: Re: [AccessD] Custom autonumbering I've used Autonumbers as actual values (like Ticket numbers in a request system) where the number doesn't have to truly be sequential. But in the case you are talking about, it seems kind of odd to create a routine that mimics the AutoNumber field type, instead of just using that field type (in a separate table to keep it sequential). Hope it works out. Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond Sent: Saturday, June 27, 2009 7:02 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Custom autonumbering Thanks Drew - your explanation corresponds my reading your first posting. I always use AutoNumber exclusively for surrogate primary keys, never for alternate natural keys like OrderNumber. In my opinion AutoNumber (and the equivalent in SQL Server: Identity) is designed for surrogate keys, not for natural keys. You could use AutoNumber for natural keys, but then you are inviting troubles. In Access pressing Esc on entering a new record will increment the AutoNumber, leaving gaps in the numbering. And depending on which version of Access you use, a Compact Database will reset the autonumber to the next value after your records INcluding deleted trailing records, or it will reset to the next record after your records EXcluding deleted trailing records. Bottom line: You simply can't predict the value of an AutoNumber field. This is what makes AutoNumber an excellent candidate for surrogate primary keys because this kind of keys is supposed to be meaningless. You can ignore the special behaviour of AutoNumber if you use it as a surrogate key like OrderID, but you certainly can't ignore it if you want a natural key value like OrderNumber, which is supposed to be meaningful. Asger -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.