Run a Report using a button on a standalone .odt document

[1gatomontes]

Goal: run a Report using a button on a standalone .odt document.

The button finds and reads the macro, but I get the "Object variable not set" error message despite the fact that the variable is set just on the previous line.

So the button on the standalone document can obviously access the database as well as the macro that it is associated with, but it does not see (or cannot access) any of the reports in the database.

Stay away from macros? I'd love to but I just can't figure out how to do so!

So with or without macros, can anyone help?

Thanks to all the contributors of this forum! I've been finding a lot of the information I needed to get this far into my database project!

[OO 4.1.3 on Windows 10]

[Villeroy]

ThisDatabaseDocument is the database document (odb) where this code is saved in. This code might be saved somewhere else.

[1gatomontes]

Thanks Villeroy for the reply.

I'm not sure I catch your drift here. But maybe this will help.

Because I was not sure where the button was looking for the macro, I had the macro saved in 3 different places as you can see in the following image:
My Macros
Benevoles_Database.odb
form_rapports.odt

Macro source from Macro Organizer

But I've just realized that with the error message, I get to see where the button is looking for the macro (I cut out that part of the image in my original message).

So the button is looking under form_rapports.odt as you can see from the top left corner of the following image:

Macro location

So I tried pointing the button to "My Macros" but I got the same error message. I wanted to try pointing to Benevoles_Database.odb but that one I do not see when editing the .odt document.

Another thing I tried was changing the data source of the MainForm in the .odt document. I changed from the registered name of the database to pointing to its location on the drive. That did not change anything. Just had to try.

By the way I forgot to mention in my original message that this (you might have guessed) is an embedded database.

Could it be that I'm trying to do something that is beyond the limitations of OOBase?

[1gatomontes]

The following code

Dim oStatement As Object
oform = thiscomponent.drawpage.forms(0)
oconnection = oform.ActiveConnection
oStatement = oconnection.createStatement()
oStatement.executeUpdate( "UPDATE ""Tools"" SET ""ClearBit"" = FALSE" )

was suggested by F3K Total in
http://ooo-forums.apache.org/en/forum/v ... 5&p=269999

to run a macro from a standalone form.

Unfortunately, I just cant apply it to my situation because I don't understand the last line...

Can anyone help based on this?

[Villeroy]

It is just a matter of 3 clicks.
Open the Base document from a simple URL button or hyperlink on your form.
Click the report section.
Double-click the report.

Why do you try to write database macros if you do not understand databases nor programming?

Did you try the Report Runner extension and the Switchboard extension?

[1gatomontes]

Yes, we are aware we can open the Base document from a shortcut on an odt document, but would this work once we develop this database in server mode? That's the next step which we have not started working on.

We did install the Switchboard Extension and started looking into it. Thanks.

We have been using the Oracle Report Builder to develop our reports, but did not know about the Report Runner Extension. We'll look into this as well. Thanks again.

To answer your condescending question, we are volunteers who proposed to help this non-profit organization by developing a database to make it easier for them to manage parts of the organization. We do have some understanding of database, and we do have some understanding of programming. Of course, we all wish we were as knowledgable as you are, but then again, if we were, this forum would be empty, wouldn't it be?

May we ask why you are still replying to posts on this forum if you are overwhelmed by frustration and bitterness? We do not wish to know the answer to this question. It is simply a question for you to answer to yourself.

Nevertheless, we do appreciate the pointers.

[Villeroy]

Yes, we are aware we can open the Base document from a shortcut on an odt document, but would this work once we develop this database in server mode? That's the next step which we have not started working on.

[Some Source] --> [Base Document] --> [Other Office Documents]

[Some Source] can be a directory of text files or dBase files, a spreadsheet file or some kind of real database connected via JDBC or ODBC. The "embedded" type of database is just an ordinary connection to an external HyperSQL database. The embedded database is installed to a temporary directory when you start working with the database and it is wrapped back into the Base document when you finish editing.

[Base Document] is just an ordinary zip archive with some configuration data in XML and embedded Writer documents as forms and reports. Some zip files contain an embedded HyperSQL database but this makes only a minor difference since this database is accessible after it has been "installed" temporarily.

[Other Office Documents] are text documents in the ODF (Open Document Format). It makes no difference if they are embedded in the Base document or stand-alone text documents or Calc spreadsheets. As an exception to the rule, the reports created by the Oracle Report Builder extension are not stored in ODF but what comes out is a text document anyway.

[Spreadsheet] --> [Base Document] --> [Serial Letter]
[Spreadsheet] --> [Base Document] --> [Database Range in Calc]
[Text Files] --> [Base Document] --> [Pivot Table in Calc]
[dBase Directory] --> [Base Document] --> [Embedded Report (Report Builder or Text Table)]
[ODBC Database] --> [Base Document] --> [Embedded Form]
[JDBC Database] --> [Base Document] --> [Stand-alone Form on Calc sheet or Writer document]


The only constant factor is a Base document which stores all info that is required for the left hand connection in order to push data to the right side. Once you start working with an embedded HyperSQL database you have just an ordinary JDBC connection to a true database which is installed to a temporary location.

Spreadsheets and text are read-only. You have to use some other program to edit these.
dBase and true databases may be editable in Base. Input forms are attached to office documents (Writer in most cases but Calc forms are possible as well) and the form controls linked to some editable database column can write data into the database. In case of read-only connections, combined forms and subforms can display related row sets.

[Villeroy]

A Basic macro to open embedded reports or forms which can be saved globally in some library under "My Macros" or within the calling document. It is supposed to be triggered by a push button.

The macro reads the "DataSourceName" from the calling button's form.'This can be a registered database name or the URL of the odb document.
The name of the form or report to be opened is read from the calling button's "Additional Info" property. This is a hierarchical name which may include one or more subfolders.

If you want to open a form, link the button's execute event to sub OpenFormButton; to call a report use sub OpenReportButton.

Code: Select all

REM  *****  BASIC  *****

Sub OpenReportButton(e)
	REM blue print of the calling form control
	REM the form of the calling form control
	oCtrlModel = e.Source.getModel()
	oForm = oCtrlModel.getParent()

	REM hierarchical name of the report noted in the control's additional info
	REM myReport or Subfolder/myReport
	sName = oCtrlModel.Tag
	OpenEmbedded oForm, sName, True
End Sub

Sub OpenFormButton(e)
	REM blue print of the calling form control
	REM the form of the calling form control
	oCtrlModel = e.Source.getModel()
	oForm = oCtrlModel.getParent()

	REM hierarchical name of the report noted in the control's additional info
	REM myReport or Subfolder/myReport
	sName = oCtrlModel.Tag
	OpenEmbedded oForm, sName, False
End Sub

Sub OpenEmbedded(oForm, sName$, bRep As Boolean)
	REM this is either a registered name or file:///path/doc.odb
	src = oForm.DataSourceName
	srv = createUnoService("com.sun.star.sdb.DatabaseContext")
	if FileExists(src) then
		url = src
	else
		url = srv.getDatabaseLocation(src)
	endif
	REM get the database document that belongs to the calling form
	db = StarDesktop.loadComponentFromURL(url, "_default", 0, Array())
	db.CurrentController.connect()
	if bRep then
		obj = db.ReportDocuments
	else
		obj = db.FormDocuments
	endif	
	doc = obj.getByHierarchicalName(sName)
	doc.open()
End Sub

[Villeroy]

If your reports are not too large you can also have stand-alone reports: [Writer] Stand-Alone Database Reports