FIrst thing I would try is LibreOffice: XRowLocate Interface Reference
Thanks @Villeroy and @Ratslinger - sofar unfamiliar with ‘Firebird’ - never heard of it… At the moment at work, so will pick up tonight.
var = oForm.getBookmark
loRow = oForm.getRow
oForm.reload
oForm.MoveToBookmark(var)
IF loRow = 0 THEN
oForm.MoveToInsertRow()
END IF
Will run well here and move to new row if cursor has moved to new row.
@Qique
Not to agree or disagree with your direction but why use macros? Can do with Query or different database.
Samples:
HSQLDB embedded using query → ComputeQuery.odb (12.9 KB)
.
Firebird embedded using computed column → ComputeFirebird.odb (15.1 KB)
.
Edit:
It’s been around for some time. See:
.
The embedded version is available within LO but experimental features must be turned on to create (can execute existing such as attached sample without enabling experimental features) a Firebird embedded database within Base. See → Cannot create a new database - need JRE - #4 by Ratslinger
2 Likes
With some great additional help of @RobertG (through DM) I have the solution as I was looking for working as required. Hence I will continue this way for now. I have stored the links and information as provided by @Ratslinger and when I run into issues with my Libroffice solution, I can pick this up.
I would like to thank everybody for your time and effort to support me. Really appreciated.
Topic can be closed from my point of view. Cheers!
Would you please share the solution?
@Villeroy et all, sorry, sure. See below. All credits to @RobertG
Macro to be used in the ‘before record action’ of my form:
SUB IDSave(oEvent AS OBJECT)
stID = ""
oForm = oEvent.Source
IF oForm.supportsService("com.sun.star.form.component.DataForm") THEN
stID = oForm.getString(oForm.findColumn("ProjectID"))
loRow = oForm.getRow
END IF
END SUB
And in the ‘after record action’ of my form:
SUB IDGet(oEvent AS OBJECT)
oForm = oEvent.Source
oConnection = oForm.activeConnection()
oSQL_Statement = oConnection.createStatement()
IF stID = "" THEN
stSql = "CALL IDENTITY()"
oResult = oSQL_Statement.executeQuery(stSql)
WHILE oResult.next
stID = oResult.getString(1)
WEND
END IF
stSql = "UPDATE ""Project"" SET ""Saldo"" = CASE WHEN ""Bijgesteld Budget"" = 0 THEN ""Budget"" - ""Spent"" - ""Forecast"" ELSE ""Bijgesteld Budget"" - ""Spent"" - ""Forecast"" END WHERE ""ProjectID"" = '"+stID+"'"
oSQL_Statement.executeUpdate(stSql)
oForm.reload
IF loRow > 0 THEN
oForm.absolute(loRow)
ELSE
oForm.last
END IF
END SUB
And as ‘GLOBAL’ declaration (is that the right term) of variables within my ‘Main’ macro file:
GLOBAL stID AS STRING
GLOBAL loRow AS LONG
The above results both in calculating the 'Saldo" (= Balance), storing it in the ‘Saldo’ field of my ‘Project’ table when I save the record, as in showing me the updated ‘Saldo’ field on the screen (form) by returning to the new or changed record (row) of the ‘Project’ table.