MS Access 2003 - Auto assigning an Identification Number

Question

So I have a little db that is used for new hires in terms of who, where and what they are issued. One thing that is issued is laptops with little barcode/ID numbers, and I would like the db to auto assign an available ID number when the user is saved to the db.

So I have a form that users use to input the information, and it is unbound, so the info saves via INSERT on button click. The ID number varies, because it is within a specific range.....so lets say its ID#001 - ID#100, and there are gps in that range due to people leaving and handing back in their equip, I want it to use the lowest available number in that range automatically.

does this make sense? how can i get this done?

thanks

I guess i should also mention that I am not really looking for this to be the "ID" of the table (unless its necessary)....i just used ID# as an example....it could be EQUIP#001-EQUIP#500 for all i care.

• EDIT:

So what I meant by ID# is terminology reflecting what I intend to use this for, not access autonumber ID. I need to create an equipID field that uses a range of something like LT#1000-LT#2000. Within this range I want to auto assign lowest number available to each new record create when its created (i just want to use the lowest number incase i have 220 users, but #112 hands his equip back in, and then the next joe to come along to get assign 112.

So I hope i making sense with this.

Answer 1

There are a few points I can think of that might help. If you keep a separate asset table with the laptops in and have a flag or status field that changed from “In Stock” to “Out On Hire” then you could do write a query to like this

SELECT MIN([Asset_tag_number]) FROM tblAssets WHERE Status=”In Stock” AND Type=”Laptop”

Load the result of that query before you save the record and it will give you the first number it comes up with that is in stock and a laptop

Answer 2

Use an Autonumber field, as per this article.