Access vba random function not working

I have a sub that is supposed to take a number of assignments (this number changes daily) and assign them to 7 associates. There are some conditions to this:

If the # of assignment is less than 7, it assigns all of them to a random associate. If the # is divisible by 7, it assigns an equal number to each. If it is not divisible by 7, it assigns equally and then gives the remainder to a random associate.

The problem is the random part. I really don't understand how random works in vba, or at least it seems like it should be super easy, but it's not (maybe). But I have this written and it's not working. (Associates(Int(Rnd() * 7) + 1)). Here is my relevant code:

Earlier in the sub I create an array of the associates and I use some dcounts to get the total assignments for that day:

Dim Associates(6) As Integer
    Associates(0) = 4687 'Anita
    Associates(1) = 4247 'Alberto
    Associates(2) = 2167 'Jeff
    Associates(3) = 4334 'Lisa
    Associates(4) = 4441 'Carrie
    Associates(5) = 2052 'Bobby
    Associates(6) = 4657 'Simona
'
Dim Person As Variant
'
TotalPop = DCount("LNo", "qry_PT_Assign")
FractionPop = Int(TotalPop / 7)
LeftPop = TotalPop - (FractionPop * 7)
'

and then I try to actually assign them.

        'Assign to Associates
        If TotalPop < 7 Then
            DoCmd.RunSQL "UPDATE tbl_Assignments SET AudTellerID = " & (Associates(Int(Rnd() * 7) + 1)) & " WHERE AudTellerID IS NULL"
        ElseIf LeftPop = 0 Then
            For Each Person In Associates
                DoCmd.RunSQL "UPDATE tbl_Assignments SET AudTellerID = " & Person & " WHERE LNo IN (SELECT TOP " & FractionPop & " LNo FROM tbl_Assignments WHERE AudTellerID Is Null)"
            Next
        Else
            For Each Person In Associates
                DoCmd.RunSQL "UPDATE tbl_Assignments SET AudTellerID = " & Person & " WHERE LNo IN (SELECT TOP " & FractionPop & " LNo FROM tbl_Assignments WHERE AudTellerID Is Null)"
            Next
            DoCmd.RunSQL "UPDATE tbl_Assignments SET AudTellerID = " & (Associates(Int(Rnd() * 7) + 1)) & " WHERE AudTellerID IS NULL"
        End If

回答1:

As per my comment, try generating the random number first, assign it to a variable, and then pass the variable into Associates().

Dim rndInt as Integer

rndInt = Int(Rnd() * 7) + 1

Associates(rndInt)

Then as @Chips said, you can use

Debug.print rndInt

Msgbox rndInt

to check its value

That way you'll be able to see what number is actually being generated

回答2:

The trick is to use a negative seed that changes constantly.

So add a time dependant seed to Rnd which changes for every unique id like in this sample select query:

 SELECT 
   Table1.ID, 
   Table1.SomeField, 
   Table1.AnotherField, 
   Rnd(-Timer()*[ID]) AS RandomIndex 
 FROM 
   Table1
 ORDER BY 
   Rnd(-Timer()*[ID]);

In your code, the expression could be something like this:

.. " & (Associates(Int(Rnd(-Timer()*" & [ID] & ") * 7) + 1)) & " ..