I have the following code that continually generates a “User-defined error, number 720 on line 69” and I am not sure what is wrong with the code.
Any constructive assistance is greatly appreciated.
Sub Click(Source As Button)
On Error Goto errHandle
Dim s As New Notessession
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument
Dim kwview As NotesView
Dim kwdoc As NotesDocument
Dim lview As NotesView
Dim strDB As String
Dim dbq As String
Dim ConnectionString As String
Dim con As New ODBCConnection
Dim qry As New ODBCQuery
Dim result As New ODBCResultSet
Dim colNames As String
Dim docVals As String
Dim lviewnav As NotesViewNavigator
Dim lentry As NotesViewEntry
Dim lentry2 As NotesViewEntry
Set db = s.CurrentDatabase
'First loop through all view documents and export them…
Set lview = db.GetView(“(ViewName)”)
colNames = “”
If lview.EntryCount > 0 Then
Forall vals In lview.Columns
colNames = colNames & |“| & vals.Title & |”,|
End Forall
colNames = Trim(Left$(colNames, Len(colNames) -1))
Set kwview = db.GetView(“(ApplicationKeywords)”)
Set kwdoc = kwview.GetDocumentByKey(“AccessDBPath”)
If Not kwdoc Is Nothing Then
strDB = kwdoc.keyValue(0)
End If
'Allow user to redefine Access DB path if necessary
strDB = Inputbox$("Enter full Access Database path: ",“Full Access Database path”, strDB)
dbq = “; Dbq=” & strDB
Forall DSN In con.ListDataSources
If (Instr(1,dsn, “Access”) > 0) Then ConnectionString = DSN & dbq
End Forall
If Not con.ConnectTo( ConnectionString ) Then
Messagebox "Could not connect to: " & ConnectionString
Exit Sub
End If
Set qry.Connection = con
Set result.Query = qry
Set lviewnav = lview.CreateViewNav()
Set lentry = lviewnav.GetFirst
While Not lentry Is Nothing
Set lentry2 = lentry
Set lentry = lviewnav.GetNext(lentry)
docVals = “”
Set doc = lentry2.Document
Forall vals In lentry2.ColumnValues
If ((Datatype(vals)>1 And Datatype(vals)<8) Or Left$(vals,1)=“$”) Then
docVals = docVals & vals & |,|
Else
docVals = docVals & |“| & vals & |”,|
End If
End Forall
docVals = Trim(Left$(docVals, Len(docVals) -1))
qry.SQL = |INSERT INTO “Leases” (| & colNames & |) VALUES (| & docVals & |);| '*******If I change this to a SELECT * statement, works fine
result.Execute '*******Line 69 dying here
doc.exportedtoAccess = Now
Call doc.Save(True, False)
Wend
Else
Messagebox “No documents to export.”
End If
'The get all the extra documents and export those to a different table
Set lview = db.GetView(“(OtherViewName)”)
If lview.EntryCount > 0 Then
Forall vals In lview.Columns
colNames = colNames & |“| & vals.Title & |”,|
End Forall
colNames = Trim(Left$(colNames, Len(colNames) -1))
Set lviewnav = lview.CreateViewNav()
Set lentry = lviewnav.GetFirst
While Not lentry Is Nothing
Set lentry2 = lentry
Set lentry = lviewnav.GetNext(lentry)
docVals = “”
Set doc = lentry2.Document
Forall vals In lentry2.ColumnValues
If ((Datatype(vals)>1 And Datatype(vals)<8) Or Left$(vals,1)=“$”) Then
docVals = docVals & vals & |,|
Else
docVals = docVals & |“| & vals & |”,|
End If
End Forall
docVals = Trim(Left$(docVals, Len(docVals) -1))
qry.SQL = |INSERT INTO “LeasePayments” (| & colNames & |) VALUES (| & docVals & |);|
result.Execute
doc.exportedtoAccess = Now
Call doc.Save(True, False)
Wend
Else
Messagebox “No other documents to export.”
End If
If con.IsConnected Then
result.Close(DB_CLOSE)
con.Disconnect
End If
Exit Sub
errHandle:
Msgbox(“An error in the application has occurred. Please call the” &_
" Help Desk or submit an e-Ticket online with the following" &_
" information and the steps taken to get to the error." & Chr(10) &_
Chr(13) & Chr(10) & Chr(13) & "(Export to Access) " & Lsi_info(2) & " - " & Lsi_info(12) &_
" Error: " & Error (Err) & " , number " & Err & " on line " & Erl())
Exit Sub
End Sub