First here is my code
It seems to never break out of the second "do until" loop. I know this is just some silly little thing, but I can't for the life of my see where i've screwed up the logic.
Here is my debug.print output (you can see where I placed the debug comments in the code above)
A. print out strSql1
B. print out transformer number
C. print out strSql2 - bolded is a varible
D. print out customer name associated to secondary transformer
E. print out primary ID to update
F. print out customer name associated to primary transformer
G. print out strSql3 - bolded is from variable
H. print our number of loops through 2nd "do until"
repeats E - H
Code:
Option Compare Database
Sub tmatch()
' Set Database
Dim db As DAO.Database
Set db = CurrentDb
' Get unique secondaries without matching record in list with agreements
Dim strSql1 As String
strSql1 = "SELECT [SECONDARIES_Unique_wo-Agreements].ID, [SECONDARIES_Unique_wo-Agreements].[Account Number], [SECONDARIES_Unique_wo-Agreements].Premise, [SECONDARIES_Unique_wo-Agreements].[Customer Number], [SECONDARIES_Unique_wo-Agreements].[Customer Name], [SECONDARIES_Unique_wo-Agreements].Transformer, [SECONDARIES_Unique_wo-Agreements].[Tariff Code], [SECONDARIES_Unique_wo-Agreements].[Tariff Description], [SECONDARIES_Unique_wo-Agreements].[Consumption (Kwh)] " & vbCrLf & _
"FROM [SECONDARIES_Unique_wo-Agreements];"
Debug.Print ("sql1: " & strSql1)
' Set resultset variable
Dim rst1 As DAO.Recordset
Set rst1 = db.OpenRecordset(strSql1, dbOpenDynaset)
Dim n As Long
n = 1
' Do until i run out of records
Do Until rst1.EOF
' Get primary records where transformer matches secondary transformer
Dim strSql2 As String
Dim Trans2 As String
Trans2 = rst1.Fields("Transformer").Value
Debug.Print ("Trans2: " & Trans2)
strSql2 = "SELECT PRIMARIES.ID, PRIMARIES.Account, PRIMARIES.Premise, PRIMARIES.[Service Pt], PRIMARIES.[Customer No], PRIMARIES.[Customer Name], PRIMARIES.Transformer, PRIMARIES.Tariff, PRIMARIES.[Tariff Decs] " & vbCrLf & _
"FROM PRIMARIES " & vbCrLf & _
"WHERE (((PRIMARIES.Transformer)='" & Trans2 & "'));"
Debug.Print ("sql2: " & strSql2)
' Set resultset variable
Dim rst2 As DAO.Recordset
Set rst2 = db.OpenRecordset(strSql2, dbOpenDynaset)
' Customer name from secondary
Dim CustName2 As String
CustName2 = rst1.Fields("Customer Name").Value
Debug.Print ("CustName2: " & CustName2)
Dim m As Long
m = 1
Do Until rst2.EOF
Dim ID1 As Long
ID1 = rst2.Fields("ID").Value
Debug.Print ("ID1: " & ID1)
Dim CustName1 As String
CustName1 = rst2.Fields("Customer Name").Value
Debug.Print ("CustName1: " & CustName1)
' First check for exact match
If CustName1 = CustName2 Then
' Update with PK and "exact"
Dim strSql3 As String
strSql3 = "UPDATE [SECONDARIES_Unique_wo-Agreements] SET [SECONDARIES_Unique_wo-Agreements].[PRIMARIES_ID-PK] = '" & ID1 & "', [SECONDARIES_Unique_wo-Agreements].[Match] = 'exact'" & vbCrLf & _
"WHERE ((([SECONDARIES_Unique_wo-Agreements].Transformer)= '" & Trans2 & "'));"
Debug.Print ("sql3: " & strSql3)
DoCmd.SetWarnings False
DoCmd.RunSQL (strSql3)
DoCmd.SetWarnings True
Else
' Second check for partial match
Dim CN1arr() As String
CN1arr() = Split(CustName1, " ")
Dim CN2arr() As String
CN2arr() = Split(CustName2, " ")
Dim NumMatches As Long
NumMatches = 0
For Each v In CN1arr
For Each w In CN2arr
If v = w Then
NumMatches = NumMatches + 1
End If
Next w
Next v
If NumMatches > 0 Then
' Update with PK and "partial"
Dim strSql4 As String
strSql4 = "UPDATE [SECONDARIES_Unique_wo-Agreements] SET [SECONDARIES_Unique_wo-Agreements].[PRIMARIES_ID-PK] = '" & ID1 & "', [SECONDARIES_Unique_wo-Agreements].[Match] = 'partial: " & NumMatches & "'" & vbCrLf & _
"WHERE ((([SECONDARIES_Unique_wo-Agreements].Transformer)= '" & Trans2 & "'));"
Debug.Print ("sql4: " & strSql4)
DoCmd.SetWarnings False
DoCmd.RunSQL (strSql4)
DoCmd.SetWarnings True
End If
End If
Debug.Print (m & ": Finished ONE PRIMARY")
m = m + 1
Loop
Debug.Print (n & ": Finished SECONDARY - last")
n = n + 1
Loop
End SubIt seems to never break out of the second "do until" loop. I know this is just some silly little thing, but I can't for the life of my see where i've screwed up the logic.
Here is my debug.print output (you can see where I placed the debug comments in the code above)
A. print out strSql1
B. print out transformer number
C. print out strSql2 - bolded is a varible
D. print out customer name associated to secondary transformer
E. print out primary ID to update
F. print out customer name associated to primary transformer
G. print out strSql3 - bolded is from variable
H. print our number of loops through 2nd "do until"
repeats E - H
