ACC: How to Fill a Table with Random Records from Another Table

      Sub BuildRandomTable(lngRequest as Long)

         Dim dbsRandom As Database
         Dim rstOrders As Recordset
         Dim rstRandom As Recordset
         Dim UpperLimit As Long
         Dim LowerLimit As Long
         Dim lngCounter As Long
         Dim lngGuess As Long
         Dim lngRecordCount As Long

         ' Assumes that this module is in the Northwind database.
         Set dbsRandom = CurrentDb
         ' Delete any existing records from tblRandom table.
         dbsRandom.Execute "Delete * from tblRandom;"
         ' Open Orders as a Table Type recordset.
         Set rstOrders = dbsRandom.OpenRecordset("Orders", dbOpenTable)
         rstOrders.MoveFirst
         LowerLimit = rstOrders!OrderID
         rstOrders.MoveLast
         UpperLimit = rstOrders!OrderID
         lngRecordCount = rstOrders.RecordCount

         Set rstRandom = dbsRandom.OpenRecordset("tblRandom", _
                         dbOpenDynaset)
         lngCounter = 1

         ' Check to make sure the number of
         ' records requested is reasonable.
         If lngRequest > lngRecordCount Then
            MsgBox "Request is greater than the total number of records."
            Exit Sub
         Else
            lngRequest = lngRequest + 1
         End If

         Randomize
         Do Until lngCounter = lngRequest
            ' Generate a random number
            lngGuess = Int((UpperLimit - LowerLimit + 1) _
               * Rnd + LowerLimit)
         ' Ensure that it exists in the Orders table.
         rstOrders.Index = "PrimaryKey"
         rstOrders.Seek "=", lngGuess
         If rstOrders.NoMatch Then
            ' Drop through and generate a new number.
         Else
            ' Check to see if it's already been used in the new table.
            rstRandom.FindFirst "lngOrderNumber =" & lngGuess
            ' If not, add it to the new table.
            If rstRandom.NoMatch Then
                With rstRandom
                    .AddNew
                    !lngGuessNumber = lngCounter
                    !lngOrderNumber = lngGuess
                    .Update
                End With
                lngCounter = lngCounter + 1
            End If
          End If
          Loop
          ' Clean up.
          dbsRandom.Close
      End Sub