Use randomize and rnd in the same query to get real random choices (1 Viewer)

ram_son · May 14, 2006

The queries go like this at present :

SELECT TOP 1

.[QuestionText],

.[Answer] AS CorrectAnswer
FROM

GROUP BY

.[QuestionText],

.[Answer], rnd([IDQuestion])
ORDER BY rnd([IDQuestion]);

SELECT TOP 3 Table.Answer AS Correct, qQuestionTextAndAnswer.QuestionText, qQuestionTextAndAnswer.CorrectAnswer
FROM

, qQuestionTextAndAnswer
WHERE (((Table.Answer)<>[qQuestionTextAndAnswer].[CorrectAnswer]))
ORDER BY Rnd([IDQuestion]);

These queries are displayed now in an Access form "frmQuestions" which is applied to "Table" that has three columns id, text , and answer. The result is one question and four suggested answer with one only being correct.The arrangement of the answers is randomized--
but the choice of the question is not realy random....it always starts with the same question as it relies only on rnd.

can I improve the queries and randomize and rnd in the same ...or else can I switch these queries to be used from vb6 code and achieve that result.

any help would be appreciated.

By the way I am really a beginner at this who is seeking help from the experts.

Jon K · May 14, 2006

In my experience, I have found that using Rnd() in queries to select random records generally fails as the same sequence of records is returned each time the database is opened and the query is run.

However, I can successfully select random records by adding an autonumber field in the table and using Rnd() in VBA to build the SQL statement based on the autonumber field.

I have attached an example. You can open the main form and click on the command button to display 3 random records in the subform. The VBA code used is in the ON Click event of the command button.
.

ram_son · May 14, 2006

I tried your sample and it works ok but I have to see if I can adapt the code in vb to vb6 .. What's more impotrtan tis how do I accomplish the two queries one after the other. I wouldn't mind uploading a small sample of my db if you don't mind taking a look at it and helping me out.

Jon K · May 15, 2006

Ok just post your sample and tell us what you want the queries to accomplish. I don't have VB6 on my system. If I can't help, maybe some other members can.

You may also post your question in the Visual Basic forum.
.

Cosmos75 · May 15, 2006

What about using a custom function to generate a random function that incorporates a Randomize() function in the query?

Code:

Public Function fRandomNum() As Double
    Randomize (0)
    fRandomNum = Rnd()
End Function

ram_son · May 15, 2006

Sorry for the delay...It's the difference in time zones.
I attached the file and used only 10 rows with Q's and A's as choices.When you doubleclick frmQuestions you will notice that it always starts with choice 9.

Cosmos75 were would this code go in vb or vb6?

Thanks for your help guys

*Hope I did the attachment thing correctly because it didn't show it in the preview.

ram_son · May 15, 2006

sorry forgot to convert to zip first

RuralGuy · May 15, 2006

Have you tried putting Randomize in the OnLoad or Open event of the form so it will initialize the seed every time you open the form?

ram_son · May 15, 2006

Yes I tried that and it seems like it has no effect ...maybe because the choice is done through an embbeded Access query.Take a look at the attached file.
Any other suggestions?

Thanks

RuralGuy · May 15, 2006

Sorry, no additional suggestions at this time.

The_Doc_Man · May 15, 2006

RANDOMIZE should always randomize your starting point for the random number sequence returned by RND function. HOWEVER, ...

If you have a form that runs the query with the RND statement and the form also runs the RANDOMIZE statement, you have to realize the order of execution for the RND vs. RANDOMIZE functions.

When you open the form, you first open the query in order to access the recordset for that query. Then you perform the OnCurrent process. But there is no time that the form is open that you haven't already run the query. In other words, the suggestion to run Randomize in OnCurrent put the cart before the horse, so to speak.

To fix this, in your OnLoad routine, use the Randomize function anyway - but then immediately do a ReQuery. That will change the order of appearance for the records selected randomly.

However, this is not the way I would have chosen the records in random order, as it is susceptible to repetition.

