Hey guys,

I know everyone hates newbs,  but i could do with a spot of help. Ive looked through the faq's and other discussions but cant find exactly what im looking for. Here goes;

I am creating a quiz using vb, i have all my questions, 20 in total, but i would like ten of them to be randomly generated each time a user selects to play.

It would be ideal to generate these questions into one form, one after the other, and store the score which they recieve in order to rate them at the end.

Is it just a case of storing these questions in 'access' somehow for example and calling them when needed, or having many items on top of eachother on the form and making them visible and invisible where appropriate (this suggests insanity) or maybe loading a text file!?

Help and suggestions would be much appreciated. Cheers

Ok I am no expert but i hope this would help,

first if you need to store the scores into a database then you may need to create 2 tables on an access database name each player and question respectively, in the table player you may need to create necessary fields such as username password and scores

now how to access them in VB Goran thought me the easiest way so now i am sharing it to you, right click on the project exporer then choose add form, on the add form dialogue choose vb form wizard, choose the best that suites your need but i think for the account creation of each player it is best that you create a form with the textboxes for each datafield needed then on the create button you may put this codes

Dim cn As ADODB.Connection Dim myRecordSet As ADODB.Recordset Dim strsql As String          Set cn = New ADODB.Connection cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False             cn.Open                                                                 strsql = "INSERT INTO players(field1,field2,field3,field4,field5) VALUES('" & Text1 & "', '" & text2 & "', '" & Text3 & "','" & Text4 & "','" & Text5 & "');"                Set myRecordSet = New ADODB.Recordset             myRecordSet.Open strsql, cn                      Set myRecordSet = Nothing

for the questions just create another form for the question table each textbox for each fields but you might want to make the textbox for the answer invisible

for example you have 20 questions then generate a number from 1 to 20 make sure you have a fields for the question table which is from 1 to 20 as well

Dim x As Integer x = 0 x = Int((20 - 1 + 1) * Rnd) + 1

the above code will generate a number from 1 to 20 but several times may result to a same number

then in order to view the question then

use this

Dim cn As ADODB.Connection Dim myRecordSet As ADODB.Recordset Dim strsql As String          Set cn = New ADODB.Connection cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False             cn.Open    strsql = "select * from question where questionindex = '" & X & '"" Set myRecordSet = New ADODB.Recordset             myRecordSet.Open strsql, cn                      Set myRecordSet = Nothing text1 = myrecordset!question text2 = myrecordset!answer

i hope somehow these gave you an idea, if not im sure Goran and Rodgers so as the others will help.

Thanks for the post jahsen.
It has given me an idea now what to do.

I do have a couple of extra questions now tho;

cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data
The above line is getting error messages about not having an end of line statement, i think its having trouble with the 4.0. any ideas?

Source=C:\folder\access.mdb;Persist Security Info=False
The line above is also causing an error - no line number?
Also im guessing the source is where access is held, or is it where my database is located?

Thanks Jon

What kind of questions are you going to manage? Long-text questions? Single correct answer or multiple correct anwer? Close answer or Open answer? Some answer proposal to choose among, or no proposal?

I got some suggestions to give, but the most important is that all depends on how you organize the db. You should include for each record (a single question): a question text, a correct answer (or some words to compare with), some optional answer proposals. Then some optional utility fields, such as some keywords to make searches, or a question rating field.

I would execute a query to retrieve only a certain number of records, instead of keeping the whole db in memory. So you work with a restricted dataset, that's faster to manage.
In the query you retrieve all the fields you need: text, correct answer, optional proposals, but you show to user only the text and the proposals, and keep the correct answer to compare the user selection. In the same query you can retrieve also the questions rating.

