Customizing autonumber

[Nyanzi]

Dear All,
I would like to customize my autonumber so that it is preceded by three letters. For example instead of having it run as follows

ID
1
2
3

it would be something like this:

ID
KLA1
KLA2
KLA3

This field will then be used as my primary key. The reason i am doing this is because i have data from 3 different databases being appended into a central database. This unique field will then ensure that every record from each of the databases is appended into the central database.

Thanks

 

[afryer]

Hi,

As far as I know there is no way to do this because the field is by definition a number meaning that you cannot use letters in it. What I would recommend is one of the following:

1) Use a single text field primary key and generate the keys automatically. You could do this by taking your text field and getting the MAX of that particular key type, i.e if it was KLA and you had 20 instances KLA1-20 then doing a max on the field would return KLA20.

l_new_index = mid (<key_value>, 3) + 1 ' get the new key
l_new_key = "ABC" & l_new_index

2) Use a compound primary key with fields for the three letter identifier and another for the unique number. If you use this method then I wouldn't recommend using the autonumber field because the numbers will get very big very quickly.

I would recommend using option 1 as this would take less time to produce.

Hope this helps.


Andrew

 

[Nyanzi]

Hi afryer,

I am quite lost but i want it to act just the way an autonumber does, to be automatically generated when i enter a new record.
Thanks

 

[PHV]

You have to code something in the BeforeInsert event procedure of the form.
Take a look at the DMax and Mid functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

Hi, Nyanzi
We had a discussion about Autonumbers. The programmers answered the thread are quite right. Please check it.

thread700-712983
thread700-737758

Regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com