Loading tables into arrays

[rnashting]

Hello everyone! I am trying to create a quiz program, and am having a mental block. I've made a form, and want the cmdBegin_click() function to call another function called loadtrivia(). The loadtrivia function is going to take 3 fields from a table (question#/question/answer), randomize them and use an input box to ask the question and accept the answer. The thing is...I'm not sure how to load the table. I'm thinking an array, but the syntax is confusing me. Could anyone start me on the path to success? I appreciate your help!

[ADezii]

Here is a Link to an Article on the GetRows() Method which will retrieve Data from a Recordset and place it in a 2-Dimensional Array:
http://bytes.com/topic/access/insigh...-using-getrows
P.S. - You could also create a Recordset, then select a Random Record Number within it, navigate to it, and post the Question.

[patjones]

Hi rnashting -

A couple of things. How many questions do you want to pull from the table at one time? And are you saying that you want to randomly pick which questions get pulled?

Visual Basic has an array data structure, but you might be more interested in using a recordset.

Pat

[rnashting]

Hm. I'm thinking that a recordset is the way to go, but this is getting a little bit above my skill level. Here's what I've done:

1 - Created a table with 3 fields (ID, question, answer)
2 - Created a form with a command button to start the quiz

The code I have so far is:

Option Compare Database
Option Explicit
 
Dim localConnection As ADODB.Connection
Public Sub Form_Load()
    Randomize
    Set localConnection = CurrentProject.AccessConnection
 
End Sub

So when I click the cmdBegin button, I'm thinking of bringing the table into the recordset, randomizing the questions, and asking them via input box. I know how to do the verifying of the answer, but I'm unsure of how to get the questions into the recordset and navigate it. The only examples I've worked with in the past used .dat files, and that seems like a horrible way. Lets say that I have two records in my table:

1, What color is the sky?, blue
2, What is 2 x 2?, 4

Ideally I want to put them in a recordset, then randomly ask one of the questions.
As you can see I'm totally in left field, but I appreciate any help you can offer.

[patjones]

Hi -

ADezii's article is very informative and an excellent resource...especially if you are used to using arrays. If you want to use a recordset, try something like this.

I made a simple table ("tblA") with an ID column, and a column to hold names of colors ("fldColor"). My code loads the table into a recordset, randomly picks a color and displays it's name in a text box on a form.

At the top of the form module (above Option Compare Database) declare the recordset and a Long as:

Private rst As Recordset
Private intNumOfRecords As Long

For the Form_Open event, put the table into a recordset, populate the recordset, and find out how many rows there are:

Private Sub Form_Open(Cancel As Integer)
 
Set rst = CurrentDb.OpenRecordset("tblA")
rst.MoveLast
rst.MoveFirst
 
intNumOfRecords = rst.RecordCount
 
End Sub

For command button event, get a random number between 1 and however many records there are, inclusive; go to the first record, and then move however many records determined by the random number:

Private Sub cmdGetColor_Click()
 
Randomize
intRowToGet = Int((intNumOfRecords * Rnd) + 1)
 
rst.MoveFirst
rst.Move intRowToGet - 1
 
Me.txtColor = rst!fldColor
 
End Sub 

I populate my text box with the record that came up, but you can do whatever you need to with the field values at that point. Since I made this recordset available to all procedures within the form module, you can access the values from somewhere else in the module...maybe to validate the answer the user types in, for instance.

Finally, clean up when the form is closed:

Private Sub Form_Close()
 
rst.Close
Set rst = Nothing
 
End Sub

I didn't put any error handling in these, so just be aware of that. Let me know what you think.

Pat

[rnashting]

That looks good. The only thing that looks weird is that I don't see a declaration for the intRowToGet, but I'll plug that in also and play with it. Hopefully this gets me rolling. Thanks!

[ADezii]

I've just responded to a Thread similar to this one. If you like, feel free to Download the Attachment that I made available to the Poster. It is at the following Link (Test Questions.zip):
http://bytes.com/topic/access/answer...-a#post3555861

[patjones]

You are right, intRowToGet does need a declaration...

[ADezii]

Years ago, I won't say how many, I developed a Random Test Generator Component for an Organization. This produced hard copy Tests in the form of an Access Report with an optional number of Randomly Generated Questions. It also has the capability of generating an Answer Sheet for those Random Questions. I am getting it all together now for you, and will upload it as soon as it all comes together. It is just one Component among many others, and has to removed accordingly. Stay tuned.

P.S. - It does incorporate many of the items previously mentioned in its logic.

[NeoPa]

Pat,

Your naming of the Long variable as intNumOfRecords will not cause the code to fail, but it is something I suggest you avoid in all circumstances. I'm sure it just slipped through after changes were made on the fly, but such code can be very hard to follow. Worth avoiding if you can.

PS. This is not intended as criticism. Simply drawing your (and others') attention to a detail best fixed when using in production.

Good solution by the way :)

[patjones]

NeoPa -

Thanks!

I think I was intending to use an Integer, which I am assuming is sufficient for the task at hand. But maybe rnashting can let us know for sure.

I try to stick to Leszynski notation for the naming of all objects and variables; and by that reckoning I should have used lngNumOfRecords, if I really intended to use Long.

Pat

[NeoPa]

I always use Long for record numbers and Excel rows. It saves having to change things when the requirements grow ;)

[rnashting]

Thanks ADezii! I would be super interested in seeing that. I think I'm on pace to do my own thing for now, but seeing yours can show me things I'm sure that I don't know that I don't know.

BTW...I've tried many sites for programming advice and help, and this one is definitely the best community around. Hopefully someday in the not too distant future I can have enough knowledge to start helping instead of needing help.

[ADezii]

Here is that Attachment that I was referring to, mashting. The concept here is the production of Hard Copy Tests with randomly generated Questions along with corresponding Answer Sheets, but it does parallel this Thread in some respects.

[NeoPa]

@rnashting
Thank you for your positive comments.

I doubt it will be too long before you're able to help. One can pick up a lot just from comments made, and your attitude seems right on the button. You're not looking for work to be done for you, simply for a helping hand along the way; Some pointers to get you going along the right track.

Welcome to Bytes!