Autonumber settings (1 Viewer)

[Chrisopia]

Firstly i find it annoying how autonumber doesnt begin from the beginning if you delete all the records and start again...

is there a way around this without recreating a brand new table. Obviously alot of testing will create alot of deleted records.

Also, I need autonumber to to be 4 digits long... anything smaller will need to be preceeded by naughts (0)...

is this possible with auto numbers?


Thanks a bunch

 

[ColinEssex]

It should not matter what the Autonumber is.

An Autonumber is purely an internal link, it's purpose is to link records in different tables together using a unique number. You should not try to use an Autonumber for anything a user might see - for example, an account number.

Col

 

[Chrisopia]

Thanks for that, but it didn't really answer my question
I've exhausted DMax and it didn't really work, so Im using autonumber, but it doesn't want to count.

I know the purpose of Autonumber is to link tables primarily without receiving duplicate numbers but I thought it would double as an invoice number for example,

makes sense, if only I could customise it. I know a long way of adding naughts

Swtich([ID]>10,"000"&[ID], [ID]>100,"00"&[ID]... etc

Thing I need to get around, or perhaps if Im just really careful is the fact it doesnt neccesarily start from 1... hmm...

 

[raskew]

You will not be able to accomplish what you're asking with Autonumber.

Autonumber is a number (obviously). 'Real' numbers do not have preceding "0's", thus it's going to require a text field. Here's an example:

x = "0032"
? format(x + 1, "0000")
0033

BTW, compacting an db with an empty table will reset that tables autonumber.

HTH - Bob

 

[Rabbie]

As Colin so rightly said you can't use Autonumber for this. Have a separate field for your invoice number and use something like this to get the next invoice number in sequence.

Function NextInvoice() As Long
' Obtains the highest used Invoice number and increments by 1
    NextInvoice = DMax("invoiceid", "invoices") + 1
End Function

Use a FORMAT statement to output leading zeroes in your reports and forms

 

[RainLover]

In the Table's design view place 0000 in Format.

 

[boblarson]

An autonumber only guarantees (and not 100% guarantees) you a UNIQUE number. It does not guarantee it will fall in a certain range, nor will it necessarily be a positive number (yes autonumbers can be negative), and it doesn't guarantee that they will be consecutive (it is possible for them to jump even if you have increment as the default).

So, if you can live with possible negative numbers, possible 8 digit numbers, jumping numbers (and you can wind up with gaps due to records starting and then being canceled or deleted records) then you might be able to use it. But you are trying to fit it into something that it doesn't do well - have meaning.

 

[gemma-the-husky]

chrisopia

autonumber will restart at 1 if you delete data, then compact and repair

the point is that you just cant use autonumber to manage a sequence.

Two alternatives

a) when you need an invoice number, use a dmax to find the highest number used, and add 1 to it - but this isnt much use by itself, if you are processing a batch of invoices - in that case you need to store the retrieved number in a variable, and then increment the variable

b) so alternatively store the next invoice number to be used in a separate table, and read it from there when you need it - this is better than using dmax anyway, as you can manually change it to whatever number you want to use, and i am sure 99% or more commercial systems manage references to invoice numbers, batchnumbers, and all sorts of reference numbers in this way.

 

[Rabbie]

chrisopia

autonumber will restart at 1 if you delete data, then compact and repair

the point is that you just cant use autonumber to manage a sequence.

Two alternatives

a) when you need an invoice number, use a dmax to find the highest number used, and add 1 to it - but this isnt much use by itself, if you are processing a batch of invoices - in that case you need to store the retrieved number in a variable, and then increment the variable

b) so alternatively store the next invoice number to be used in a separate table, and read it from there when you need it.

Of course remember to increment the Next Invoice number everytime you use it or you will get duplicates. -

this is better than using dmax anyway, as you can manually change it to whatever number you want to use, and i am sure 99% or more commercial systems manage references to invoice numbers, batchnumbers, and all sorts of reference numbers in this way

Gemma, Have you any evidence for this assertion or is it like most statistics made up on the spot

 

[gemma-the-husky]

i am a qualified accountant - every commercial accounting system i have ever seen stores a central table with next document references, covering things like next doc ref for invoices, credits, cash postings, journal vouchers, etc etc - even extending to things like stores/product refs, fixed asset refs, personnel ids - indeed many personnel systems do not accept any id number, but use the last number as a check digit.

although we mention it as a possibiltiy, I have never seen a system which determined a reference number by using a lookup on existing data.

some of this may be a vestige of 3GL systems thinking, where autonumbers werent possible, but i feel it also adds benefits that you wouldnt get with a dmax type system

i certainly feel that using a dmax is unnecessarily restrictive, eg, what if an organisation wants to start numbering invoices with a year/month prefix - eg 0902-00786 (the hyphen is just for illustration) - you just cant do this automatically with a dmax system

[edited]
or what if a system is designed to have a text reference (rather than numeric) - eg a fixed asset reference, or a nominal ledger code reference - you cant do those easily with dmax's. and although you COULD do these with an autonumber key, get the numeric bit of the reference with a dmax, and get the alpha bit, based on some sort of lookup - in my experience this isnt how these things get implemented. A NL code is particularly relevant because these would NEVER be implemented sequentially, as they ought to follow a naming and grouping convention of some sort

