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

Populate Controls Randomly from Access Database

P: 3
Greetings

I am busy with a program that randomly selects data from a databse and then populates controls with the random data. the user must then select an option that must be stored for printing at the end of the program.

Below I have pasted my code. I am using VB6 and MSAccess Database.

Thanking You in Advance.

Julian

ps. I am a beginner

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Dim CN As ADODB.Connection
  3. Public RS As ADODB.Recordset
  4. Dim i As Long
  5. Dim strArray(5) As Long
  6. Dim x As Long
  7. Dim z As Long
  8. Dim WordCounter As Long
  9. Dim CurrentWord As Long
  10. Dim SQL As String
  11.  
  12. Private Sub OpenConnection()
  13.  
  14. Set CN = New ADODB.Connection
  15. CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\cas.mdb"
  16. CN.Open
  17.  
  18. End Sub
  19.  
  20. Private Sub ReturnRecords()
  21.  
  22. Set RS = New ADODB.Recordset
  23.  
  24. OpenConnection
  25.  
  26. With RS
  27.     .ActiveConnection = CN
  28.     .Source = "SELECT * FROM [caslit]"
  29.     .CursorType = adOpenStatic
  30.     .LockType = adLockOptimistic
  31.     .Open
  32. End With
  33.  
  34. End Sub
  35.  
  36. Private Sub cmdNext_Click()
  37.  
  38. Test    'jumps to sub. to generate the next question
  39.  
  40. lblStatus.Caption = RS.AbsolutePosition & "  of  " & RS.RecordCount
  41.  
  42. End Sub
  43.  
  44. Private Sub Form_Load()
  45.  
  46. ReturnRecords
  47.  
  48. lblDate.Caption = Date
  49.  
  50. lblTime.Caption = Time
  51.  
  52. 'Test
  53.  
  54. Finder
  55.  
  56. lblStatus.Caption = RS.AbsolutePosition & "  of  " & RS.RecordCount
  57.  
  58. End Sub
  59.  
  60. Private Sub Form_Paint()
  61.  
  62. Dim wid As Single
  63. Dim hgt As Single
  64. Dim x As Single
  65. Dim y As Single
  66.  
  67.     wid = Picture1.ScaleWidth
  68.     hgt = Picture1.ScaleHeight
  69.     y = 0
  70.     Do While y < ScaleHeight
  71.         x = 0
  72.         Do While x < ScaleWidth
  73.             PaintPicture Picture1.Picture, _
  74.                 x, y, wid, hgt
  75.             x = x + wid
  76.         Loop
  77.         y = y + hgt
  78.     Loop
  79.  
  80. End Sub
  81.  
  82. Private Sub mnuExit_Click()
  83.  
  84. Set CN = Nothing
  85. Set RS = Nothing
  86.  
  87. Load frmIndex
  88. frmIndex.Show
  89.  
  90. Unload Me
  91.  
  92. End Sub
  93.  
  94. Private Sub Test()
  95.  
  96. Dim RndNum As Long
  97. Dim strCount As Long
  98.  
  99. For i = 0 To 5
  100. If strCount = 1 Then
  101. i = i - 1
  102. strCount = 0
  103. End If
  104. strCount = 0
  105. Randomize
  106. RndNum = Int((5 * Rnd) + 1)
  107.  
  108.     x = 0
  109.     For x = 0 To i
  110.  
  111.         If strArray(x) = RndNum Then
  112.         strCount = 1
  113.  
  114.         End If
  115.  
  116.     Next x
  117.  
  118.     If strCount < 1 Then
  119.         strArray(i) = RndNum
  120.         strCount = 0
  121.  
  122.     End If
  123.  
  124. Next i
  125.  
  126. 'Call Finder
  127.  
  128. End Sub
  129.  
  130. Private Sub Finder()
  131.  
  132. Call Test
  133.  
  134. WordCounter = 5
  135.  
  136. 'create
  137. SQL = "SELECT * FROM caslit WHERE indexid="
  138. CurrentWord = 0
  139. For z = 0 To WordCounter - 1
  140. If z <> WordCounter - 1 Then
  141. SQL = SQL & "'" & strArray(z) & "' OR indexid="
  142. CurrentWord = CurrentWord + 1
  143. ElseIf z = WordCounter - 1 Then
  144. SQL = SQL & "'" & strArray(z) & "'"
  145. End If
  146. Next z
  147.  
  148. lblQuestion.Caption = RS!question
  149. OptA1.Caption = RS!c1
  150. OptA2.Caption = RS!c2
  151. OptA3.Caption = RS!c3
  152. OptA4.Caption = RS!c4
  153.  
  154. End Sub
Mar 1 '07 #1
Share this Question
Share on Google+
4 Replies


100+
P: 1,646
Hi. This is a lot of code. You have not posted a clear question. Which part of your code is not working? What, specifically, are you stuck on?
Mar 4 '07 #2

