Loop The Loop: adding list item and sub-items assigned to items.

[Astro]

Hello there,

below are the codes i've written to record all the items (which are qnsID) in list box (List1) into qnsdata_table.

Private Sub Command6_Click()
Dim x As Integer
Dim y As Integer
 
x = List1.ListCount
y = 0
 
rst.Open "SELECT * from qnsdata_table", cn, adOpenDynamic, adLockOptimistic
 
Do While Not y = x
    rst1.Open "SELECT qnsdataID from autonumber", cn, adOpenDynamic, adLockOptimistic
    rst.AddNew
    rst!qnsDataID = "qd" & Right("000000" & CStr(CInt(rst1!qnsDataID) + 1), 6)
    rst!qnsID = Me.List1.ItemData(y)
    rst.Update
    rst1.Close
    rst1.Open "UPDATE autonumber SET qnsdataID = qnsdataID +1", cn, adOpenDynamic, adLockOptimistic
    y = y + 1
Loop
 
rst.Close
End Sub

The problem is that for each item (qnsID) there are several assigned (ansID) and i need them to be recorded along with the qnsID, into qnsdata_table.
______________________________
| qnsdataID | qnsID | ansID |
-----------------------------------
| qd0001 | qns0002 | ans0012 |
| qd0002 | qns0002 | ans0016 |
| qd0003 | qns0002 | ans0001 |
------------------------------------

In this case, should i have another Do While Loop within the current Loop, to find out how many 'ansID' there are which are assigned to each 'qnsID'.

I've tried

rst2.Open "SELECT Count(ansID) AS ansID_count FROM ans_table where....(conditions)
 
Me.Text2 = rst2!ansID_count         '(ive no idea where to store the count) value)
 
a = Me.Text2                              '(shift value into variable a)
b = 0

and here goes another loop, but it didn't work.

Need some advice and comments from you guys =)

Thanks a million.

Astro