[RESOLVED] access vba looping issue

[Astro]

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.

Private Sub Command6_Click()
Dim i As Integer
Dim x As Integer
i = List1.ListCount
x = 0
Dim a As Integer
Dim b As Integer
Dim t As Integer
t = 0
'rst2.Open "SELECT DISTINCT ansID from ss_table WHERE'" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic
 
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
            Me.Text1 = a
            Do While Not b = a
            rst3.Open "SELECT DISTINCT ansID FROM ss_table WHERE '" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic
            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)
            
            Do While Not rst3.EOF
            rst!ansID = rst3!ansID
            rst3.Update
            rst3.MoveNext
            Loop
            
            rst.Update
            rst1.Close
            rst3.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

Do While Not rst3.EOF
            rst!ansID = rst3!ansID
            rst3.Update
            rst3.MoveNext
            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

[[Pieter]]

Use also

rst.MoveNext

Pieter

[Astro]

Hey Pieter,

i've tried adding that line in different parts of my code. it didnt work.
Thanks.

Astro

[[Pieter]]

I think you should add it here.

Do While Not (rst3.EOF Or rst.EOF)
    rst!ansID = rst3!ansID
    [B]rst.Update
    rst.MoveNext[/B]
    rst3.Update
    rst3.MoveNext
Loop

BTW why do you update rst3. you didn't change it?

Pieter

[Astro]

Hey Pieter,

You're right, i've removed rst3.Update. Sadly it's not working still after adding that line of code you last suggested. It's still adding the last item in the list.
Am i looping the wrong way?

Thanks

Astro

[si_the_geek]

I've been trying to work out exactly what's going on in your code, and it doesn't make too much sense to me. I think it's probably best if we take a step back from the code for a moment, and start again.

I can see you have tables called qnsdata_table, ss_table, and autonumber (bad name to use!).

What is the structure of your database? How do the tables relate to each other?

[salvelinus]

Looking at the color formatting of the code you posted, without going through it, it looks like you have missing or misplaced quotes.
Take this line, for example:

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

That looks ok, actually, but vbcode is formatting it as if everything after the single quote in "' = qnsID" is a comment. I don't know if I'm missing something in the code or if it's a vbcode formatting error. Is there a difference in your editor?
FWIW, .MoveNext typically comes just before the Loop statement for a recordset.
One thing you may want to check is if the data in your listbox contains single or double quotes. That can throw off your SQL. You may have to test the data and fix the string if it does. I.e., if your data is "St. Marys", you're ok , but if it's "St. Mary's", you could have a problem with the single quote. You can usually fix it by doing a Replace for single quotes with two single quotes, in that example.

[si_the_geek]

That is a bug in the VBCode formatting of this site - the last ' always gets read as a comment.

Absolutely right on the .MoveNext , and the use of Replace

[RobDog888]

You can do a workaround to prevent the false commenting with vbcode tags by adding a last dummy comment.

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

[Astro]

Hey Pieter,

I got it man!

Appreciate that you took the time to understand my codes.
I'll paste the entire chunk and explain it at the bottom.

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

I've removed a fuzzy wuzzy loop, so im down to 2 loops now.

rst3.Open "SELECT DISTINCT ansID FROM ss_table WHERE '" & Me.List1.ItemData(x) & "' = qnsID", cn, adOpenDynamic, adLockOptimistic
            rst3.MoveFirst
            Do While Not rst3.EOF
            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)
            rst!ansID = rst3!ansID
            rst3.MoveNext
            rst.Update
            rst1.Close
            rst1.Open "UPDATE autonumber SET qnsdataID = qnsdataID +1", cn, adOpenDynamic, adLockOptimistic
            Loop
            rst3.Close
            rst2.Close

I did a Do While Not rst3.EOF and right before it, added rst3.MoveFirst.
Thats it =)

Thanks man!

Astro