• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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.

TQ.
0
peter_chong
Asked:
peter_chong
  • 2
  • 2
4 Solutions
 
DhaestCommented:
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 ?
0
 
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.

Regards,

Patrick
0
 
peter_chongAuthor 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.

 
0
 
DhaestCommented:
Is the prefix always the same ? Why should you use it than ?
0
 
Patrick MatthewsCommented:
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now