Changing Prefix of an AutoNumber field

[Nilay]

I have a table 'Invoice', which has an autonumber field 'InvoiceNumber'. The prefix on the autonumber is '0809', that being the current financial year. (Btw, I have given the prefix by specifying the format of this field as "0809"0). I would like to change the prefix to "0910" beginning from Apr 1, 2009. Problem is, when I change the prefix, all the existing records get the new prefix; while I want the new prefix only for the new records. I'm hoping there is a solution around, and appreciate all help. Thanks

 

[Rabbie]

You will need to use code to decide which prefix you should use depending on the date.

Are you aware that Autonumbers can skip if there is a hitch with the DB. they are not guaranteed to run in strict sequence - they can jump.

 

[Nilay]

I'm aware that there is an option to have autonumbers appear in sequence or random; I want them in sequence. It it only the prefix that I want changed for new invoices. I guess 'use code' means some programming, which I am not well versed with. I've used 'if' conditions in queries, but don't know how to apply them for tables - like 'if year so-and-so, then prefix so-and-so' (format criteria in autonumber field has 'Build' greyed out on right-click). Could you post a small code to give an idea; I'll play with it to see if I can use it some way. Thanks

 

[Rabbie]

Sorry I was not clear in my previous post. Autonumbers selected in sequence can still miss out numbers for various reasons. You are not guaranteed a a run of numbers with no gaps. This has been referred to many times on this forum. Also your autonumbers will not reset the sequence when you change the prefix.

I always use a Function to get the next available Invoice number adding 1 to the previous highest value for a given prefix.

Autonumbers are pure Long integers normally starting from 1 and incrementing so you need to concatenate the prefix onto the front to give your number.

I am at work just now so I don't have time to post some code but I will try this evening.

 

[gemma-the-husky]

nilay

rabbie is correct - but are you saying you are not actually storing the prefix in your file - just the incrementin number

if so, how does your app "know" which year to allocate - is there a date field avaialble, or will you have ot use the invoice number itself?

 

[Atomic Shrimp]

If you want an incrementing number to have meaning, or obey rules, autonumbers just are not the way to do it, because of the above mentioned behaviours.

I think what you need to do is store the prefix in its own field, then concatenate it with the invoice number in a query, then using that query as the source for your forms, reports, etc.

You could still use an autonumber for the invoice reference this way, provided that you don't mind possible gaps in the sequence, and don't want to control it in any other way.

 

[Nilay]

Thank you all. I do have a date field.

I think what you need to do is store the prefix in its own field, then concatenate it with the invoice number in a query, then using that query as the source for your forms, reports, etc.

This sounded easy to less knowledgeble me, so this is what I did. I have now added a field 'Prefix' in the table (current default value is '0809-', will change it to '0910-' from Apr 1), so the invoice number now is 'Prefix' & 'InvoiceNumber' (autonumber field).

One more question - as the situation is, from April 1, I'll start having invoice number as 0910-283 (this being approximately 283rd invoice in the table, where 283 is the autonumber). Is there a way to reset this number to 1 without changing existing numbers, so the the new year invoices will start from 0910-1 ? Thanks

 

[wiklendt]

yeah, see i think you're still on the wrong track. InvoiceID is something totally different to InvoiceNumber. I understand that InvoiceNumer can be unique etc, but with autonumber you can't really change anything. i would leave InvoiceID with autonumber to run it's course. i would then have the prefix field, and then another field for the appropriate invoice for that month/date/whatever you're incrementing with. i don't think it's a good idea to make either InvoicePrefix or InvoiceNumber have defaults in the table. i think they need to be made on-the-fly via your data entry form. that way, when you need to increment again, it's automatically done for you.

InvoiceID doesn't ever have to be 'seen' - it's just there to make it super easy to know which one's which. and super easy to make relationships with other tables.

 

[Atomic Shrimp]

I agree. Autonumbers are generally intended to create unique record identifiers that will not have any meaning to the end user - and will often not even be visible to the end user, just used behind the scenes to keep things tied together properly.

It sounds like you're going to have to implement your own invoice numbering - either by looking up the highest existing number and adding 1 when you want a new record, or by keeping a single-record table of parameters, one of which is the last used (or the next available) document number.

If you're not absolutely constrained by some accounting practices also in use, I would recommend thinking hard about the idea of numbering invoices from 1 again each year, because of the possibility of confusion or error - seems like... well, not a recipe for disaster, but a serving suggestion for sure.

 

[Nilay]

Thank you all. It is forums like these that encourage individual self-trained users like me to experiment with Access, knowing that if there's some problem, answers will be around. Here's what I did - I dropped the idea of continuing with autonumbers for invoice numbers. Instead I converted it to a text field; this deleted all the entries (invoice numbers) in the field and in the related 'Product' table (the common field was InvoiceNumber). But I had physical copies of invoices, so I carefully and manually entered the invoice numbers in all the records of both tables. I'll now be manually entering the invoice number of my choice and format; only additional effort would be to check which was the previous number.

But I've learnt the moral - to avoid using autonumbers for important fields as far as possible. Thank you all once again.

 

[wiklendt]

and a tip: when i have to change the data type of a field, what i normally do is create a new field of the end-type i want, then it's easier to either copy over the data, or use an update query (if appropriate - i haven't mastered these yet).

and always, always, always, always, always, always, always, always make and keep backups of your database - i have about 10-20 consecutively numbered backups EACH DAY for my DB's depending on how much i work on them.

at the end of the week, i zip up these backups and put them into a "developemental" subfolder on my computer. if EVER i need to go back to ANY point, i have them ALL there.

 

[Rabbie]

Thank you all. It is forums like these that encourage individual self-trained users like me to experiment with Access, knowing that if there's some problem, answers will be around. Here's what I did - I dropped the idea of continuing with autonumbers for invoice numbers. Instead I converted it to a text field; this deleted all the entries (invoice numbers) in the field and in the related 'Product' table (the common field was InvoiceNumber). But I had physical copies of invoices, so I carefully and manually entered the invoice numbers in all the records of both tables. I'll now be manually entering the invoice number of my choice and format; only additional effort would be to check which was the previous number.

But I've learnt the moral - to avoid using autonumbers for important fields as far as possible. Thank you all once again.

That's not really the moral to be learnt here. The real lesson is to use Autonumbers for what they are designed for - To act as the Primary Key of the record. If you need a field to increase in strict sequence then use a function that finds the current highest value and adds 1 to it. This is easily adjusted to reset to 1 if you want to have a different prefix.

Anothe valuable lesson here is to do as wiklendt suggests and take backups before any changes.

Finally it's always a good idea to get your design right before you build and populate your DB.

 

[boblarson]

I second Rabbie's statement. Autonumbers are commonly misunderstood. They really are not meant for "human consumption." That means that they should be used by the SYSTEM - "Behind The Scenes" and not where someone can see them and expect things out of them. They are there to give a UNIQUE key to a record and then you can use that key as a foreign key in another table. Unless you really understand their limitations the reason they are there, you really shouldn't use them for anything else. They can be useful at times for other stuff, as long as you have a clear understanding of what they will and won't do, and how they can act (even unexpectedly at times).