Anyway I can get the results I get from these embedded Acces 2000 queries by using vbcode.
The queries goes like this at present :
SELECT TOP 1 [Table].[QuestionText], [Table].[Answer] AS CorrectAnswer
FROM [Table]
GROUP BY [Table].[QuestionText], [Table].[Answer], rnd([IDQuestion])
ORDER BY rnd([IDQuestion]);
SELECT TOP 3 Table.Answer AS Correct, qQuestionTextAndAnswer.QuestionText, qQuestionTextAndAnswer.CorrectAnswer
FROM [Table], 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 question choice is not realy random....Always starts with the same question as it relies only on rnd.
I hope someone has suggestions for this.
Any assistance will be appreciated.
Last edited by ram_son; May 14th, 2006 at 11:08 AM.
Reason: maybe it's easier to achieve the same result through vb code instead of access query
Instead of randomizing the SQL result... why not just randomize them when they are displayed (transfer from recordset to display control)? Unless your using a data bound control for the display.
I am a beginner at this and what I did is take the code from the Form in MS Acces and somehow, by uncommenting some of the code made it display in vb6 using labels and textboxes (no ADODC or such). Then I tried to insert Randomize in form_load but didn't seem to make any difference.
I tried that it but didn't seem to do the trick.Does this code achieve the results of the two queries at once? The original table only has the the three columns ID,Text, and Answer.
My last reply was to bushmobile suggested code.I was wondering if I needed more steps to join the results of the queries.In other words one query chooses a question and its correct answer and the other query generates three wrong answers. Then these results would be scrambled and displayed in labels. Would the code you suggested do all that? I didn't get any response from clicking the command1 button.
leinad31.. are suggesting to put an additional field in the MS Access table? I really don't know how to go about doing that but I'll be more than willing to create a sample db and uploaded if you have the patience for me.
I'v been trying very hard reading up on the topic and asking in forums with no luck..
I hope you guys could would bear with me a little longer..
Thanks
the problem with your code leinad31 is that everytime the query is run the outcome will be the same because your seeding the random number generator by the same thing each time. Hence the reason I created a random number in VB and multiplied that by the ID.
Leinad31 I tried the vbcode and kept getting the error two few parameters for some reason.
bushmobile I actually wanted that plus I wanted three other wrong answers chosen and the all displayed on the same form.
what more has to be included in the code.
I am attaching a sample of the db file I am working with which has th e embedded MS Access queries. Maybe it will make things cleare and easier to deal with.
Yap, the fieldname in the Order By clause causes the request for a parameter cause that fieldname is not in the original table's fieldlist.
If there's still no alternate solution then just create an additional field in your table for the seed (I previously suiggested storing the random number, we'll store a seed value instead).
Open an rs and loop through it, updating the Seed field from VB (which has the randomize statement).
Since all records now have new seed values ...
SELECT Rnd(SeedField), Table1.* FROM Table1 ...etc
What I'm not sure at the moment is if ORDER BY Rnd(SeedField) would work as expected or if it will request a parameter...
Last edited by leinad31; May 15th, 2006 at 01:15 PM.
No I couldn't get anywhere with these two attempts maybe because the randomizing has already taken place in the embedded queries. I got errors like item not found in this collection and the with block thing. I wish and hope you can get the file open in your MS Access.
The folowing is a quote from a member of a forum :"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."
Do I do the requery as such Set ReQuery... or not ?
When I do that I get an error object not found..
Could you tell me how to do a requery soon after thre randomize?
The file will open in Access 2003. I didn't open it because it contains VBA code and macros, and I don't open someone else's macros. But there's nothing wrong with the table, which is all you need for VB, or the queries. Just run the queries from VB.
The most difficult part of developing a program is understanding the problem.
The second most difficult part is deciding how you're going to solve the problem.
Actually writing the program (translating your solution into some computer language) is the easiest part.
Please indent your code and use [HIGHLIGHT="VB"] [/HIGHLIGHT] tags around it to make it easier to read. Please Help Us To Save Ana
You mean execute the form frmQuestions from VB6. What's the syntax to use because I am having problem adapting the code between the two..could you help out with that.
No I couldn't get anywhere with these two attempts maybe because the randomizing has already taken place in the embedded queries. I got errors like item not found in this collection and the with block thing. I wish and hope you can get the file open in your MS Access.
The folowing is a quote from a member of a forum :"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."
Do I do the requery as such Set ReQuery... or not ?
When I do that I get an error object not found..
Could you tell me how to do a requery soon after thre randomize?
If his suggestion works then good for you...
Which part of my suggestion were you not able to do? Think of it as saving a Numeric value on its own field/column, make the field a single/double or float data type. Open a recordset and loop through the records, updating just one column/field (your number field for storing random numbers). The numbers (random) you will generate in VB (and not in the DB) like in my second sample;
When your finished looping through the records, reopen the recordset with your SELECT query which includes you Number field. Set the clause for ORDER BY MyNewNumberField.
Your just adding one field and updating it with random numeric values.
I'll try that -- but would the looping be costly if the records involved are over 1000 in number? I'll see how I can manage and let you know . Appreciate the help.
Yes it can be costly if the table is large (many fields and many records)... you can maintain it as a separate table to minimize the number of fields to two (ID, RandomVal) then use INNER JOIN to relate the ordering table with the questions table.
As to filling up this ordering table, you can INSERT the IDs from the questions table then iterate the recordset to fill up the RandomVal column... I think its possible to fill up this table from a text file treated by INSERT as an external database but I have no experience with using sql that way.
The stuff is a little beyond my query competence. Could you maybe put together a query that would do the procedure at once with maybe INNER JOIN based on three fields IDQuestion,QuestionText and Answer to produce two results 1: choosing a questionText as question and answer as the correct answer.. Then get three other wrong answers and randomize them with the correct answer.
That would be of great help.
Reply With Quote
Originally Posted by ram_son
