My environment: Windows 10, LibreOffice Version: 6.4.2.2 (x64) HSQLDB Split Database
I found some code in this forum to handle record not found, but it does not work for me.
The lines I added specifically for record-not-found, are commented in the code below
The code below works fine without the commented lines (as long as a record is found).
Since adding the new lines (the commented lines) I get
BASIC runtime error. Object variable not set
on this line
oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
How to fix the code for record not found?
Thank you for assistance
Sub LookUpSql (sSeekField,sSource,sWhereItem,sCompareItem,Optional sWhereItemTwo,Optional sCompareItemTwo) As String
Dim oStatement As Object
Dim sQuery As Object
Dim sSqlTask As String
Dim sLookup As String
If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) Then
ThisDatabaseDocument.CurrentController.connect
End If
oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
oStatement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE '-----new line for error test
If IsMissing(sWhereItemTwo) Then
If sCompareItem = "" Then
MsgBox("Error: No LookUp value for field " & sWhereItem)
Exit Sub
End If
sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """ = '" & sCompareItem & "'"
Else
If sCompareItemTwo = "" Then
MsgBox("Error: No LookUp value for field " & sWhereItemTwo)
Exit Sub
End If
sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """ = '" & sCompareItem & "' AND """ & sWhereItemTwo & """ = '" & sCompareItemTwo & "'"
End If
sQuery = oStatement.ExecuteQuery(sSqlTask)
Dim CursorTest AS Boolean '-----new line for error test
CursorTest = sQuery.first '-----new line for error test
If CursorTest = "False" Then '-----new line for error test
MsgBox("Record not found= " & sSeekfield) '-----new line for error test
End If '-----new line for error test
sQuery.Next()
sLookup = sQuery.GetString(1)
PutToField("MacroResponse",sLookup,"Text")
End Sub