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

[Astro]

Private Sub Command6_Click()
 
Dim i As Integer
Dim x As Integer
Dim a As Integer
Dim b As Integer
 
i = List1.ListCount
x = 0
b = 0
 
rst.Open "SELECT * from qnsdata_table", cn, adOpenDynamic, adLockOptimistic
 
    Do While Not x = i
 
        rst2.Open "SELECT DISTINCT Count(ansID) AS ans_count FROM ss_table WHERE '" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic
 
        a = rst2!ans_count
 
        Do While Not b = a
 
            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(x)
            
            rst3.Open "SELECT DISTINCT ss_table.ansID AS ss_table_ansID from ss_table WHERE '" & rst!qnsID & "' = qnsID", cn, adOpenDynamic, adLockOptimistic
    
            rst!ansID = rst3!ss_table_ansID
            
            rst3.Close
            rst.Update
            rst1.Close
            rst1.Open "UPDATE autonumber SET qnsdataID = qnsdataID +1", cn, adOpenDynamic, adLockOptimistic
            b = b + 1
 
        Loop
 
        rst2.Close
 
        x = x + 1
 
    Loop
 
rst.Close
MsgBox "okiedokz"
 
 
End Sub

Almost got it, rst!ansID = rst3!ss_table_ansID is now fixed, i can't do a rst.MoveNext, neither can i loop the recordset (not that i know how). Any suggestion? Haha i'm aware that my codes are crap long. Sorry.

Thanks

Astro