P: 3
Hi. This is a lot of code. You have not posted a clear question. Which part of your code is not working? What, specifically, are you stuck on?
[quote=tjbvo] Hi. Apologies.

The code "Private Sub Test()" is to do the random selection, and also to eliminate any duplicates.

The code "Private Sub Finder()" is to randomly select the data from the database.

However, when the cmdNext button is clicked, nothing happens. It is supposed to display the next random data in the lblQuestion, optA1, optA2, optA3 and optA4 fields/controls.

These are the only "Subs" that are giving me problems.

Thanking You for your help in Advance.
Julian

[quote]
Mar 4 '07 #3

100+
P: 1,646
Hi Julian
So it looks like your problem is in this snippet:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Test()
  2.  
  3.     Dim RndNum As Long
  4.     Dim strCount As Long
  5.  
  6.     For i = 0 To 5
  7.  
  8.         If strCount = 1 Then
  9.             i = i - 1
  10.             strCount = 0
  11.         End If
  12.  
  13.         strCount = 0
  14.         Randomize
  15.         RndNum = Int((5 * Rnd) + 1)
  16.  
  17.         For x = 0 To i
  18.  
  19.             If strArray(x) = RndNum Then
  20.                 strCount = 1
  21.             End If
  22.  
  23.         Next x
  24.  
  25.         If strCount < 1 Then
  26.             strArray(i) = RndNum
  27.             strCount = 0
  28.         End If
  29.  
  30.     Next i
  31.  
  32. End Sub
Copy this code and put comments all the way through saying what the different lines are for and what you want to do.
Mar 4 '07 #4

P: 3
Hi Julian
So it looks like your problem is in this snippet:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Test()
  2.  
  3.     Dim RndNum As Long
  4.     Dim strCount As Long
  5.  
  6.     For i = 0 To 5
  7.  
  8.         If strCount = 1 Then
  9.             i = i - 1
  10.             strCount = 0
  11.         End If
  12.  
  13.         strCount = 0
  14.         Randomize
  15.         RndNum = Int((5 * Rnd) + 1)
  16.  
  17.         For x = 0 To i
  18.  
  19.             If strArray(x) = RndNum Then
  20.                 strCount = 1
  21.             End If
  22.  
  23.         Next x
  24.  
  25.         If strCount < 1 Then
  26.             strArray(i) = RndNum
  27.             strCount = 0
  28.         End If
  29.  
  30.     Next i
  31.  
  32. End Sub
Copy this code and put comments all the way through saying what the different lines are for and what you want to do.
[quote=tjbvo]
Greetings

I have placed comments in both subs.

Hope this will be of help.


Private Sub Test()

Dim RndNum As Long ' declare variable
Dim strCount As Long ' declare variable

For i = 0 To 5 ' setup count
If strCount = 1 Then ' setup initial start point
i = i - 1 ' reduce start point by one
strCount = 0 ' setup actual start point in database
End If ' end setup
strCount = 0 ' confirm database start point
Randomize ' invoke random function
RndNum = Int((5 * Rnd) + 1) ' setup random function for return of data

x = 0 ' setup an array
For x = 0 To i ' declare array values

If strArray(x) = RndNum Then ' start of array
strCount = 1 ' confirm that array has a value

End If

Next x ' next array value

If strCount < 1 Then ' find value of array
strArray(i) = RndNum ' confirm that array value in not duplicate
strCount = 0 ' if array is duplicate set value to 0. force new random array value

End If

Next i ' next non duplicate array value

'Call Finder ' here i have commented out the next sub as i was having problems.
' this call statement will not be active as i was just testing the
' sub by calling the next sub procedure directly from here.

End Sub

Private Sub Finder()

Call Test ' calling the previous sub procedure

WordCounter = 5 ' setting of limit of variable

'create
SQL = "SELECT * FROM caslit WHERE indexid="
CurrentWord = 0 ' setting of initial value
For z = 0 To WordCounter - 1 ' declaring value of variable
If z <> WordCounter - 1 Then ' establishing value of variable
SQL = SQL & "'" & strArray(z) & "' OR indexid="
CurrentWord = CurrentWord + 1 ' setting value of variable
ElseIf z = WordCounter - 1 Then ' if value of variable is nothing then get next value
SQL = SQL & "'" & strArray(z) & "'"
End If
Next z ' return next value from database

lblQuestion.Caption = RS!question ' display value returned from database
OptA1.Caption = RS!c1 ' display value returned from database
OptA2.Caption = RS!c2 ' display value returned from database
OptA3.Caption = RS!c3 ' display value returned from database
OptA4.Caption = RS!c4 ' display value returned from database

End Sub

ps. In the sub "Finder" must the "next z" be before or after the display controls ?

Thanks for your assistance in advance

Julian
[quote]
Mar 10 '07 #5

Post your reply

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