Nested for loop (1 Viewer)

[boycie]

Hi all,

I am trying to loop through a table (tblTest) with two fields fldNum1 and fldNum2. The table has 10 records. I want to loop through each flNum1 and in turn loop through fldNum2 and multiply fldNum1 by fldNum2 (10 records), then back up to fldNum1 row 2 and again multiply all fldNum2 records until row 10. Hope Ive explained that ok.

I have managed to loop through and multiply fldNum1 * fldNum2 in each row but this is not what I need and Im struggling a liitle. Can you please help?
thanks

 

[Kiwiman]

Howzit

How About this

Dim rs as dao.recordset
Dim rs1 as dao.recordset
Dim db as dao.database

DIm i as integer
Dim x as integer
Dim dblCalc as Double

Dim strRS as string

strRS = "Select yourtable.field1, yourtable.field2 From yourtable"

set db = currentdb

set rs = db.openrecordset(strRs)
set rs1 = db.openrecordset(strRS)

if rs.eof and rs.bof then
    'Do nothing as recordset is empty
Else
    rs.movelast
    rs.movefirst
    for i = 1 to rs.recordcount
        dblcalc = 0
        if rs1.bof and rs1.eof then
             ' Do nothing
        Else
            rs1.movelast
            rs1.movefirst
            FOr x = 1 to rs1.recordcount
                 dblcalc = dblcalc + (rs.field1 * rs1.field2)
                 rs1.movenext
            next x
      ' DO something with dblcalc
      rs.movenext
   next i
end if

rs.close
rs1.close
set rs = nothing
set rs1 = nothing
set db = nothing

 

[boycie]

Thanks Kiwiman,
Ive managed to resolve it using the following code but I think yours looks more efficient and I will try it. My problem is knowing how to approach a problem and which loop types to use and why.

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim result As Double

rs.Open "table1", conn, adOpenKeyset, adLockOptimistic
rs2.Open "table1", conn, adOpenKeyset, adLockOptimistic


Do While Not rs.BOF And Not rs.EOF

Do While Not rs2.BOF And Not rs2.EOF

result = rs![fldNum1] * rs2![fldNum2]

Debug.Print result

rs2.MoveNext

Loop

rs.MoveNext
rs2.MoveFirst
Loop