Excluding a com.sun.star.sdbc.SQLException error when "Message: The cursor points to before the first or after the last row.."

[papasierra]

Hello bright people,
I have set up a macro that takes a file name or web link from a column called “URL” in a subform called “URLForm” and puts that string into a button’s Target URL field.
This works fine for existing records. However when I create a new record in the parent form “MainForm”, there is no record in the column field called “URL” in the subform “URLForm” so my macro breaks at the line:
stCol = oform.Columns.getbyname("URL").getstring

I get an error:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: The cursor points to before the first or after the last row..

I have not been able to work out the condition to use in an IF statement to end the sub if there is no record present in the subform “URLForm”.
Thank you very much for your help.

I am using:
Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded

Here is the macro:

SUB InsertLocation
Dim oForm as Object
Dim oButton as Object
Dim oSubstService as Object
Dim stHomePath as String
Dim stFullPath as String
Dim stDrive as String
Dim stStart as String
Dim stCol as String
oForm = thiscomponent.drawpage.forms.getbyname("MainForm").getByName("URLForm")
oButton = oform.btnGetURL
oButton.TargetUrl = ""
oSubstService = CreateUnoService("com.sun.star.util.PathSubstitution")
stHomePath = oSubstService.substituteVariables("$(prog)", true)
stDrive = Mid(stHomePath, 9,1)
IF stDrive = "C" THEN
stFullPath = "file:///C:/Users/PS/Nextcloud/Documents/OwnFiles/Clinical/Useful info/"
ELSE
stFullPath = "file:///" + stDrive + ":/nc/Documents/Ownfiles/Clinical/Useful info/"
END IF
stCol = oform.Columns.getbyname("URL").getstring
stStart = Left(stCol, 3)
IF stStart <>  "" THEN
IF stStart = "htt" OR stStart = "www" THEN
oButton.TargetUrl = oform.Columns.getbyname("URL").getstring
ELSE
oButton.TargetUrl = stFullPath + oform.Columns.getbyname("URL").getstring
END IF
END IF
END SUB

[Ratslinger]

Hello,

Instead of:

stCol = oform.Columns.getbyname("URL").getstring

try:

stCol = oform.Columns.getbyname("URL").Value

That is what I am using on the form I just checked (not a sub form).

You can also escape the sub by checking if you are on a new record. Once you have oForm then:

If oForm.isNew then exit sub

If you still have issues a sample demoing the error will be of help. The error you are getting does not seem correct for what you are doing.
.
What is the data in the sub form based upon - table; query?

[papasierra]

Hi Ratslinger thank you very much for your helpful advice. I have changed getstring to Value. I would be interested to know what the difference between the two are.
I don’t know why I had that error but it replicated each time I triggered the macro.
The sub form is based on a table.

Using

If oForm.isNew then exit sub

did not solve the error problem. But when I changed the definition of oForm to the MasterForm the error went away. I have not idea why it didn’t work for the subform URLForm because that had a new form as well.
Here is the changed macro. I tidied up the duplication of oform.Columns.getbyname(“URL”).Value as well:

SUB InsertLocation
Dim oForm as Object
Dim oButton as Object
Dim oSubstService as Object
Dim stHomePath as String
Dim stFullPath as String
Dim stDrive as String
Dim stStart as String
Dim stCol as String
oForm = thiscomponent.drawpage.forms.getbyname("MainForm")
IF oForm.isNew THEN EXIT SUB
oForm = thiscomponent.drawpage.forms.getbyname("MainForm").getByName("URLForm")
oButton = oform.btnGetURL
oButton.TargetUrl = ""
oSubstService = CreateUnoService("com.sun.star.util.PathSubstitution")
stHomePath = oSubstService.substituteVariables("$(prog)", true)
stDrive = Mid(stHomePath, 9,1)
IF stDrive = "C" THEN
stFullPath = "file:///C:/Users/PS/Nextcloud/Documents/OwnFiles/Clinical/Useful info/"
ELSE
stFullPath = "file:///" + stDrive + ":/nc/Documents/Ownfiles/Clinical/Useful info/"
END IF
stCol = oform.Columns.getbyname("URL").Value
stStart = Left(stCol, 3)
IF stStart <>  "" THEN
IF stStart = "htt" OR stStart = "www" THEN
oButton.TargetUrl = stCol
ELSE
oButton.TargetUrl = stFullPath + stCol
END IF
END IF
END SUB

Thanks again. Your consistent application to helping beginners is inspirational!

[Ratslinger]

Hello,
First it is good that it is working.
.
Still do not think that actually solved the problem - just a kludge. I understand why that works but not clear why the sub form is not working. May try to create (or find in my older samples) the same using a sub form.
.
.
Edit:

Yes can see the error. Don’t recall having a form such as this. It is not a kludge - there is no record in the sub form for this occasion and thus presents the error. By checking for new in the main form & bypassing the code if true, it circumvents the situation. Keep what you currently have.

[RobertG]

Have had the same error in a subform. I solved this problem by

IF oForm.IsRowCountFinal AND oForm.RowCount = 0 THEN EXIT SUB