Assign Unique ID number for each new record

[Guest]

I have a database that I use to track hardware assets, and each entry has a unique 4 digit ID number assigned to it that matches an asset tracking label on the equipment. The problem is the numbers were assigned before I constructed my database so they are all over the place

I need my form to automatically generate a unique number between 1000-9999 when I create a new record. I don't care what number it assigns new entries, just that it is different than previously assigned numbers. Even if it started at the highest used number and then was sequential from there I could live with tha

I am a total novice when it comes to scripts and expressions so please go easy on m

 

[John Vinson]

I have a database that I use to track hardware assets, and each entry has a unique 4 digit ID number assigned to it that matches an asset tracking label on the equipment. The problem is the numbers were assigned before I constructed my database so they are all over the place.

I need my form to automatically generate a unique number between 1000-9999 when I create a new record. I don't care what number it assigns new entries, just that it is different than previously assigned numbers. Even if it started at the highest used number and then was sequential from there I could live with that

I am a total novice when it comes to scripts and expressions so please go easy on me

I'd suggest assigning them sequentially (it's easier than randomly).

Create a table named NUM with one field N; fill it with all the
numbers from 1000 to 9999 (you can do this very easily by filling-down
in an Excel spreadsheet and copying and pasting, or importing it).

Now create a Query joining NUM to your asset table. Select the join
line and make it a LEFT OUTER join - "show all records in NUM and
matching records in ASSETS". Select the ID from the Assets table and
use a criterion of IS NULL.

This query will now show only the *unused* numbers.

In your form's BeforeInsert event, just DLookUp a value from this
query and use it as the ID. Next time you insert something it will
have been removed from the query.