How to increase counter in primary key?

I have a table called inventory table where the inv_id is the primary key.

I would like to increase the counter when a new inventory has been
key-in to the user form.
Initially, the form will begin with counter=1. The counter will increased
gradually when the inventories have been entered.
If the user cancel to add any record at the initial stage or later,
the counter can trace back to previous. E.g 1->0 or 5->4

Please help me to know about how to implement counter which is very
common in every application developments.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk HaestProject managerCommented:
Why don't you let the database generate the next sequence number ?
This number wil only be increased if you really insert a record in the database !
Which database are you using ?
Patrick MatthewsCommented:
Hello peter_chong,

As Dhaest indicates, the easiest way to do this is to let the db handle it.  For example, in Access you would
set up the ID column as AutoNumber, and in SQL Server you would set it up as an identity column.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peter_chongAuthor Commented:
In my situation, I want to insert an id which has prefix, e.g: E00001 ... E00100
How to do this in Visual Basic?
I am using Access XP as database.
Later, I will planned to use mySQL as a database.

Dirk HaestProject managerCommented:
Is the prefix always the same ? Why should you use it than ?
Patrick MatthewsCommented:

Again, I agree with Dhaest.  If that prefix is always the same, then simply use an autonumber ID, and then
in your queries and reports apply the formatting:

SELECT "E" & Format(ID, "00000") AS IDWithPrefix, Field1, Field2, Field3
FROM SomeTable


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.