Results 1 to 1 of 1
2008-08-01, 21:53 #1
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Populate field with unique random numbers (All)
In <post:=724,794>post 724,794</post:> in the Excel forum, <!profile=KSTidmore>KSTidmore<!/profile> asked how to fill a field in a make-table query in Access with unique random numbers between 1 and 900.
Since it's a question about Access and the answer is rather long, I've started a new thread here in the Access forum.
It's easy to assign random numbers in a make-table query but not to ensure that these numbers are unique. It's better to leave the field blank in the make-table query (or to assign a fixed value such as 0), and to use VBA code to populate the field after the make-table query has been run.
The attached sample database contains a module with a procedure AssignRandom with the following syntax:
Sub AssignRandom( _
strTableName As String, _
strFieldName As String, _
lngMin As Long, _
lngMax As Long)
strTableName is the name of the table.
strFieldName is the name of the field to be populated.
lngMin and lngMax define the range of numbers.
The sample database contains a table tblCustomers taken from the NorthWind database, to which I added a field intRandom. To populate this field with unique randon numbers between 1 and 900, you can use the procedure
AssignRandom "tblCustomers", "intRandom", 1, 900
I also added a procedure to clear the intRandom field. after testing.
Note: the code requires a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor.