Results 1 to 1 of 1
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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:
    <code>
    Sub AssignRandom( _
    strTableName As String, _
    strFieldName As String, _
    lngMin As Long, _
    lngMax As Long)
    </code>
    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
    <code>
    Sub FillRandom()
    AssignRandom "tblCustomers", "intRandom", 1, 900
    End Sub
    </code>
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •