ju*********@derrickcompany.com wrote:
Hello,
I currently have a field named QuoteNumber in a table named Quotes.
The field is set as autonumber and is used to assign sequential numbers
as users enter information in the table through a form. This number is
mainly used later as reference when a customer calls our office with
questions. We use the quote number to retrieve their related
information through a query. This QuoteNumber field is NOT a primary
key. It is not important that the numbers be perfectly sequential, as
in when a record is deleted before saving, then the assigned autonumber
is lost as well.
Now my question/problem. I have formatted the autonumber field to view
as "05-000" where 05 represents the year, and the three digits after
the hyphen increase sequentially. However, I did not think ahead when
implementing this design, and now that it is 2006, I need to change the
numbers in front of the hyphen to 06 and reset the numbers after it to
start at 001. All previous quote numbers with the 05 prefix must
remain intact. I am thoroughly stuck and would appreciate any
constructive ideas, changes, workarounds, etc.
Jason
Do you have a date field. Like QuoteDate? If so, you can format it so
the "05-" and "06-" are correct.
If not, create a new field called QuoteYear. I'll assume this is a
recent app since you just experienced this problem. Update all quote
records to "05"...to the last Quote for "05" and everything else is "06"
Now...regarding your numbering scheme. Open up the table in design
again and create a new field called NewQuoteID, longint. Now create a
query the updates NewQuoteID with the QuoteID number. Review to see
that is correct. If so, open the table in design and kill off the field
called QuoteID. Save the table. Now rename the field NewQuoteID to
QuoteID. You may want to renumber the existing records for 2006.
There's a couple of methods for assigning the quoteID. One uses DMax().
You check and find out what the max number is for that year and inc by
1. Or you can have a table that stores the years. It has 2 fields;
year, curnumber. When you create a new quote, it gets the current year.
It then reads the table and sees if 2006 exists (or whatever the
current year is). If not, it appends a record and sets the curnumber to
0.
When you SAVE the record...if you want to keep it sequential...you would
update the QuoteID in the BeforeUpdate event if it's a new
record...either by calling the Dmax() function or a function to read the
year table, get the current number, inc by 1, save it to the table, and
storing that value as the QuoteID for the current record.
You REALLY want to generate the quoteid when the record is saved. Why?
Because 2 people might create a Quote record around the same time and
you could end up with 2 quotes having the same number.
Other folks may have some better alternatives. Regardless, you have a
little bit of work ahead of you...not much...but enough to slow you down
for a couple of hours.
