Dear all,

In my table, I have a field called employeeAutoNo that has Auto Number data type. E.g: 5.

I have another field called EmployeeID. E.g.: ABC/5/2008, where 2008 is the year & ABC is a contant.

How do I get the get the value from employeeAutoNo and concat with the constant and year (year is taken from System Date) and then store into the EmployeeID field?

Thanks

The problem you are going to run into here is that the autonumber is not assigned to the record until After the record is created. So you couldn't create a new employee and then carry out the concatenation, and then forget seeing the generated EmployeeID until the next time you navigate to the record.

You can create discrete transactions that will generate this all at once, but if you have multiple users, you run the risk of having one user create a new record, then a second, and the first runs the transaction and takes the wrong PK as the center part of the EmployeeID.

Typically this kind of information can be generated as needed, but that may not help since the look up might be problematic.

I would suggest creating a WhoCreated field, and a DateCreated field in the table, and then capture the user's username that created the record (generally (Environ$("Username")) does the trick). Where you want to put the transaction is up to you. You can use the user's name and the MAX(employeeAutoNo) to find the last record they created and generate the key that way.

I am sure there are more elegant ways of doing this, but the fact that the autonumber is not generated until After the record is created is the limiting factor.

Did any of that help? Do you need code?

mmcdonal

Look it up at: http://wrox.books24x7.com