create database that generates random non-repeating numeric id

  • Thread starter Thread starter MackBlale
  • Start date Start date
M

MackBlale

I am trying to create a database that creates a member id for each entry
automatically. However, I need the number to be random integer between
1-1000 and non-repeating. I don't want sequential member numbers. Thanks
 
I am trying to create a database that creates a member id for each entry
automatically. However, I need the number to be random integer between
1-1000 and non-repeating. I don't want sequential member numbers. Thanks
Click to expand...

This will be a bit complicated but doable. Create a thousand-row two-field
table NewIDs with one Long Integer field NewID and a Double, Sortkey. I'd
suggest creating it in Excel; in the first column use Insert... Series to fill
in values 1 to 1000, and in the second column use Rand() to fill in a random
double. Import or copy and paste this into Access.

Create a Query qryNewID

SELECT TOP 1 NewID
FROM NewIDs LEFT JOIN Members
ON NewIDs.NewID = Members.MemberID
WHERE Members.MemberID IS NULL
ORDER BY Sortkey;

Then use code in your form's BeforeInsert event like:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!MemberID = DLookUp("NewID", "qryNewID")
End Sub

to pick the next ID. Sorting by a Rand() value will shuffle the data into
random order and the left join will exclude already used ID's.
 
This seems like I am creating a pool of numbers for Access to draw from when
I want the program to create the numbers based upon upper and lower limits
and then check to make sure the number has not been used already. I thought
I might be able to use the RND instruction in VBA. You have answered the
question but I'm looking for a simpler solution. Ideas anyone?
 
Back
Top