In a situation like this, where an identifier is appropriate, I tend to have an autonumber primary key, and then let the user enter his own identifier, ratherbvthna use the identifier as the PK

 

[Rabbie]

Gemma, Thanks for your explanation. I must admit that I hve never has any problems in producing sequential Invoice Numbers as required by HMRC using the Dmax method although I do call a function to provide the next number. This seems to work well even when producing a batch of invoices(Month end). I can see the attractions of using a table to look-up the next value but this does require to be updated explicity everytime a number is used while the DMAX method does this automatically.

Professional systems are probaly more rigorously then many of the Access systems referred to on this forum so anythin that will get it right is an advatage

 

[gemma-the-husky]

Rabbie

i edited my previous post, so have a look

In a complicated sytem I have, I do invoicing by storing the batch of new invoices in a temporary table - i then allocate invoice numbers to those transactions, and when the whole run has processed, i update the system with the details in the temporary table

one reason for this was that while developing and testing i could abort the invoice run at any point before the final process, and just rerun it. I actually collect the next invoice in a variable, increment that in the invoice process, and store the final number after the invoice run completes, as typically I am generating 100s of invoices at a time.

I dont think there is much practical difference between getting invoice numbers from a separate table, or from the invoicing table - except that in the latter, its hard to start from a particular value.

 

[Rabbie]

Rabbie

i edited my previous post, so have a look

AS a professional programmer it is not hard to use DMAX in conjunction with a prefix to produce your suggestion. Just calculate the incrementing part and merge with the prefix

In a complicated sytem I have, I do invoicing by storing the batch of new invoices in a temporary table - i then allocate invoice numbers to those transactions, and when the whole run has processed, i update the system with the details in the temporary table

one reason for this was that while developing and testing i could abort the invoice run at any point before the final process, and just rerun it. I actually collect the next invoice in a variable, increment that in the invoice process, and store the final number after the invoice run completes, as typically I am generating 100s of invoices at a time.

I dont think there is much practical difference between getting invoice numbers from a separate table, or from the invoicing table - except that in the latter, its hard to start from a particular value.

Again quite easy by providing default value to a NZ function - DMAX retuns Null if it can't find a value

I can see the advantages of using a temporary table especially while testing but i really think that it is six of one and half a dozen of the other.

 

[neileg]

Trouble is that DMax will return the same number if a second user starts a record before the first user has saved. Some good ideas on this thread http://www.access-programmers.co.uk/forums/showthread.php?t=148279
Alterntively, allocate the number as the last step before saving the record. This helps to reduce the gaps in the sequence due to abandoned records too.

 

[Rabbie]

Trouble is that DMax will return the same number if a second user starts a record before the first user has saved. Some good ideas on this thread http://www.access-programmers.co.uk/forums/showthread.php?t=148279
Alterntively, allocate the number as the last step before saving the record. This helps to reduce the gaps in the sequence due to abandoned records too.

True Neil, tho' any method can result in the same number being returned if two attempts are made close together. I always have the incrementing field indexed with no duplicates so an error will be quickly spotted and the next number then allocated. And as you say if you get the number just before you save the number the risk is substantially reduced.

 

[gemma-the-husky]

True Neil, tho' any method can result in the same number being returned if two attempts are made close together. I always have the incrementing field indexed with no duplicates so an error will be quickly spotted and the next number then allocated. And as you say if you get the number just before you save the number the risk is substantially reduced.

i forget which book I got this from - a suggested method of retrieving safely the next record without the possiblityof multiple access is for the user requiring the number to lock the table before the read and write.

they suggest having an unlinked table in the back end, and open it by directly opening the backend table, locked, retrieving the number, and then releasing the lock, which guarantees no multiple updates.


'looked up the reference now - the example was taken from

' From Access 97 Developer's Handbook
' by Litwin, Getz and Gilbert. (Sybex)

 

[Rabbie]

Excellent suggestion. Especially where there is a risk of several users requesting the next number simultaneously.

 

[Chrisopia]

you are all too clever

Thanks for explaining the flaws of using autonumber, I am confident now to attempt a Dmax version...

Its also interesting in using a seperate table to create the counting... this made me laugh, because I need to filter some data into a seperate table anyway. The invoice number or the quote number from tblInvoice and tblQuote,

using an append query to set filter through my tblOrder to desipher what is a quote or an invoice.

The result was going to be an invoice number (related to tblInvoice's autonumber). Instead of copying the realted tables for tblInvoice and tblQuote (thbOrder with tblOrderDetails)... I was just going to reference the relevant table information for an invoice number.
(e.g. the control source of the invoice box wouldn't be the same as the rest of the inofrmation on the page)

does this make sense?

 

[neileg]

i forget which book I got this from - a suggested method of retrieving safely the next record without the possiblityof multiple access is for the user requiring the number to lock the table before the read and write.

they suggest having an unlinked table in the back end, and open it by directly opening the backend table, locked, retrieving the number, and then releasing the lock, which guarantees no multiple updates.


'looked up the reference now - the example was taken from

' From Access 97 Developer's Handbook
' by Litwin, Getz and Gilbert. (Sybex)

One of the suggestions in the thread I linked uses a locked table to do just this. Not my idea, taking no credit.

 

[Rabbie]

Thanks forthat, Neil. Can you post the page ref so I can look it up tonight.