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!!!!