Auto Number with the First Letters of two subsequent fields??!!

[nicramnic]

Hi Guys!

Here is the deal. First I dont know if this can be done using the AutoNumber feature but maybe you guys can help, where it can be done with a different way but also auotomaticaly.


Heres what I would like this field to do. I would like the autonumber (or soemthing else, it would not be a number anymore) to add to its field the first letter of Say field Name and First Letter of Field Surname.

What it does now is of course it goes 1,2,3....so on.

What I want it to do is to go 1MW,2RT, ....so on.

Example.

Auto Number | Name | Surname

1MW Mark Wills
2 RT Richie Tip


The auto number is primary key. But even if there is a case where the letters repeat lets say Mark Will Michelle Willard, the number will still be unique becauase the number at the front would be incremented.

Is this possible to do??

Thanks

Martin

 

[ste713]

Very similer to what im looking for, any ideas anyone?

 

[namliam]

Is this possible to do??

Yes it is posible, then again should you do this?? NO! NO NO NO NOOOOOOOOOOOO!

Primary Key is Primary and should be relatively meaningless (with some exceptions). Why add 2 characters to your primary key to be calculated from two other fields? NO NO NO!

You really should not do this

Sorry!

 

[neileg]

I agree with namliam. There's no reason why you can'y generate this ID, but not as a primary key. Any time you want this ID you can calculate it by concatenating the autonumber with the intital letters of your other fields. Something like
MyID= [Autonumber Field]&Left([Name],1)&Left([Surname],1)

Bear in mind that an autonumber does not guarantee that the next number is consecutive from the last, only that it is unique.

 

[DCrake]

Simple Software Solutions

My conclusions fall into both camps. Yes you can do this, and you may have a valid reason for doing it.

You could have a primary key field that is not an autonumber but a concat of the autonumber and the initials of the person. However what happens if 123SW (Susan Williams) get married? will you be wanting to change the primary key?

I am forever pessamistic when it comes to things like this as I always look for reasons for not doing it as opposed to reasons for doing it.

CodeMaster:

 

[georgedwilkinson]

You can definitely do it. It's called a smart key.

The most expensive smart key in history was known as the Y2K bug. There are others that have come close for individual companies, though. Those companies didn't seem to learn, either.

Good luck!

 

[Mike375]

Here is a little DB attached ready to go. Open MainForm and go from there

There is code behind the two labels but I have also left two macros there so you can see how it is done.

The second lable that inserts the number and initials into PK field could also be run on the LostFocus event for last name.

I have Echo True which lets you kind of of see what happens. False in the code or No in the macro will stop you seeing the form flash open and close

But as other have said, bad idea for a Primary Key Field