Loop help please (works but errors)

[alloyd1]

I am looping records and extracting data via imacros. The loop works but after it is done, I always get an error. I was hoping a fresh set of eyes can tell me where I am going wrong on the loop.

Your help is greatly appreciated.

Option Compare Database

Public Function GetTitle()
On Error GoTo Err_GetTitle_Click

Dim Rs As DAO.Recordset
Dim cntr, varRecCnt
Dim iim1, sql As String
Dim iret As String
Dim sHostName As String
Dim tempvalueid As String
Dim tempMLSID As String

Set Rs = CurrentDb.OpenRecordset("tbl_pulltitle")

Set iim1 = CreateObject("Imacros")
iret = iim1.iimInit
iret = iim1.iimDisplay("Title Extraction")

'Rs.MoveFirst
Do Until Rs.EOF

    tempvalueid = Rs!Reference_ID
    tempMLSID = Rs!MLSNumber
    'Set the variable for the macro -var_THISISVARIABLEHERE
    iret = iim1.iimSet("-var_MLSID", Rs!MLSNumber)

'Play the Macro
   iret = iim1.iimPlay("--------1extract")
   'As it copies the fields from the macros this will Append Results into Database
   tempLastExtract1 = iim1.iimGetLastExtract(1)
   tempLastExtract2 = iim1.iimGetLastExtract(2)
   tempLastExtract3 = iim1.iimGetLastExtract(3)
   tempLastExtract4 = iim1.iimGetLastExtract(4)
   tempLastExtract5 = iim1.iimGetLastExtract(5)
   tempLastExtract6 = iim1.iimGetLastExtract(6)
   tempLastExtract7 = iim1.iimGetLastExtract(7)
   tempLastExtract8 = iim1.iimGetLastExtract(8)

    CurrentDb.Execute "INSERT INTO tbl_title_results ( ListingID, OwnerName) Values ('" _
    & tempMLSID & "',  '" & iim1.iimGetLastExtract(1) & "')"
   
   If iret < 0 Then
        
      'MsgBox iim1.iimGetLastError()
      Exit Function
     
   End If
    Rs.MoveNext
Loop

iret = iim1.iimDisplay("Done!")


Err_GetTitle_Click:
    MsgBox Error()

End Function

 

[MarkK]

What's the error? Where does it happen?

 

[alloyd1]

It is at the end of the loop. Instead of finishing up I get the error which has nothing in it. So I am not sure what the error is.

Err_GetTitle_Click:
MsgBox Error()

If I remove MsgBox Error() then it just finishes.

 

[alloyd1]

I believe I had the error msgbox wrong. I switched it out to this and it seems to have fixed the issues

Exit_GetTitle_Click:
    Exit Function

Err_GetTitle_Click:
    MsgBox Err.Description
    Resume Exit_GetTitle_Click:

 

[MarkK]

You need to exit the function before the error handler runs. The most common pattern for error handling goes like this . . .

Sub SomeRoutine
On Error GoTo Handler

[COLOR="Green"]    'code here[/COLOR]

Final:
    Exit Sub

Handler:
    MsgBox Err & " " & err.description
    Resume Final

End Sub

See how "Exit Sub" happens before MsgBox?

You can also do . . .

Sub SomeRoutine
On Error GoTo Handler

[COLOR="Green"]    'code here[/COLOR]
    Exit Sub

Handler:
    MsgBox Err & " " & err.description

End Sub

Cheers,