LibreOffice Base, ThisDatabaseDocument and ThisComponent

[aigelmund]

My first question in this forum:

I created a report in LibreOffice Base. The report itself works fine.
I want to execute this report automatically when LibreOffice is started with the database. I try to do that with the following macro:
Sub OpenReport()
Dim rptName as String
Dim DB as Object
rptName = “rptMessdaten”
msgbox "open report " & rptName
DB =ThisDatabaseDocument
'further code till end
End Sub

I connected this macro with the OnOpen event of LO Base. This also works properly, as is proofed by the MsgBox.

When executing the line DB = ThisDatabaseDocument, I get the following error: BASIC Laufzeitfehler. Variable nicht definiert. (BASIC runtime error, variable not defined). The undefined variable is ThisDatabaseDocument. The macro is part of the Standard.Module1 area of the database, as it should be, and not in My Macros. I also execute this macro manually by F5, or by a button in a form. Always the same message.

I found in this forum several topics where people had a similar problem. They exchanged ThisDatabaseDocument with ThisComponent and tell that it worked after that modification. I also tried that, with no result. Also ThisComponent is undefined. This means, in my application both ThisDatabaseDocument and ThisComponent are always undefined. I got my code from this forum where in many places it is described why it fails when run under My Macros but works when run in the macro area of the database.

As it works for others, I assume i make a mistake which I do not see. And I hope somebody also might see what I do wrong.

My environment is:

1. Ubuntu 18.04 with LibreOffice 6.0.7.3
2. Windows 10 with LibreOffice 7.2.1.2
   In both environment the problem is identical.

Kind regards
Albert Igelmund

[RobertG]

I don’t find a event onOpen, when I have a look in the database file. There is a “Open Document” event, but connecting to the database will be the last thing, which will be executed. So I would connect to “View created”.

Then you could connect with

oDatasource = thisDatabaseDocument.CurrentController

To the datasource of your database.

[aigelmund]

Thanks for the fast reply.
You are right, the event is “open Document”. And also, that this event is the wrong one because the database might not yet be open. However, that does not solve the problem. Your code thisDatabaseDocument.CurrentController is in the end the same as mine. I split it only in 2 parts for showing that it is the thisDatabaseDocument which fails and not the .CurrentController.
I also executed the code via a button in a form and via F5, at a time when the database is definitely open. same result.
Though definitely the Open Document event is the wrong place for starting a report, that is nevertheless not the only problem.

[RobertG]

The connection to the database when starting a Base file has nothing to do with starting the report. The error you got was the error to connect to the database while opening the file, not while view has been created.

Have tested this but it seems there is something totally wrong while opening the report after connecting to the database. LO will hang when trying to open the report. Have set wait(100) or something like this after connecting to database but LO hangs every time …

SUB OpenReport
DIM oDatasource AS OBJECT
oDatasource = thisDatabaseDocument.CurrentController
IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
Wait(100)
thisDatabaseDocument.ReportDocuments.getByName("MyReport").open
END SUB

… and it will be the same with starting a form here.

[echo8hink]

FWIW: I’m using Linux Mint 20.2 Cinnamon 5.0.7, Linux kernel 5.4.0-90-generic, and LibreOffice 7.1.5.2 and the “OpenReport” sub works for me.

I tested it in an unregistered Base document with embedded HSQL file. I did put () after the sub, as: " Sub OpenReport()", but I doubt that has anything to do with it. I use the option to “enable experimental features” that may affect BASIC? I even commented out the “wait(100)” and it worked.

[RobertG]

Did you really start it with the database file (Tools → Customize → Events → View Created or something like Open Document, Activate Document)? Running the macro when Base window is opened will work here without any problem.

[echo8hink]

Sorry, I assigned the macro with Tools->Customize->Events->Open Document.

I totally missed the “View Created” part.

[RobertG]

But you got this working? When connecting to any event of the database document here I couldn’t open a form nor could I open a report. LO will try to open both, but it will hang with an empty window.

[echo8hink]

I have routinely used: Tools->Customize->Events->Open Document to display a switchboard form when opening a .odb file. I had never tried to open a report with the event. So, I tried your sub and one of my reports in a test.odb and it opened the report. It did display the “Allow Macros” and “Convert to Firebird” questions before running the report, so I’m not sure what is really to gain. It stops at the report preview. I had the thought it might do a report output to .pdf without more intervention. That was what my personal interest was… That’s probably why I missed the “View Created” part. I have not tested the .pdf thing yet…

[Ratslinger]

@RobertG

Report generated without Wait from main Base opening using Open Document event. No hang.

Version: 7.2.2.2 / LibreOffice Community
Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56
CPU threads: 8; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded
.
.
Edit: View Created also works
.
.
@echo8hink

Did, using macros, create PDF from reports. This was done without intervention and report not shown, to mail merge reports. You should be able to get your info from my samples posted there.

How to print a base report and send it by mail (mailmerge)

