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?
Patrick MatthewsConnect With a Mentor Commented:
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.


DhaestConnect With a Mentor Commented:
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 ?
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.

DhaestConnect With a Mentor Commented:
Is the prefix always the same ? Why should you use it than ?
Patrick MatthewsConnect With a Mentor Commented:

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


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.