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
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.
