I want to make a self generating ID field and other things

[mabz]

I am making a database and I want to make a field to generate it's own data on a table, similar to auto number. But I want it to it to be letters and numbers using the first 3 letters from another field.



For example

for company “ABC” the ID will be “ABC1”, and if there is already a company called “ABC”, then the second “ABC” company will be called “ABC2” and company “AAB” will be “AAB1”

The first 3 letters should be from the name of the company and the number is set it aside from other companies with the same first 3 letters.

Can anyone help me with this problem? :cry:

[StepUP]

Create an autonumber field, and then on the "Before Update" event of the form, add some code to concatenate the company ID with the autonumber field.

e.g.

Me.CompanyAndNumber = Me.CompanyID & Me.AutoNumField

[DanInManchester]

I don't like manual PK's myself so I avoid them but your code will need to be something like this :

this will get you the next primary key number to insert :

SELECT Max(mid( CompanyID ,4, len(contentid)-4)) + 1 as NewValue
FROM tbl_Accounts
WHERE CompanyID LIKE 'ABC%'

new key would be

strID = "ABC" & rs("NewValue")

The mid function is getting the part of the company ID that is numeric. It assumes that it is always preceeded by three character.

You may need to tweak this a bit.