I have an application I an creating. I need to create a unique Key on the form that will be used in other databases later on.
Basically It will be formatted like this: H0600001
The H is standard for the project
Using Datepart and Right,2 I am able to pull 06 from 2006
I have created a table called tbluniquekey with 1 record. It does not have a primary key assigned. The starting number will be 00000 (five zeros) I used DLookup to pull the only record.
What I am doing is combining the 3 above items to one field. I can get the H and 06 okay.
What code would I use to get 00000 to be 00001. I have used FIELDNAME (for the o's) + 00001 (VBA Code). It always changes to 00001 to 1. Then the result is H061. I want H0600001. I don't want the zeros removed
My next challenge would then be to save that record with the unique key. Which is easy after I get the correct value. Then go the the uniquekey table that I did the DLookup on and change the number stored from 00000 to 00001. The next time it would change to 00002
I don't want to use the autonumber because I don't want breaks in the sequence number if records are deleted (if possible.) When 2007 hits I can change the 06 to 07 in the code and then restart the incremental nummber with zero again.
Any comments or ideas or a better way to get my outcome to be H060001, H0600002 and upward would be appreciated
Thanks in Advance