Hello there, heres all my code to add questions from a listbox into a database table along with the answers assigned to a particular question.

VB Code:
  1. Private Sub Command6_Click()

  2. Dim i As Integer

  3. Dim x As Integer

  4. i = List1.ListCount

  5. x = 0

  6. Dim a As Integer

  7. Dim b As Integer

  8. Dim t As Integer

  9. t = 0

  10. 'rst2.Open "SELECT DISTINCT ansID from ss_table WHERE'" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic

  11.  
  12. b = 0

  13.  
  14. rst.Open "SELECT * from qnsdata_table", cn, adOpenDynamic, adLockOptimistic

  15.     Do While Not x = i

  16.         rst2.Open "SELECT DISTINCT Count(ansID) AS ans_count FROM ss_table WHERE '" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic

  17.             a = rst2!ans_count

  18.             Me.Text1 = a

  19.             Do While Not b = a

  20.             rst3.Open "SELECT DISTINCT ansID FROM ss_table WHERE '" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic

  21.             rst1.Open "SELECT qnsdataID from autonumber", cn, adOpenDynamic, adLockOptimistic

  22.             rst.AddNew

  23.  
  24.             rst!qnsDataID = "qd" & Right("000000" & CStr(CInt(rst1!qnsDataID) + 1), 6)

  25.             rst!qnsID = Me.List1.ItemData(x)

  26.             

  27.             Do While Not rst3.EOF

  28.             rst!ansID = rst3!ansID

  29.             rst3.Update

  30.             rst3.MoveNext

  31.             Loop

  32.             

  33.             rst.Update

  34.             rst1.Close

  35.             rst3.Close

  36.             rst1.Open "UPDATE autonumber SET qnsdataID = qnsdataID +1", cn, adOpenDynamic, adLockOptimistic

  37.             b = b + 1

  38.             Loop

  39.             rst2.Close

  40.     x = x + 1

  41.     Loop

  42. rst.Close

  43. MsgBox "okiedokz"

  44.  
  45. End Sub



VB Code:
  1. Do While Not rst3.EOF

  2.             rst!ansID = rst3!ansID

  3.             rst3.Update

  4.             rst3.MoveNext

  5.             Loop
(extracted from Command6_Click)
This doesn't appear to work, it is intended to add the list of answers assigned to a particular question.

For example, qns000001 has ans000001, ans000002 and ans000003. After clicking on the command button. only ans000003 is added to qns000001 'thrice'.

Any Suggestions?

Thank You

Astro