[RobertG]

I got it working only 5%. All other starts of the report will throw many JavaNullPointer-exceptions and LO hangs. First I got it starting was when execution LO with SAL_USE_VCLPLUGIN=gen ./soffice instead of kf5. But it will fail also many times with gen. Could be it has something to do with my Java installation (openjdk 11) on OpenSUSE 15.2 64bit rpm Linux.

Another Test with Collabora Office
Version: 21.06.4.20211008
Build ID: 51dc26375e00e380eea5f8c53f35f21dbf8f8166
CPU threads: 6; OS: Linux 5.3; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

will work without problem on the same machine.

[Ratslinger]

@RobertG

Could be it has something to do with my Java installation (openjdk 11)

Using openjdk 14 here on Ubuntu 20.04 Mate (64-bit).

[echo8hink]

openjdk 11.0.11 2021-04-20
OpenJDK Runtime Environment (build 11.0.11+9-Ubuntu-0ubuntu2.20.04)
OpenJDK 64-Bit Server VM (build 11.0.11+9-Ubuntu-0ubuntu2.20.04, mixed mode, sharing)

[aigelmund]

I was the one starting this thread. and while being absent some days, I see several people left their comments. Some have similar problems, others have no such problem at all. All comments concentrate on starting a report from an event, either Open Document or View created.
But I have that problem ALWAYS, not only when starting anything from an event, and also not only when starting a report. Whatever macro I create, when using ThisDatabaseDocument or ThisComponent this results in an error telling that the variable is undefined. This is also when running the macro manually with F5 long after all databases are open.
What I had not mentioned before: My database is SQLite connected via ODBC to LibreOffice. I will immediately tomorrow try to find out if that makes a difference.

[Ratslinger]

@aigelmund

ThisComponent this results in an error telling that the variable is undefined

Have just looked at this. Happen to have an SQLite3 DB with macros attached to resize screen and to display data from a table grid. Both routines use thisComponent and there are no errors.

Also inserted you statement of DB = ThisDatabaseDocument and received no errors.

HOWEVER----

If you have Option Explicit present, you will get the message if a DIM is not provided. So DB = ThisDatabaseDocument needs:
.

DIM DB as Object

Your code is showing the statements but I show no problem in my testing. Possibly need a sample of your Base file and DB.

[aigelmund]

Sometimes the problem sits before the computer!
I created a new database with a new table and a report on this table. I created a macro with code similar to that in my problem case. And it worked immediately, even from the Open Document event. But manually with F5 always.
Then I compared the code in the old and the new module. The difference was an Option Explicit present in the non-working version. And therefore all has to be declared explicitly. But it is not a missing
DIM DB as Object
as the previous comment of Ratslinger mentions. It is the fact that ThisDatabaseDocument is not explicitly declared. But as this is a reserved word, I cannot declare it with a DIM, cause that would make it a normal variable. But without being declared, it is also handled like a normal variable which then is undefined, as the eroor message says. Not very logical, but so it is. Removing the Option Explicit made the code work immediately.
This means, most of the problem is solved. Now I have only to find the correct event to which the macro will be bound. With Open Document it works sometimes, but sometimes it hangs the complete application. With other events it behaves similar. But that is another independent problem. The original one is solved.
Thanks to all of you for your comments. They helped me to think into the right direction.

[Ratslinger]

@aigelmund

It is the fact that ThisDatabaseDocument is not explicitly declared.

Cannot see that as the issue but do not have your Base file to look at. Removing Option Explicit does not solve the problem.
.
.

@aigelmund

But it is not a missing
DIM DB as Object
as the previous comment of Ratslinger mentions

Correct - that was noted in my comment:

@Ratslinger

DIM DB as Object

Your code is showing the statements but I show no problem in my testing. Possibly need a sample of your Base file and DB.

.
.
It also matters where your code is located. It should not be under My Macros as thisDatabaseDocument is not available there. On that, see → ThisDatabaseDocument vs ThisComponent?

[aigelmund]

@ Ratslinger:
You were right, it was NOT the “Option Explicit”!!!

I had in addition an “Option Compatible” and an “Option ClassModule” in my module. They were completely unnessary. And the “Option ClassModule” causes the problem. If i take that out, then ThisDatabaseDocument works fine as it should. Do I put the Option Classmodule back into the module, ThisDatabaseDocument behaves again as an undefined variable.

My code is now, without any extras, like this:
oController = ThisDatabaseDocument.CurrentController
If not (oController.isConnected()) Then oController.connect()
ThisDatabaseDocument.ReportDocuments.getByName(“myReport”).open
This works always when starting the macro manually with F5 (when no Option ClassModule is present).

Next step is that I call the report via push button from a form. I have 2 reports, a very simple one, and my more complicated one. The simple one always works. The complicated one hangs LO (though it works correctly with F5). But that is a separate problem. I will narrow it down before I come back with detailed question to that one.

Thanks to all for the help.
Albert