Remembering that a database will never return anything you didn't tell it in the first place, and assuming that you really, really, REALLY want this to be as truly random as any mathematical generator could ever be,...

Add a field in the table. Using recordset operations behind the scene, update that field with a number chosen randomly. In a second query, scan the table for duplicated "random" fields. Add another random number to the duplicates only. Keep that up until you have no duplicates. Now order by the random fields. Which means that you might have to take the approach of a form that isn't bound to the table so you can "host" the code underneath the form's OnLoad routine. Then perhaps you can open a sub-form that IS bound to the real data. Or perhaps the unbound form can just OPEN a second "main" form that is bound to your table.

ram_son · May 15, 2006

Thanks The _Doc_Man for the enlightening explanation which makes a lot of sense and that's why I suspect I was having all this trouble. But still I am not very good at this and I do not need it to be really, really random. I just do not want it repeating the choices in such an obvious manner.
I remember the requery being in the code at one point but couldn't make it work. I am going to upload the vb6 files as well and maybe that will make it easier for you to take alook at it and help me out ..

Needless to say your help will be very much appreciated.

Jon K · May 16, 2006

I made the following changes in your Access database.

1) Moved qt and ca to a module to make them global and added a public function for use in the first query.

Code:

Public qt As String        ' question text module-level variable
Public ca As String        ' correct answer module-level variable

Public Function getCA() As String
   getCA = ca
End Function

2) Removed Rnd() from the first query.
SELECT

.[QuestionText],

.[Answer] AS CorrectAnswer
FROM

WHERE

.[Answer]=getCA();

3) Added the following code in the LoadQuestion subroutine after Randomize.

Code:

    Randomize
'=============================================
  Dim iCount As Integer
  Dim j As Integer
  
  iCount = DCount("*", "Table")
  j = Int(iCount * Rnd()) + 1
  ca = DLookup("[Answer]", "[Table]", "[IDQuestion]=" & j)
'=============================================

Now after getting rid of Rnd() in the first query, the form can start with a random answer in Access.

DCount() and DLookup() are Access specific. In VB6, you can open recordsets with Select statements instead. If the number of records in the table is fixed, you can hard code iCount with the number.

Hopefully you can adapt the code in VB.
.

ram_son · May 16, 2006

That 's a great improvement ..it seems to do the job of starting with a different choice each time at least on the Access form. Initially I want to use it the way it is setup in the Access db (as is that is).
When I tried it with VB6 I got the error of " undefined function in expression getCA" which I am trying to work out now.
I just wanted to thank you about what you have done so far and let you know that it's basically what I need done. Now back to the error.

ram_son · May 17, 2006

Update on what I was able to achieve with your help Jon K and of the changes to the database:

1: I am able to execute the MS Access form directly from inside VB6 and the randomness is satisfactory for what I need...but, is there a way to see only the form itself without the whole Access window in the background?

2: When I try to switch the code so I generate a VB6 form I get the error "runtime error 3085 : unidefined function in expression : getCA()". I tried to define the getCA() but was unable to get the job done..

Any more useful suggestions.

johnwatkins35 · Jun 22, 2019

BTW i love the database, but i was wondering if there is a way to have up to 6 answers visible to choose from and not to give the give answer when you click next question. Then at the end the grade is shown.

Use randomize and rnd in the same query to get real random choices (1 Viewer)

ram_son

Registered User.

Jon K

Registered User.

Attachments

ram_son

Registered User.

Jon K

Registered User.

Cosmos75

Registered User.

ram_son

Registered User.

ram_son

Registered User.

Attachments

RuralGuy

AWF VIP

ram_son

Registered User.

RuralGuy

AWF VIP

The_Doc_Man

Immoderate Moderator

ram_son

Registered User.

Attachments

Jon K

Registered User.

Attachments

ram_son

Registered User.

ram_son

Registered User.

johnwatkins35

Registered User.

Similar threads

Users who are viewing this thread