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