troubleshooting Question

Auto Increment Number on form

Avatar of ccowan159
ccowan159Flag for United States of America asked on
Microsoft Access
8 Comments2 Solutions755 ViewsLast Modified:
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

Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros