Results 1 to 1 of 1
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 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
    Sub FillRandom()
    AssignRandom "tblCustomers", "intRandom", 1, 900
    End Sub
    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