Results 1 to 6 of 6
  1. #1
    amkp711 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    14

    Exclamation Randomly select record from a table, using a recordset

    Hello! I am working on setting up randomization procedures for a clinical trial and I am attempting to reliably build them into access. I currently have a [random allocation list] in the database where each record is marked by a [study_arm] number field, [Clinic] selection, and [ACT score] selection. I created a form titled [frm_Randomizer] which displays the participant's [Clinic] and [ACT score]. On clicking the randomize button on the form, I want access to randomly select a record from the random allocation list where [Clinic] and [ACT score] match those on the form. From there, it needs to fill another field on this form "Randomizer" with the study_arm number (will be 0, 1 or 2).



    Currently, I am running into coding errors and I have gotten turned around as to what the issue is. It repeatedly comes up as "too few parameters", "No Current Record", or SQL issues regardless of what I do to fix the code. I have checked and triple checked all my spellings, and I know that there are records in the [random allocation list] that should come up (Count of about 60 for every possibility). Any ideas what issues are underlying my code? Also, this was adapted from online code snippets I found, so if there is an easier way to do this, I would be very interested...

    Private Sub Command10_Click()
    Dim Randomization_Test As DAO.Database
    Dim Random_Entry As DAO.Recordset
    Dim SpecificRecord As Long, i As Long, NumOfRecords As Long
    Set Randomization_Test = CurrentDb
    Set Random_Entry = CurrentDb.OpenRecordset("SELECT [study_arm], [Clinic], [ACT Score] FROM [random allocation list] WHERE [Clinic] = Me.[Clinic] And [ACT Score] = Me.[ACT Score]")
    Random_Entry.MoveLast
    NumOfRecords = Random_Entry.RecordCount
    SpecificRecord = Int(NumOfRecord * Rnd)
    If SpecifcRecord = NumOfRecord Then
    SpecificRecord = SpecificRecord - 1
    End If
    Random_Entry.MoveFirst
    For i = 1 To SpecificRecord
    Random_Entry.MoveNext
    Next i
    [Forms_frm_Participant Details].Randomizer = Random_Entry(Study_Arm)
    Random_Entry.Close
    Me.Dirty = False
    If Me.Randomizer = "0" Then
    Me.Study_Arm = "1-Usual Care"
    ElseIf Me.Randomizer = "1" Then
    Me.Study_Arm = "2-Clinic-Based Intervention"
    ElseIf Me.Randomizer = "2" Then
    Me.Study_Arm = "3-Home-Based Intervention"
    End If
    Me.Dirty = False
    DoCmd.Close acForm, "frm_Randomizer", acSaveYes
    End Sub



    Please let me know what suggestions you have. I really appreciate all the help I have received on this forum. I am new to access, and you are all life savers!

    Side note, my next step will be to ensure that the record selected each time is not repeated in the future. I was thinking I would just add a "Yes/No" field into the table and have the recordset criteria look for records where that value is False. Then I would set it to mark this field to "true" after the record has been used. Do you think this should work? Again, please let me know if there is an easier way...

    Thank you!!!!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You must use Randomize to get a new seed value so that values are not repeated.
    You may find this link of interest also.

  3. #3
    amkp711 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    14
    Thanks for the reply! But the access database is not itself randomizing participants. It is merely running a recordset to select a random record from a table...

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Did you read this info at http://office.microsoft.com/en-ca/ac...0756538.aspx??

    Here's a routine that could pick a random record from a recordset

    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : randomNumber
    ' Author    : Jack
    ' Created   : 11/18/2010
    ' Purpose   : To Generate Random numbers between and including a range of numbers.
    'Lo and Hi are the lowest and highest random numbers you wish to generate.
    'The Randomize keyword is critical to getting different results for each Access session.
    '---------------------------------------------------------------------------------------
    ' Last Modified:
    '
    ' Inputs: N/A
    ' Dependency: N/A
    '------------------------------------------------------------------------------
    '
    Function randomNumber(Lo As Long, Hi As Long) As Long
    10       On Error GoTo random_Error
    20    Randomize
    30    randomNumber = Int((Hi - Lo + 1) * Rnd - Lo)
    
    40       On Error GoTo 0
    50       Exit Function
    
    random_Error:
    
    60        MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure random of Module AccessMonster"
    End Function
    Sample usage:

    Code:
    Sub test_random()
    'suppose you had a recordset with 2350 records
    ' get 10 random records from the recordset
        Dim i As Integer
        Dim recordCountEnd As Long
        Dim recordCountStart As Long
        recordCountStart = 1
        recordCountEnd = 2350
        For i = 1 To 10
            Debug.Print randomNumber(recordCountStart, recordCountEnd)
        Next i
    End Sub
    Result

    Code:
    865 
     1493 
     1922 
     520 
     1128 
     551 
     2283 
     1163 
     2173 
     2116

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi --

    To select a random record from your table, try this:

    ("SELECT top 1 [study_arm], [Clinic], [ACT Score] FROM [random allocation list] WHERE [Clinic] = Me.[Clinic] And [ACT Score] = Me.[ACT Score] order by rnd(Record_ID)"), where record_id is a positive number in the table - if it is unique, so much the better.

    A good explanation is here:

    http://blogannath.blogspot.ca/2009/1...ks-select.html

    or

    http://www.techrepublic.com/blog/how...rosoft-access/

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I currently have a [random allocation list] in the database where each record is marked by a [study_arm] number field, [Clinic] selection, and [ACT score] selection.
    fill another field on this form "Randomizer" with the study_arm number (will be 0, 1 or 2)
    I am confused.... you say in the first quote that [study_arm] is a number field and in the code you are trying to store a text string.

    Just so we are talking apples to apples, Tables have fields, forms have controls. A form has (can have) a record source. If a field in the record source is dropped onto a form, a text box (usually) is created.
    Unfortunately, Access names the text box the same name as the bound field. I always take the time to rename controls different from the bound field.
    ie, if a field is named "LName", Access will name the control "LName". I change the the control to tbLName (tb = text box).

    Also, I never use spaces in object names.

    I modified your code to make it easier to read (for me).
    The reason you get the error "too few parameters" is that you need to concatenate The values from Me.[Clinic] and Me.[ACT Score] to the SQL string.

    Code:
    Private Sub Command10_Click()
        Dim d As DAO.Database
        Dim r As DAO.Recordset
    
        Dim SpecificRecord As Long, i As Long, NumOfRecords As Long
        Dim sSQL As String
    
        Set d = CurrentDb
    
        sSQL = "SELECT [study_arm], [Clinic], [ACT Score] 
        sSQL = sSQL & " FROM [random allocation list]"
        sSQL = sSQL & " WHERE [Clinic] = " & Me.[Clinic] & " And [ACT Score] = " & Me.[ACT Score]
    
        '    Debug.Print sSQL    '<< for debugging
        Set r = d.OpenRecordset(sSQL)
    
        r.MoveLast
    
        NumOfRecords = r.RecordCount
        SpecificRecord = Int(NumOfRecord * Rnd)
        If SpecifcRecord = NumOfRecord Then
            SpecificRecord = SpecificRecord - 1
        End If
    
        r.MoveFirst
    
        For i = 1 To SpecificRecord
            r.MoveNext
        Next i
    
    ' What is this form??? Why not use Me.Randomizer like the IF statements below??
        [Forms_frm_Participant Details].Randomizer = r(Study_Arm)
    
        r.Close
    
        Me.Dirty = False
    
        'Is Me.Randomizer a number or a string?? A text "0" is not the same as a number 0.
        If Me.Randomizer = "0" Then
            Me.Study_Arm = "1-Usual Care"    <<< Is "Study_Arm" a number or a string????
        ElseIf Me.Randomizer = "1" Then
            Me.Study_Arm = "2-Clinic-Based Intervention"
        ElseIf Me.Randomizer = "2" Then
            Me.Study_Arm = "3-Home-Based Intervention"
        End If
        Me.Dirty = False
    
        '    DoCmd.Close acForm, "frm_Randomizer", acSaveYes   '<< acSaveYes is used to save DESIGN changes, not data. It is not needed here 
        DoCmd.Close acForm, "frm_Randomizer"
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-28-2014, 01:10 AM
  2. Replies: 6
    Last Post: 07-24-2014, 04:01 PM
  3. Replies: 18
    Last Post: 06-01-2013, 02:26 PM
  4. Replies: 3
    Last Post: 02-06-2013, 07:23 PM
  5. Replies: 3
    Last Post: 11-24-2010, 06:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums