Access 2016启动时随机函数首次总是选中第7条记录的原因

问题原因 #

你的代码首次启动总是选中固定记录，核心问题在于SQL中Rnd(StarterID)的用法和VBARandomize的作用范围不匹配：

• Access的Rnd(固定值)会生成基于该固定值的恒定随机数，每个StarterID对应的随机值是固定的。
• VBA中的Randomize仅影响VBA环境内的随机数生成，对SQL查询中的Rnd()函数完全不起作用。
• 首次启动应用时，Access内置的随机数生成器初始种子固定，导致SQL排序结果完全固定，因此总是选中同一条记录（即你看到的第7条）；后续操作中，随机数种子因其他操作被改变，随机功能才恢复正常。

解决方案 #

方案1：修改SQL语句，使用动态随机种子 #

将SQL中的Rnd(StarterID)替换为Rnd(-Timer() * StarterID)，结合当前时间生成动态种子，确保每次查询的排序结果都随机：

Private Sub Form_Load()
   Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM (SELECT *,Rnd(-Timer() * StarterID) As RandomValue FROM starter where asked = False ORDER BY Rnd(-Timer() * StarterID))")
    
    GlobalVars.starterid = rs!starterid
    ques = rs!starterquestion
    Answer1 = rs!Ans1
    Answer2 = rs!Ans2
    Answer3 = rs!Ans3
    Answer4 = rs!Ans4
    GlobalVars.correctAnswer = rs!correctAnswer
    
    Me.question.Value = ques
    Me.Ans1.Caption = Answer1
    Me.Ans2.Caption = Answer2
    Me.Ans3.Caption = Answer3
    Me.Ans4.Caption = Answer4
    
    Set rs = Nothing
End Sub

方案2：在VBA中直接定位随机记录（更可控） #

先获取符合条件的记录总数，生成随机行号后直接定位，避免SQL排序的不确定性：

Private Sub Form_Load()
    Dim rs As Recordset
    Dim recordCount As Long
    Dim randomIndex As Long
    
    Randomize
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM starter where asked = False")
    
    ' 处理无符合条件记录的情况
    rs.MoveLast
    recordCount = rs.RecordCount
    If recordCount = 0 Then
        Set rs = Nothing
        Exit Sub
    End If
    
    ' 生成1到记录总数之间的随机行号
    randomIndex = Int((recordCount * Rnd) + 1)
    
    ' 定位到随机记录
    rs.MoveFirst
    rs.Move randomIndex - 1
    
    ' 赋值操作和原代码一致
    GlobalVars.starterid = rs!starterid
    ques = rs!starterquestion
    Answer1 = rs!Ans1
    Answer2 = rs!Ans2
    Answer3 = rs!Ans3
    Answer4 = rs!Ans4
    GlobalVars.correctAnswer = rs!correctAnswer
    
    Me.question.Value = ques
    Me.Ans1.Caption = Answer1
    Me.Ans2.Caption = Answer2
    Me.Ans3.Caption = Answer3
    Me.Ans4.Caption = Answer4
    
    Set rs = Nothing
End Sub

内容的提问来源于stack exchange，提问作者Fatima Habib