469,951 Members | 2,613 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

Using VBA in Access to shuffle a record set then copy it over to another..

Here is my prediciment. I have a database with a form. It in the people can pick a main category, then sub category, then it shows a list of questions from that in a sub form. I am trying to find a way to shuffle the questions in the sub form whenever they rechose a new sub category

[Main Category]
[Sub Category]

[SubForm with questions from sub category]



I was trying to change a shuffle script that I have used before to work here but have spent 3 hours and came up blank..

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo30_AfterUpdate()
  2. Dim MyDB As Database, MyRS As Recordset, intNoOfRecords As Integer
  3. Dim intRandomRecordNumber
  4. Set MyDB = CurrentDb
  5. Set MyRS = MyDB.OpenRecordset("Questions", dbOpenDynaset)
  6. MyRS.MoveLast
  7. intNoOfRecords = MyRS.RecordCount
  8. intRandomRecordNumber = Int(Rnd * intNoOfRecords)
  9. MyRS.AbsolutePosition = intRandomRecordNumber
  10.  
  11. ' Dim rs As Object
  12. 'Set rs = Me.Recordset.Clone
  13. 'myRS.MoveFirst
  14. 'RS.MoveFirst
  15. 'Do Until MyRS.EOF
  16. 'rs.Edit
  17. 'rs.Fields("Question") = MyRS.Fields("Question")
  18. 'rs.Fields("Answer1") = MyRS.Fields("Answer1")
  19. 'rs.Fields("Answer2") = MyRS.Fields("Answer2")
  20. 'rs.Fields("Answer3") = MyRS.Fields("Answer3")
  21. 'rs.Fields("Topic") = MyRS.Fields("Topic")
  22. 'rs.Fields("Section") = MyRS.Fields("Section")
  23. 'rs.Update
  24. 'rs.MoveNext
  25. 'MyRS.MoveNext
  26. 'Loop
  27.  
  28.  
  29.  rs.FindFirst "[Topic] = '" & Me![Combo30] & "'"
  30.  If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The first part is the shuffling of the questions.....the second part I was trying to figure out how to copy the contents of MyRS which is shuffled over RS. The last part just displays what is selected....Maybe I am TOTALLY off here. Any help would be great
May 3 '07 #1
1 3324
ADezii
8,800 Expert 8TB
Here is my prediciment. I have a database with a form. It in the people can pick a main category, then sub category, then it shows a list of questions from that in a sub form. I am trying to find a way to shuffle the questions in the sub form whenever they rechose a new sub category

[Main Category]
[Sub Category]

[SubForm with questions from sub category]



I was trying to change a shuffle script that I have used before to work here but have spent 3 hours and came up blank..

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo30_AfterUpdate()
  2. Dim MyDB As Database, MyRS As Recordset, intNoOfRecords As Integer
  3. Dim intRandomRecordNumber
  4. Set MyDB = CurrentDb
  5. Set MyRS = MyDB.OpenRecordset("Questions", dbOpenDynaset)
  6. MyRS.MoveLast
  7. intNoOfRecords = MyRS.RecordCount
  8. intRandomRecordNumber = Int(Rnd * intNoOfRecords)
  9. MyRS.AbsolutePosition = intRandomRecordNumber
  10.  
  11. ' Dim rs As Object
  12. 'Set rs = Me.Recordset.Clone
  13. 'myRS.MoveFirst
  14. 'RS.MoveFirst
  15. 'Do Until MyRS.EOF
  16. 'rs.Edit
  17. 'rs.Fields("Question") = MyRS.Fields("Question")
  18. 'rs.Fields("Answer1") = MyRS.Fields("Answer1")
  19. 'rs.Fields("Answer2") = MyRS.Fields("Answer2")
  20. 'rs.Fields("Answer3") = MyRS.Fields("Answer3")
  21. 'rs.Fields("Topic") = MyRS.Fields("Topic")
  22. 'rs.Fields("Section") = MyRS.Fields("Section")
  23. 'rs.Update
  24. 'rs.MoveNext
  25. 'MyRS.MoveNext
  26. 'Loop
  27.  
  28.  
  29.  rs.FindFirst "[Topic] = '" & Me![Combo30] & "'"
  30.  If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The first part is the shuffling of the questions.....the second part I was trying to figure out how to copy the contents of MyRS which is shuffled over RS. The last part just displays what is selected....Maybe I am TOTALLY off here. Any help would be great
Here is a Routine that will shuffle all Questions in a Table named tblQuestions. I'm not saying that it is the most efficient method available, but I created it and have used it extensively without a single problem. It works by generating a series of Unique Random Numbers from 1 to the Number of Questions in tblQuestions. It then loops through tblQuestions in order, and assigns these Random Numbers to a [RandomID] Field in tblQuestions. You can set any criteria you like on tblQuestions, the Randomness (is that a word?) of Questions is exposed by sorting on the [RandomID] Field. The code is listed below. Any questions feel free to ask. Before you do anything, create a Field in tblQuestions named RandomID (INTEGER).
Expand|Select|Wrap|Line Numbers
  1. Dim intNumberOfTestQuestions As Integer
  2. intNumberOfTestQuestions = DCount("*", "tblQuestions")
  3.  
  4. Dim MyRandom As Integer, Counter As Integer, T As Integer
  5. ReDim RandomNumbers(1 To intNumberOfTestQuestions) As Integer
  6.  
  7. Randomize   'Seed the Random Number Generator
  8.  
  9. 'Fill the Array with Integers ranging from 1 to intNumberOfTestQuestions
  10. For T = 1 To intNumberOfTestQuestions
  11.   MyRandom = Int(Rnd() * intNumberOfTestQuestions + 1)
  12.     RandomNumbers(T) = MyRandom
  13. Next T
  14.  
  15. 'Lets eliminate the Duplicates and keep on going until all 
  16. 'elements in the Array are unique
  17. DoItAllOverAgain:
  18. For Counter = 1 To UBound(RandomNumbers)
  19.   For T = 1 To UBound(RandomNumbers)
  20.     If Counter <> T Then
  21.       If RandomNumbers(Counter) = RandomNumbers(T) Then
  22.         RandomNumbers(Counter) = Int(Rnd() * intNumberOfTestQuestions + 1)
  23.         GoTo DoItAllOverAgain
  24.       Else
  25.       End If
  26.     End If
  27.   Next T
  28. Next Counter
  29.  
  30. '----------------------------------------------------------------
  31. 'Get ready to shuffle the Questions via creating new RandomIDs
  32. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  33. Dim intRSCounter As Integer
  34.  
  35. Set MyDB = CurrentDb()
  36. Set MyRS = MyDB.OpenRecordset("tblQuestions", dbOpenDynaset)
  37. MyRS.MoveFirst
  38.  
  39. Do While Not MyRS.EOF
  40.  intRSCounter = intRSCounter + 1
  41.   MyRS.Edit
  42.     MyRS![RandomID] = RandomNumbers(intRSCounter)
  43.   MyRS.Update
  44.     MyRS.MoveNext
  45. Loop
  46.  
  47. MyRS.Close
  48.  
  49. 'Whatever your RecordSource for the Sub-Form is just add a
  50. 'sort on the [RandomID] Field
May 4 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Scott McDaniel | last post: by
5 posts views Thread by Iain Miller | last post: by
5 posts views Thread by stupi | last post: by
1 post views Thread by CapMaster | last post: by
4 posts views Thread by lupo666 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.