You can filter the number of records by the sql TOP intruction, within the query itself. I created a small single-table db in Access97. In it, the job is done all by the sql instruction. It's done in Access, but it can be improved if you use VB, so I put no forms in it.
First, I included the Rnd() MSAccess' function into the sql statement, but it doesn't work properly as the returned values are not exactly random. In VB we can initialize the system randomizer by calling the Randomize instruction before using the Rnd() function, but Randomize is not avaliable in Access.
More, as in VB you can build an sql statement like a string, you can randomize and create an array of IDs, and then build a query to retrieve only the TOP 10 records with those IDs.
Anyway, you can have a look on my small db. It's designed to manage not-that-long questions (max 255 chars; if you need more, you shoud use a Memo type field, but in this case the field will not be indexed so you'd create another keyword field - or some other trick - for searches), with five answer proposal to choose among, and a single correct answer allowed.
Sample:
What is the largest continent on the Earth?
1) Africa
2) Europe
3) North America
4) Asia
5) South America
(user should choose the answer 4)
You should design the way the user makes his choice and how you can compare it with the correct answer you take by the query.
As I said at first, if you need to manage questions in other ways, you have to modify the db implementation.

Hope it helps.

PS. The correct syntax is as following:

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False"

It is one single line, if you need to trim it in two lines, you should use the underscore char ("_").
Data Source is the path and name of the .mdb file.
P.P.S. not everyone hates newbies, at least not here.

____________________________
Real Programmer can count up to 1024 on his fingers

____________________________
Attached:
db6.zip 10 KB (Downloads: 19)

yronium,

Thanks for the informed reply.

I am planning to have a short question followed by, similar to your example, three possible multiple choice answers.

I am having trouble realising the need for a query once the data has been loaded into the vb form. If the questions are loaded into text boxes and the possible answers into radio boxs, will it just not be a case of having a large 'if' statement to asses if each checked radio box corresponds to the invisible answer, and if it does increment a global variable score?!

Or is that an impossible way of doing it?

Thanks again.

No large If statement: an array of radio buttons and a Select Case. And only if you need to evaluate every given answer, I mean "...he said "a": three points, he said "b": correct, six points, he said "c": one point...". If you only need to check if the given answer is correct or not, you just need an If block ("...If answer = "b" Then...")
This method is intended to show user one record (question) at a time: he read the question, make a choice, submit, then move to the next question. So you should create a recordset variable to store the questions, and link the textboxes to the fields of the recordset. You got to create also a global variable to keep the score, and increase it when moving to the next record.

Say you have four textboxes, named txtQuestion, txtOption1, txtOption2 and txtOption3: you can link them to the fields of the query. Say you have three radio buttons aside the txtOption1, txtOption2 and txtOption3 textboxes, and group the radio buttons in an array, named optGivenAnswer(); so you have optGivenAnswer(0), optGivenAnswer(1) and optGivenAnswer(2). Finally, say you put a command button named btnSubmit.
Then you can obtain the given answer by a Select Case block, as following:

Option Explicit Dim ChosenAnswer As Byte Private Sub btnSubmit_Click()     Select Case ChosenAnswer         Case 0            ' do the appropriate things         Case 1            ' do the appropriate things         Case 2            ' do the appropriate things     End Select End Sub Private Sub optGivenAnswer_Click(Index As Integer) ' (one single Click event occurs during loading phase too)     ChosenAnswer = Index End Sub

If otherwise you need only to compare the given answer with the correct, retrieved by the query but hidden to the user, you can use a simpler approach

Dim correct As Byte correct = ... ' (set the value from the query, here) If optGivenAnswer(correct).Value = True Then     ' do the appropriate things

If you prefer showing user all the questions once together, you might consider using a grid, and a grid column of cells/some textboxes to input his choices instead than radio buttons. But even in this case, the best solution is not a large If statement, but better looping by a For cycle, say "...For Each record In recordset, If CellInput.Value = recordset.Fields("CorrectAnswer").Value Then ....increase the score...")

Hope it helps.

____________________________
Real Programmer can count up to 1024 on his fingers

I wonder if you can take a look at my code to load some data from my database and see why it is not working - thanks:

Private sub form1levelselector.load ()

Dim cn As ADODB.Connection
Dim myRecordSet As ADODB.Recordset
Dim strsql As String
        
Set cn = New ADODB.Connection
cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\Visual Work\db1.mdb;Persist Security Info=False
            cn.Open
  

