Adding a number to field using Calculated value
Hello ! New here and trying to make a simple database ( is any database simple? ) .Here is my problem. I have the following fields... pkCustomer , LastName, FirstName . I want it to generate a unique ID in the pkCustomer field when I enter data in the other two fields. I need it to get the first 3 letters from the Last Name, the first letter of the First Name and then add 0001. If that ID that already exists, then I need it to add 0002. So far, I have it generating the letters by using this in the calculated value option - Left(LastName;3) & Left(FirstName;1) . that part works great . I just don't know the best way to get it to check for existing ID then add the 001 or 002 etc...
An example of what I need it to do...
pkCustomer - CusV0001 If this record already exists then it should be CusV002
Last Name - Custard
First Name - Vanilla
I hope all that makes sense. Thanks !