As already noted, have to add a unique ID field to Data table - autonumber should serve. I called it PKID.
This is what I came up with. Build a 'temp' table to save records for audit.
Code:
Sub RandomSeq()
Dim db As DAO.Database, rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset, x As Integer, strP As String
CurrentDb.Execute "DELETE FROM AuditRecs"
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Purpose")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM [AuditRecs]")
Set db = CurrentDb
Do While Not rs.EOF
Set rs1 = db.OpenRecordset("SELECT Data.*, Purpose.Rec_to_Return FROM Data INNER JOIN Purpose ON Data.Purpose = Purpose.Purpose " & _
" WHERE Data.Purpose='" & rs!Purpose & "' ORDER BY Data.Purpose, Rnd([PKID])")
x = 1
Do While x <= rs1!Rec_to_Return And Not rs1.EOF
rs2.AddNew
If DCount("*", "AuditRecs", "Purpose='" & rs1!Purpose & "' AND County='" & rs1!County & "'") = 0 Then
rs2!DataPKID = rs1!PKID
rs2!Purpose = rs1!Purpose
rs2!County = rs1!County
x = x + 1
End If
rs2.Update
rs1.MoveNext
Loop
rs1.Close
rs.MoveNext
Loop
End Sub
Alternatively, add a Yes/No field in Data table to 'flag' record as selected for audit and modify code. If only one user at a time will run this procedure, this would be satisfactory.