strsql = "select * from qRandomQuestions1 where Questions.QuestionRating = '" & 1 & '""

Set myRecordSet = New ADODB.Recordset
            myRecordSet.Open strsql, cn
        
            Set myRecordSet = Nothing

text1 = myrecordset!Questions.Text

End sub

Where qRandomQuestion1 is my query, Questions.QuestionRating is a field in the questions table and Questions.Text is the questionfield from the questions table which i wish to load.
I have a text box on the form where i thought the question from my database would appear. Any ideas please?

N.B. for yronium - I now have the Qyestions.QuestionRating as a way of making sure i load a question from the level of difficulty the user wants. Thanks

I am using similar code for loading data from a vb form into an access database as I am for retrieving data as shown in my coding above.

I have narrowed it down to the problem lying somewhere in the first line of code, the 'Dim cn As ADODB.Conection' declaration.

When the code is processed an error occurs saying 'User-Defined type not defined' appears for both sending and retreiving data.

I desperately need this bloody database to interract with my vb forms and I cannot see why it wont work - please anybody help!  

quote:

---

jonwiper wrote:
            myRecordSet.Open strsql, cn
        
            Set myRecordSet = Nothing

text1 = myrecordset!Questions.Text

---

quote:

---

Where qRandomQuestion1 is my query, Questions.QuestionRating is a field in the questions table and Questions.Text is the questionfield from the questions table which i wish to load.

---

quote:

---

N.B. for yronium - I now have the Qyestions.QuestionRating as a way of making sure i load a question from the level of difficulty the user wants. Thanks

---

quote:

---

jonwiper wrote:
When the code is processed an error occurs saying 'User-Defined type not defined' appears for both sending and retreiving data

---

Thanks for the new piece of coding yronium, i'll try it shortly.

With regards to the error i'm getting, it is the first line of code thats highlighted, I also am quite sure it isn't the project/reference im missing. I have Microsoft DAO 3.6 object library selected. That's right, yes?

Thanks once more. Jon

I have sorted out the problems I had before, i think it must have been one of the references conflicting. It is no longer syntax errors but runtime.

Dim cn As ADODB.Connection Dim myRecordSet As ADODB.Recordset Dim strsql As String          Set cn = New ADODB.Connection cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False             cn.Open                                                                 strsql = "INSERT INTO Players(First Name, Last Name) VALUES('" & Text1.text & "', '" & text2.text & "');"                Set myRecordSet = New ADODB.Recordset             myRecordSet.Open strsql, cn                      Set myRecordSet = Nothing

With the code above; I am getting the response 'Syntax error in INSERT INTO STATEMENT' when i click debug it highlights the line of code: myRecordSet.Open strsql, cn

This is very interesting as later on in my project on a different form when trying to read from the same database but a different table. The code from the question part; I get the error message 'Data type mismatch in criteria expression' when I debug this error it points me again to the same line of code that it does above.

This is really frustrating me as all that is wrong with my project is the database and the vb forms not interacting properly. It must be something simple surely that as a novice im just missing!? Please help, if your not bored by now! Much appreciated. Jon

quote:

---

jonwiper wrote:
I have Microsoft DAO 3.6 object library selected. That's right, yes?

---

quote:

---

cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False

---

quote:

---

            strsql = "INSERT INTO Players(First Name, Last Name) VALUES('" & Text1.text & "', '" & text2.text & "');"
  
            Set myRecordSet = New ADODB.Recordset
            myRecordSet.Open strsql, cn
[...]

With the code above; I am getting the response 'Syntax error in INSERT INTO STATEMENT' when i click debug it highlights the line of code: myRecordSet.Open strsql, cn
[...]
The code from the question part; I get the error message 'Data type mismatch in criteria expression' when I debug this error it points me again to the same line of code that it does above.

---

Use [] arround fields that consist of two or more words. THe output of the SQL statement shoud look like this:

INSERT INTO Players ([First Name], [Last Name]) VALUES .....

____________________________
If you find the answer helpful, please mark this topic as solved.

