Here the DB modified for this thread.
Select a record and value in the id column is persisted in PLOT’s only field.
Rem: if msg does not show up, place cursor into field ID and leave it – TAB or Enter.
CopyingTB.odb (15.1 KB)
Thanks for trying again. I am not able to open it. I wonder what version Libre office it was created in? Here is what Chat GPT said:
" You’re running a newer LibreOffice version (7.6+) where embedded HSQLDB support was removed. That’s why “HSQLDB Embedded” doesn’t appear in the connection list anymore. LibreOffice 7.6+ only shows external connections (JDBC, Firebird, MySQL, etc.).
Your .odb file was made with an older version (6.x or earlier) that used embedded HSQLDB. That’s why you get:"
When I try to open CTBTT.odb and CopyingTB.odb I get these errors:
The connection to the data source “CTBTT” could not be established.
SQL Status: HY000
The connection to the external data source could not be established. No SDBC driver was found for the URL ‘sdbc:embedded:firebird’. at ./connectivity/source/commontools/dbexception.cxx:413
A connection for the following URL was requested “sdbc:embedded:firebird”.
Can you let me know what version Libre office this was made on?
Jonathan Francoeur a.k.a. Kernel Hazelnut
Everything Edible Landscaping H.Q.
48.381980, -123.707835
250 642 0267
6411 Sooke Rd., Sooke, B.C., Canada, V9Z 0A8
That’s obviously nonsense, generated by an AI without the ability to write something simple like “I have no clue.”
.
The abillity to work with HSQLDB was not removed. There are versions - especially on linux - wich are installed without java etc.
.
There may be problems to use embedded and external HSQLDB at the same time/in the same configuration. Would need to read/google the details on this…
ChatGPT says a lot of things. A lot of them also aren’t true. LLMs do not have a concept of “truth” as their entire function is to recomposite the words from their training data into decent-sounding word salad. Whether it was correct this time or not, I suggest you do not rely on ChatGPT or any other LLM for information, it will eventually mislead you.
I did:
sudo apt-get install libreoffice-sdbc-hsqldb
and it worked allowing me to open the tables once, but only once. After closing and reopening now I’m getting
The connection could not be established. The database was created by a newer version of LibreOffice. at ./connectivity/source/commontools/dbexception.cxx:41
and once again I can’t open the tables or the form but I was able to copy the macro
Option Explicit
Sub PersistID(Evt As Object)
REM DECLARE THE VARIABLES:
Dim F As Object, ID As Object, Con As Object, Stmt As Object
Dim sSQL As String, plot_id As String
Dim resultado As Integer
REM SET REFERENCES:
ID = Evt.Source.Model
F = Evt.Source.Model.Parent.Parent
Con = F.ActiveConnection()
Stmt = Con.createStatement()
REM BUILD SQL UPDATE AND PERSIST Plot #:
plot_id = CStr(ID.CurrentValue) ' plot id to persist
sSQL = "UPDATE ""PLOT"" SET ""ID"" = '" & plot_id & "'"
resultado = Stmt.executeUpdate(sSQL)
print resultado & " record(s) updated to " & plot_id
End Sub
You should first check in another machine with an independent LO’s install.
If “plot_id” is numeric (INTEGER) no need for
CStr(ID.CurrentValue) and ’
Dim plot_id As Long ' or Integer
[...]
sSQL = "[...] SET ""ID"" = " & plot_id
By the way: no more need for -get.
CopyingTB.odb is doing what I want by copying the id to the correct place. Thank you!
I’d like to understand it better so that I can modify it and use it for my application. Some questions to help me understand:
When/how is it called to run? I was expecting to see it listed in the events tab tied to something like ‘when record change’.
I’ve added a table to the example that shows the plot_id on the plot table. I have to select the table and click refresh to see the change. Is there a way to automate this?
Event is ID - On losing focus
But I think better is Mouse button released (to click inside control).
Control = NOME in enclosed new dummy Firebird DB without plot_id control, as per your design.
It could be. Must check what suits your setup better trying. And what Events are disponible of course.
And you can add Event (no need to just 1 Event). So you can add another Event if you want.
I think better having it, but no need. At first I didn’t grasp you don’t have it in the form.
Check enclosed new dummy DB, Form TESTE.
What change? Routine saves selected plot_id (be it present in the Form or not) without need to have changed data in the selected row.
If you have Form with Sub Forms linked together, when you change record (row), linked Form(s) row(s) change. But of course we must check your specific setup and needs.
And yes: basic way to automate is
TheForm.reload
But first we must check your setup (forms design) and how you do want them working together.
Check this new dummy DB (Firebird).
LO 25.8.2.2
OS: Windows 11 X86_64 (build 26200)
PersistID.odb (20.0 KB)
I am able to open CopyingTB.odb after having done sudo apt-get install libreoffice-sdbc-hsqldb
I am not able to open CTBTT.odb or PersistID.odb because I get the error
“The connection to the external data source could not be established. No SDBC driver was found for the URL ‘sdbc:embedded:firebird’. at ./connectivity/source/commontools/dbexception.cxx:413”
when I try to open tables of forms. Looking at CopyingTB.odb where/when is the macro engaged? I looked in the events of the control and form and did not see anything.
TableControl > ID > Column > Events (of the column).
But please open and check these 2 new dummy DBs I sent, in another machine with another LO’s install. I included routine for your layout without the plot_id control, and Event After update.
Enclosed example persists id both on
- clicking NOME
or - After updating GRANDEZA
Without id showing up in the TableControl.
PersistID_HSQLDC.odb (13.4 KB)
I can not open them with LO 25.8.22 or LO 7.3
What version did you use to create them?
Also, when I look at CopyingTB.odb I do not see the macro selected in the events of the form or the control.
Now I can no longer open the tables of CopyingTB like I could before. I’m not sure what changed because I am getting the same errors for CCTBTT.odb and PersistID.odb
SQL Status: HY000
The connection could not be established. The database was created by a newer version of LibreOffice. at ./connectivity/source/commontools/dbexception.cxx:413
I have installed Version: 7.4.7.2 / LibreOffice Community
Build ID: 40(Build:2)
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Debian package version: 4:7.4.7-1+deb12u9
Calc: threaded
and
Version: 25.8.2.2 (X86_64)
Build ID: d401f2107ccab8f924a8e2df40f573aab7605b6f
CPU threads: 4; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
I think this explains the problem OpenOffice file: The database was created by a newer version of LibreOffice - #5 by Ratslinger
I was able to recreate the tables and form from CopyingTB.odb into LAndpLAN.odb. I recreated the Plots module and the PersistID macro and assigned it to mouse release and it works! It gives this error but I added a control to view the PLOT table and I can see it is working.
Now I have to modify the macro to effect the Plot table using the forms in LANdpLAN.odb
… so first of all you must repair yor install.
And, as I said before, open for study the “exercises” in other machines with fresh LO’s installs.
Good luck!
Persisted ID should never show up in a Form. It’s a “filter” parameter for further uses.
great this one I can open! THank you
I’d like to find help wherever I can on this problem. It’s very difficult for me.