How to increase counter in primary key?

Posted on 2009-02-12
Last Modified: 2013-12-25
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.

Question by:peter_chong
    LVL 53

    Assisted Solution

    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 ?
    LVL 92

    Accepted Solution

    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.



    Author Comment

    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.

    LVL 53

    Assisted Solution

    Is the prefix always the same ? Why should you use it than ?
    LVL 92

    Assisted Solution

    by:Patrick Matthews

    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



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now