Results 1 to 2 of 2
  1. #1
    dandoescode is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Posts
    85

    Possible Infinite Loop

    So I'm working on some code which will look at two tables. It will take the first string value from one table and then compare it with all other values of the other table finding the closest match. It will then cycle to the second string value from the first table and repeat the process. Each of the tables has about 1500 entries. Currently all the code does is lookup the string values, but when I run the code Access freezes up. I can't seem to find an error in the loop, but I cant imagine this process taking more than a couple minutes. Any thoughts?



    Code:
    Option Compare Database
    
    Private Sub Go_Click()
    
    Dim tableOne, tableTwo As String
    Dim tableOneId, tableTwoId As Integer
    Dim strOne, strTwo As String
    Dim tableOneCount, tableTwoCount As Integer         'Declare Variables
    Dim s As Variant
    Dim t As Variant
    Dim d As Variant
    Dim m, n
    Dim i, j, k
    Dim a(2), r
    Dim cost
    Dim maxIdOne, maxIdTwo As Integer
    Dim mydb As Database
    Dim rst As DAO.Recordset
    Dim Distance, MinDistance
    
    tableOne = Me.tableOne                              'Grab Table Values from Form
    tableTwo = Me.tableTwo                              ' And Initialize Variables
    
    maxIdOne = DMax("ID", tableOne)
    maxIdTwo = DMax("ID", tableTwo)
    
    Set mydb = CurrentDb()
    Set rst = mydb.OpenRecordset("strValueAn")
    
    tableOneId = 1
    tableTwoId = 1
    
            Do While tableOneId < maxIdOne + 1          ' Outer Do Loop cycles through strOne values
            
                    strOne = Nz(DLookup("strValue", tableOne, "ID = " & tableOneId), 0)
                    
                    Do While tableTwoId < maxIdTwo + 1  ' Inner Do Loop compares each strOne value against all strTwo values, looking for the closest match
                    
                    strTwo = Nz(DLookup("strValue", tableTwo, "ID = " & tableTwoId), 0)
                    
                    
                    tableTwoId = tableTwoId + 1
                    
                    Loop
            
            tableTwoId = 1
            
            tableOneId = tableOneId + 1
            
            Loop
    
    
    End Sub

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I do not see where you actually compare strOne to strTwo. Also the domain aggregate functions are relatively slow; using 2 nested recordsets would be more efficient. I see that you open a recordset, but you do not use it. Also, I'm not sure why you would use the NZ functions, it would follow that there is something in the fields you are comparing, if not filter those out first.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with a For Loop in VB
    By JFo in forum Programming
    Replies: 5
    Last Post: 09-29-2011, 02:45 AM
  2. Loop Problem
    By JDPrestige in forum Forms
    Replies: 3
    Last Post: 11-14-2010, 06:48 PM
  3. Do While loop
    By jgelpi16 in forum Programming
    Replies: 7
    Last Post: 07-23-2010, 08:21 AM
  4. VBA Loop
    By DreamOn in forum Programming
    Replies: 4
    Last Post: 06-25-2010, 03:35 AM
  5. Replies: 9
    Last Post: 04-28-2010, 11:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums