Automatically creating a primary key without autonum

[ets960]

I'm pretty new to access and I've been working on a pretty complex project...

Anyways, my first problem I'm having is that I want Microsoft Access to automatically create a key number. This number depends only on the year, and then it would concatonate another number.

Example: 5-3000, then 5-3001, 5-3002... The five corresponds to the year, and the other part is just incrementing by one for each new record.

What would be the best way to do this? I don't necessarily need the dash in there, it could simply read 53000, 53001, 53002, etc.

Thanks for the help!

 

[gold007eye]

Go into the design view of the table and right click on the field you want to be your primary key and click the "primary key" icon. then go to this field and modify the "format" properties to something like "5-0000" that should make 5 the constant and the 0000 should change incrimentally.

 

[ets960]

Thats a great solution...

Can I reset it when I come around to 2006? I could stop by the client's place to do it, but is there any other way to do it?

Thank you so much, if there's no other way I'll probably just do that.

 

[gold007eye]

when you got to 2006 you could just change the 5 to a 6 I am not sure how to make it change automatically.

 

[ets960]

http://www.access-programmers.co.uk/forums/showthread.php?t=75516

That person has something similar, I'm just not sure if I can adapt that to work for me (also since that is in Access >97, I'm not sure if all the commands will work with 97)

 

[The_Doc_Man]

Do not take this approach. When you have a key that MEANS something, you cannot use autonumber, you cannot reset it, and you cannot compound it and get the effect you wanted. Autonumber has NO OTHER MEANING than a unique, often not predictable number that takes up 4 bytes and gives you a suitable field for being a prime key for any number less than 2 billion records total for the life of your application.

I'm going to presume that you want the key to be something like yyyy-nnnnnnn and the yyyy part resets each year and when yyyy resets, the nnnnnnn part resets to 0 or 1 and starts over again. (This presumption is based on the most common form of key question we see here.)

First, when your candidate key has two parts (at least one of which has meaning), you must split the key into two parts. Then your candidate key is the COMPOUND KEY that contains the two parts. Trust me - if you try to do this ANY OTHER WAY you will hate yourself very quickly. Trying to rebuild a single field that contains two parts is just pure agony because of its need to permeate the entire DB. So if you must do this, do it in two parts.

Second, the yyyy part can probably be generated from a DatePart function when you create the record. It is easy to get the four-digit year number from many sources.

Third, the nnnnn... part can be generated with a DMax function that includes an NZ function. What you want is something like

Next sequence number = 1 + Nz( Dmax( sequence number, table, year = current-year), 0 )

In other words, 1 + the highest existing sequence number for this year, or 1+0 if this is the first entry (which would cause DMax to return nothing, hence the need for NZ).

BUT....

If you really want this to be your primary key, you will STILL hate yourself later even if you did as I suggested with compounding the key fields. Complex keys in Access are possible but are a pain in the toches. You can generate a number that looks like this, but you really, really, REALLY would be better off if you just used a "true" autonumber field and never showed it to anyone as a displayable element. Then this sequence number you could generate would be all "for show."

 

[ets960]

Thank you, your advice has been heeded. I appreciate all the help, I will keep an autonumber field and will create that other part for show. Thanks a lot!

 

[ets960]

Can I do this inside the table by just setting the default value to that function? Or do I have to create some code that will do this in a form?

Should I put those extra values inside the MAIN table that I am using (e.g. YEAR and SequenceNumber), or should I create a new table that will determine the next number every time?

here's the code that I was trying to put in the default value box:

=1+Nz(DMax([NextSequence],[MAIN],[Year]=Year(Now())),0)

It says it doesn't recognize [NextSequence] as a valid expression.. This is the table variable that contains the number of the next value I want to use...

 

[Mile-O]

Do it in the form's BeforeUpdate event although you should look up the DMax() function in the help files to find out how it works as your current effort is incorrect.