nested loop problem

[TrollBro]

Hi - I cannot get the 1st outer loop to perform, but the 2nd iner loop seems to work fine. This code is basically trying to evaluate each name field against every other name field in a table. The code compares the 1st name to all the others, but never moves on to the 2nd name to repeat the process. I thought the code looked pretty straight forward, but I must be missing something. Any suggestions?

Thanks


If rs.BOF And rs.EOF Then ' no records
Else

Do Until rs.EOF

vResultKeep = 0 ' reset to 0 for each new test

v2Test = rs![Name]
vCountA = vCountA + 1
vCountB = 0
rs.MoveNext

Do Until rs.EOF
vTested = rs![Name]
vCountB = vCountB + 1
vResult = Simil(v2Test, vTested)

If vResult > vResultKeep Then
vResultKeep = vResult
End If

rs.MoveNext
Loop
Loop

 

[PHV]

never moves on to the 2nd name
standard behaviour as the inner loop exhausted the recordset (rs.EOF = True)

Any suggestions
Use another recordset in the inner loop.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrollBro]

Thanks PHV

I added a second rs but it still doesn't seem to perform the outer loop. (My test file has only four records so I tracked them as they get tested with v2TestNames and vTestedNames):

Set rs = CurrentDb.OpenRecordset("zttbl_Test1", dbOpenSnapshot)
Set rs2 = CurrentDb.OpenRecordset("zttbl_Test1", dbOpenSnapshot)

If rs.BOF And rs.EOF Then
Else

Do Until rs.EOF

vResultKeep = 0 ' reset to 0 for each new test

v2Test = rs![Name]
v2TestNames = v2TestNames & ", " & v2Test
vCountA = vCountA + 1
vCountB = 0
rs.MoveNext

Do Until rs2.EOF vTested = rs2![Name]
vTestedNames = vTestedNames & ", " & vTested
vCountB = vCountB + 1
vResult = Simil(v2Test, vTested)

If vResult > vResultKeep Then
vResultKeep = vResult
End If

rs2.MoveNext
Loop
Loop

 

[PHV]

Same problem (rs2.EOF) as you don't set rs2 on each iteration of the outer loop.

Another way is to use bookmarks.
Typed, untested:

While Not rs.EOF
  vResultKeep = 0 ' reset to 0 for each new test
  v2Test = rs![Name]
  vCountA = vCountA + 1
  vCountB = 0
  rs.MoveNext
  myBM = rs.Bookmark
  Do Until rs.EOF
    vTested = rs![Name]
    vCountB = vCountB + 1
    vResult = Simil(v2Test, vTested)
    If vResult > vResultKeep Then
      vResultKeep = vResult
    End If
    rs.MoveNext
  Loop
  rs.Bookmark = myBM
WEnd

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TrollBro]

Thanks PHV

I remmed and moved the set rs2 statement below and got the results I was looking for. I haven't worked with bookmarks in my code before so thanks for mentioning it - looks like a clean tool I should become familiar with.

Thanks!

Set rs = CurrentDb.OpenRecordset("zttbl_Test1", dbOpenSnapshot)
REM Set rs2 = CurrentDb.OpenRecordset("zttbl_Test1", dbOpenSnapshot)

If rs.BOF And rs.EOF Then
Else

Do Until rs.EOF 'DO UNTIL END OF RECORDSET
'Moved to here:
Set rs2 = CurrentDb.OpenRecordset("zttbl_Test1", dbOpenSnapshot)

 

[MajP]

With out seeing the whole code it is difficult to see what this does, but could you not just make a group by query to get the answer?

 

[TrollBro]

MajP

The code is as you suspected more complicated. The code I'm building need to evaluate various field values within each record against all comparable fields in all the other records in the file. The hunt is for "duplicate" records, but not neccessarily exact match (two data entry user mistakenly enter the same info but use different words etc), and "related" (two data entry users enter different parts of the same event into two separate records). These users would enter names in comment fields, comments in address fields, etc etc. I'm runing these loops through text matching algorithms, generating a scores from 0 to 1 where 1 = perfect match. Well, thats the plan anyway. With what I got for help this afternoon from PHV all I have left is some laborious grunt work to code for and capture results for various combinations of comparisons.

 

[ZmrAbdulla]

May be creating two recordset of the same table and looping through will be easy.
typed not tested.

If rs1.BOF And rs1.EOF Then ' no records
    Else
       Do Until rs1.EOF
           vResultKeep = 0 ' reset to 0 for each new test
           
           v2Test = rs1![Name]
           vCountA = vCountA + 1
           vCountB = 0
           
                rs2.MoveFirst
                Do Until rs2.EOF
                    vTested = rs2![Name]
                    vCountB = vCountB + 1
                    vResult = Simil(v2Test, vTested)

                        If vResult > vResultKeep Then
                            vResultKeep = vResult
                        End If

                    rs2.MoveNext
                Loop
           rs1.MoveNext
       Loop
End If

Zameer Abdulla