change access 2000 queries to vbcode

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.

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.

Is that what you meant.

From what I've seen you have to do something like:

VB Code:

Private Sub Form_Load()
    Randomize
End Sub
 
Private Sub Command1_Click()
    Dim sSQL As String, lRND As Long
    lRND = Int(RND * 1000)
    sSQL = "SELECT TOP 1 [Table].[QuestionText], [Table].[Answer] AS CorrectAnswer, " _
         & "Rnd(" & lRND & "*[IDQuestion]) FROM [Table] ORDER BY 3"

Thereby randomly seeding the RND function in the DB each time. I'm sure an expert will be along at some point to tell me I've got it all wrong :D

Try creating one more field.. an expression using the random function. Then in your ORDER BY clause use the alias for this expression.

I don't have data to test this and I don't plan to create a db to test it :D

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 code I suggested was meant, in theory at least, to randomly select an record from the table. which was what i thought was what you wanted.

Nope, I meant creating the additional field in the query and not in the db. What I conceived was to generate a random number as an additional field...

PLease test...

VB Code:

SELECT TOP 1  [b]rnd([IDQuestion]) AS RndNum[/b], [Table].[QuestionText], [Table].[Answer] AS CorrectAnswer
FROM [Table]
GROUP BY [Table].[QuestionText], [Table].[Answer], rnd([IDQuestion])
ORDER BY [b]RndNum[/b];

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.

No way to change the seeding like with the Randomize statement?

Then I guess a field in the DB is an option... before the SELECT query, you'll update the random values in this field for all records.

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...

leinad31...
I am just a beginner at this and got this far through the assistance of good people on forums.

1-Please explain how to add seed in a field

I decided to upload the the vb6 files as well maybe itt will make it easier still for people who want to help.

Somethings wrong with my Access, I can't open your DB.

Just add another column/field to the relevant table and name it [Seed] or something...

Then in VB you'll assign random numbers, say range from 1 to 100, to the Seed field...

VB Code:

Randomize
 
Do Until rs.EOF
   rs.Fields("Seed").Value = Int(100 * Rnd + 1)
   rs.MoveNext
Loop
 
connADO.Execute "SELECT Rnd([Seed]), ...FROM ... ORDER BY Rnd([Seed]) 'etc

If that doesn't work, try using a column/field to hold a random number assigned in VB instead of a seed field

VB Code:

Randomize
 
Do Until rs.EOF
   rs.Fields("RndNum").Value = Rnd
   rs.MoveNext
Loop
 
connADO.Execute "SELECT [RndNum], ...FROM ... ORDER BY [RndNum] 'etc

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.

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.

Thanks.

Quote:

---

Originally Posted by ram_son

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;

rs.Fields("YourNewNUmericField").Value = Rnd
rs.Update

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.