quote:

---

Goran wrote:
Use [] arround fields that consist of two or more words. THe output of the SQL statement shoud look like this:

INSERT INTO Players ([First Name], [Last Name]) VALUES .....

---

I still cant get the blasted thing to work, even with gorans advice. The First Name and Surname are my field names in the table, so it should relate to them right?!

I've eventried turning it on its head and using the method below but even that doesn't work! ARGH!

cn.Provider = "Microsoft.Jet.OLEDB.4.0;" cn. ConnectionString = “C:\My Documents\Visual Work\db1.mdb” cn.Open Set myRecordSet = New ADODB.Recordset myRecordSet.Open “SELECT First_Name, Last_Name FROM Players” , db1, adOpenDynamic, adLockOptimistic myRecordSet.AddNew myRecordSet!First_Name = Text1.Text myRecordSet!Last_Name = Text2.Text myRecordSet.Update myRecordSet.Close Set myRecordSet = Nothing Set cn = Nothing ‘Clear the text boxes after adding Text1.Text = “” Text2.Text = “”

At last!!!!  

I have been able to write to my table in access with the following code:

Dim cn As ADODB.Connection Dim strsql As String Set cn = New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\Visual Work\db1.mdb;Persist Security Info=False" cn.Open strsql = "INSERT INTO Players([First_Name], [Last_Name]) VALUES('" & Text1.text & "', '" & Text2.text & "')" cn.Execute strsql

It didnt like whitespace between the 'First' and 'Name' so changing it in both the table and the statement worked, plus the [] courtesy of goran.

Thanks guys, im so happy it now works.  

I may still be back to ask about reading from the database now the writing is done. haha. Cheers

jonwiper, you are mixing some things here.... I will give you two examples how things work with field names.

Situation 1:

Table: Players
1st field: First_Name
2nd field: Last_Name

SQL SELECT statement

SELECT First_Name, Last_Name FROM Players

Situation 2:

Table: Players
1st field: First Name
2nd field: Last Name

SQL SELECT statement

SELECT [First Name], [Last Name] FROM Players

So, as it can be seen, if you replaced blank space with _ character in field name, there is no need for [] brackets. [] brackets are only needed if field name has empty space in it.

____________________________
If you find the answer helpful, please mark this topic as solved.

Back again guys,

Yronium gave me some ideas about how to check my answers in this quiz im making - its turned into a personal challenge now ive started! ;)

I wondered if you could go into some more detail about it for me. I know you've help me alot but I would still appraciate it immensely. The code he suggested is in an above post.

As he suggested I have my question text box and three answer boxes. I need to compare whatever answer the user choses via an array of check boxes, to the correct answer.

But what im not sure about is how to get the checkbox related to the text boxes to check the correct answer?!

Cheers for your time, Jon

quote:

---

jonwiper wrote:
But what im not sure about is how to get the checkbox related to the text boxes to check the correct answer?!

---

This method surely means that for each question, I would need to know which answer and therefore which associated check box is actually beside the right answer. No?

I.e. if optgivenanswer(1) is beside the right answer then if its value is true (selected) then it will be correct. But if the questions are being loaded randomly the correct answer isnt always loaded into the same answer textbox.

So how will the array help then?!

Sorry to keep bothering you but I really dont get this bit.

Thanks mate, Jon

The correct answer is a field into the recordset. You have the fields OptionalAnswer1, OptionalAnswer2 and OptionalAnswer3, and then the field CorrectAnswer, containing a value from 1 to 3 (the field that contains the correct answer). Each OptionalAnswer field doesn't know if it contains the correct one or not, but it doesn't matter as the correct one is written into an appropriate field. So, it's a record feature, and it changes for each record: the next question has its own correct answer.

It's the records that are returned randomly, so each record - in its random order - has its three OptionalAnswer fields and its own CorrectAnswer field.

Look the sample image I attached.
The CorrectAnswer field is not shown on the form, but it's included in the query, so you know its value. You have just to compare the hidden textbox value with the index (+1, as arrays are in Zero base) of the optionbuttons array, during the btnSubmit_Click event.

