Numbering Sequence/Autonumber Problem...

[smbarney]

I need to create a button that generates a sequential four digit requisition number and places that number in a field along with some text. I cannot use autonumber because this number is not and cannot be the primary key. In addition, I need to be able to reset the number to 0001 at the beginning of each federal fiscal year. Here are the details:

reqtype: An option group that has these choices: PC, TL, and RQ
actionyear: A drop down box that lists the current fiscal year: 2007
reqnumber: A field that, when the button is clicked, combines a text prefix, reqtype, and actionyear with a four digit sequential number.

The end result should look like this: SEC-07-PC0010 and be recorded in the reqnumber field.

My code writing abilities are limited. Could someone please help me out. Thanks.

 

[The_Doc_Man]

Good news and Bad news.

The bad news is you cannot do this in a simple field and still get the desired results easily.

The good news is it isn't that hard otherwise.

The parts you show are SEC - 2-digit-year - CODE sequence

If SEC is constant, ignore it. Supply it later.

The code and year are obviously selected.

The sequence number is unique per year or per code-year combo?

If unique per year, then you want to use a DMax to find the highest sequence number having the given year. If unique per year/code combo, find DMax for the highest sequence number with the year and code.

Which means that year and code must be SEPARATE fields for the purpose of number assignment.

Once you have the Dmax, you can add 1 to it to get the next available number. Once the year changes, this number just starts over again from 0.

If you don't like DMax, DCount is also possible as long as you don't ever delete a sequence number once it is assigned. In either case, add 1 to get the next number.

Read up on Compound Keys in order to understand that you don't need a single field as your PK. Only the compound key needs to be unique for tables that use compound keys. Component key-members don't individually have to be unique. In your case, the combined year, code, and sequence number appear to be a candidate for the combined prime key for this table.