View Single Post
 
Old October 13th, 2009, 01:02 AM
Old Pedant Old Pedant is offline
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Since it's an Access DB, you can't do it all in the DB, unfortunately. With MySQL or SQL Server (or Oracle or...) you could.

But the answer is trivial:

Create *another* field, say call it PNUM, which is a simple AUTOINCREMENT (aka AUTONUMBER, aka COUNTER) field.

Create you PRODUCTCODE field as a CHAR(4) or VARCHAR(4) or whatever works for you, giving it a default value of NULL (which you don't have to specify...that's what it will be by default).

And every timy you insert a record, PNUM will increase. And *AFTER* each insert, you just then execute a simple SQL query:
Code:
UPDATE products SET productCode = 'P' & Right( '000' & CStr(pnum), 3 ) WHERE productCode IS NULL
Presto.