Of course this is just a sample, but you can choose another validation method.
Hope I have been clear.

[Edited by yronium on 15-04-2005 at 08:31 AM GMT]

____________________________
Real Programmer can count up to 1024 on his fingers

____________________________
Attached:

Hii,

  Can u please tell me how to cretae database for inserting quiz options. The forntend may be anything either asp or .net.

Regards,
Usha.

quote:

---

jahsen wrote:
Ok I am no expert but i hope this would help,

first if you need to store the scores into a database then you may need to create 2 tables on an access database name each player and question respectively, in the table player you may need to create necessary fields such as username password and scores

now how to access them in VB Goran thought me the easiest way so now i am sharing it to you, right click on the project exporer then choose add form, on the add form dialogue choose vb form wizard, choose the best that suites your need but i think for the account creation of each player it is best that you create a form with the textboxes for each datafield needed then on the create button you may put this codes

Dim cn As ADODB.Connection Dim myRecordSet As ADODB.Recordset Dim strsql As String          Set cn = New ADODB.Connection cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False             cn.Open                                                                 strsql = "INSERT INTO players(field1,field2,field3,field4,field5) VALUES('" & Text1 & "', '" & text2 & "', '" & Text3 & "','" & Text4 & "','" & Text5 & "');"                Set myRecordSet = New ADODB.Recordset             myRecordSet.Open strsql, cn                      Set myRecordSet = Nothing

for the questions just create another form for the question table each textbox for each fields but you might want to make the textbox for the answer invisible

for example you have 20 questions then generate a number from 1 to 20 make sure you have a fields for the question table which is from 1 to 20 as well

Dim x As Integer x = 0 x = Int((20 - 1 + 1) * Rnd) + 1

the above code will generate a number from 1 to 20 but several times may result to a same number

then in order to view the question then

use this

Dim cn As ADODB.Connection Dim myRecordSet As ADODB.Recordset Dim strsql As String          Set cn = New ADODB.Connection cn.ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\folder\access.mdb;Persist Security Info=False             cn.Open    strsql = "select * from question where questionindex = '" & X & '"" Set myRecordSet = New ADODB.Recordset             myRecordSet.Open strsql, cn                      Set myRecordSet = Nothing text1 = myrecordset!question text2 = myrecordset!answer

i hope somehow these gave you an idea, if not im sure Goran and Rodgers so as the others will help.

---

Dear ushakiran

the fourth post in this thread has an attached .zip file. It contains an Access 97 database, with all the concepts I spoke about. Have a look on it, and please notice the query into it, returning random questions. It has no forms, but in fact I built the one in the picture above after I posted it. You can do any customization on it.

Also, in this other thread I posted a modified version of it, in order to follow another approach on randomizing possible answers. There are two attached files in the thread, the second one has a form into. Have a look on it too.

Hope they may be of any help

____________________________
Real Programmer can count up to 1024 on his fingers

Private Sub Command1_Click()
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("C:\indiacars.mdb", False, False, ";pwd=")
Set rs = db.OpenRecordset("GeneralDetails")
Do While Not rs.EOF
rs.AddNew
" what shld be the insert statement to populate the DBpls reply"
rs.Update

rs.Close
db.Close
End Sub
Pleaseeeeeeee let me know

Dim LastReached As Long ' ... previous code... rs.MoveLast LastReached = rs.Fields("IDGeneralDetails").Value rs.AddNew rs.Fields("IDGeneralDetails").Value = LastReached + 1 rs.Fields("MySecondField").Value = "Johnny" rs.Fields("MyThirdField").Value = "English" rs.Fields("MyFourthField").Value = "New York" rs.Fields("MyFifthField").Value = #12/31/2005# rs.Fields("MyOtherField").Value = "banana" rs.Fields("MyAnotherField").Value = 37 rs.Fields("MyFurtherField").Value = vbNullString ' ... set the value for each recordset's field... rs.Update ' ... next code...

Hope it helps

____________________________
Real Programmer can count up to 1024 on his fingers