By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Loading tables into arrays

P: 17
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!
Mar 22 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Private rst As Recordset
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Set rst = CurrentDb.OpenRecordset("tblA")
  4. rst.MoveLast
  5. rst.MoveFirst
  6.  
  7. intNumOfRecords = rst.RecordCount
  8.  
  9. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGetColor_Click()
  2.  
  3. Randomize
  4. intRowToGet = Int((intNumOfRecords * Rnd) + 1)
  5.  
  6. rst.MoveFirst
  7. rst.Move intRowToGet - 1
  8.  
  9. Me.txtColor = rst!fldColor
  10.  
  11. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3. rst.Close
  4. Set rst = Nothing
  5.  
  6. End Sub

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

Pat

Share this Question
Share on Google+
14 Replies


ADezii
Expert 5K+
P: 8,699
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.
Mar 22 '10 #2

patjones
Expert 100+
P: 931
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
Mar 22 '10 #3

P: 17
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim localConnection As ADODB.Connection
  5. Public Sub Form_Load()
  6.     Randomize
  7.     Set localConnection = CurrentProject.AccessConnection
  8.  
  9. 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.
Mar 23 '10 #4

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Private rst As Recordset
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Set rst = CurrentDb.OpenRecordset("tblA")
  4. rst.MoveLast
  5. rst.MoveFirst
  6.  
  7. intNumOfRecords = rst.RecordCount
  8.  
  9. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGetColor_Click()
  2.  
  3. Randomize
  4. intRowToGet = Int((intNumOfRecords * Rnd) + 1)
  5.  
  6. rst.MoveFirst
  7. rst.Move intRowToGet - 1
  8.  
  9. Me.txtColor = rst!fldColor
  10.  
  11. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3. rst.Close
  4. Set rst = Nothing
  5.  
  6. End Sub

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

Pat
Mar 23 '10 #5

P: 17
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!
Mar 23 '10 #6

ADezii
Expert 5K+
P: 8,699
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
Mar 23 '10 #7

patjones
Expert 100+
P: 931
You are right, intRowToGet does need a declaration...
Mar 24 '10 #8

ADezii
Expert 5K+
P: 8,699
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.
Mar 24 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
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 :)
Mar 24 '10 #10

patjones
Expert 100+
P: 931
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
Mar 24 '10 #11

NeoPa
Expert Mod 15k+
P: 31,768
I always use Long for record numbers and Excel rows. It saves having to change things when the requirements grow ;)
Mar 24 '10 #12

P: 17
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.
Mar 25 '10 #13

ADezii
Expert 5K+
P: 8,699
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.
Attached Files
File Type: zip Random Test.zip (38.6 KB, 110 views)
Mar 25 '10 #14

NeoPa
Expert Mod 15k+
P: 31,768
@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!
Mar 26 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.