We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to increase counter in primary key?

Medium Priority
374 Views
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.

TQ.
Comment
Watch Question

Dirk HaestProject manager
CERTIFIED EXPERT
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 ?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2010
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.

Regards,

Patrick

Author

Commented:
Dhaest:
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 manager
CERTIFIED EXPERT
Commented:
Is the prefix always the same ? Why should you use it than ?
CERTIFIED EXPERT
Top Expert 2010
Commented:
peter_chong,

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

Regards,

Patrick
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.