473,320 Members | 2,107 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 3538
ADezii
8,834 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

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

Similar topics

5
by: Scott McDaniel | last post by:
I have a VB app which stores information in an Access 2000 db. The VB app handles multiple users (it's a logbook type of application, users share lookup tables but don't share information among...
5
by: Iain Miller | last post by:
Trying to get my head round building a DB to analyse phone bills. On the surface its fairly simple - duration in seconds x cost per minute/60. The problem arises with working out what time of...
19
by: James Fortune | last post by:
I have a lot of respect for David Fenton and Allen Browne, but I don't understand why people who know how to write code to completely replace a front end do not write something that will automate...
11
by: Grasshopper | last post by:
Hi, I am automating Access reports to PDF using PDF Writer 6.0. I've created a DTS package to run the reports and schedule a job to run this DTS package. If I PC Anywhere into the server on...
5
by: stupi | last post by:
I have an access database replicated over a network. Recently and more often, after I fill one record into the database, and then try to replicate it, I get this message "scaling of decimal values...
6
by: Bob Alston | last post by:
I am looking for others who have built systems to scan documents, index them and then make them accessible from an Access database. My environment is a nonprofit with about 20-25 case workers who...
1
by: CapMaster | last post by:
I've found some programs of how to create a standard game of blackjack on C++. But how would you do it using structs? Here is my assignment: Problem Statement: The purpose of this project is to...
0
by: RobAMacAF | last post by:
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...
4
by: lupo666 | last post by:
Hi everybody, this time I have three problems driving me nuts :-((( (1) I have a report with 20 or so Yes/No "squares". Is there a way to either hide/show the "square" or change the yes/no...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.