Problem with macro to run a report

English
#1

I found a macro by Ratslinger from 2018 that looks like it will work (for running a report from a form button) and substituted my relevant info, but I get a “Basic Runtime Error - Variable Not Defined” at the Line “oController = Thisdatabasedocument.currentController”. I have defined all the variables, I think. Any ideas on what is stopping me? The macro is shown below. Thanks.

Rem Macro to run a report (Based on a query)
Sub Run_Report
    Dim oDrawPage as Object
    Dim oForm as Object
    Dim oController as Object
    Dim oReportdoc as Object
    oDrawPage = ThisComponent.getDrawPage()
    oForm = oDrawPage.Forms.getByName("Customer_Form")
    If oForm.IsNew then
        oForm.insertRow()
    else
        oForm.updateRow()
    EndIf
    oController = Thisdatabasedocument.currentController
    if not oController.isconnected then oController.connect
    oReportdoc = Thisdatabasedocument.reportdocuments.getbyname("Job Schedule Report").open
End Sub

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

#3

ThisDatabaseDocument is the embedding document if the code is stored within the database document. I don’t recall, how to access the database document from code that is stored in the global scope (“My Macros”).

The following code tries to get the right object anyway. It will also work with forms and/or reports that are stored in embedded folders. Store the hierarchical name of the report in the button’s “Additional Info”, for instance: Monthly Reports/Sales By Article where “Monthly Reports” is the name of a folder.

Sub Open_Report_Button(e)
REM specify the hierarical name in the button's "Additional info" field
	sName = e.Source.Model.Tag
	doc = getDBDocument(e.Source)
	OpenEmbedded(doc, sName, bReport:=True)
End Sub

Sub Open_Form_Button(e)
REM specify the hierarical name in the button's "Additional info" field
	sName = e.Source.Model.Tag
	doc = getDBDocument(e.Source)
	OpenEmbedded(doc, sName, bReport:=False)
End Sub

Sub OpenEmbedded(odb, sHierachicalName$, bReport As Boolean)
	if bReport then
		container = odb.ReportDocuments
	else	
		container = odb.FormDocuments
	endif
	obj = container.getByHierarchicalName(sHierachicalName)
	obj.open()
End Sub

Function getDBDocument(src):
    oModel = src.getModel()
    ' a button's parent is always a form
    oForm = oModel.getParent()
    oActiveConnection = oForm.ActiveConnection
    oDataSource = oActiveConnection.getParent()
    getDBDocument = oDataSource.DatabaseDocument
End Function
#2

Have you tried omitting this part of the code?
Also no need to declare or define oReportdoc, unless it is a global variable. If it is a global object then

oReportdoc = thisDatabaseDocument.reportDocuments.getByName(“Job Schedule Report”)
oReportdoc.open()

Else just open the report with

  • thisDatabaseDocument.reportDocuments.getByName(“Job Schedule Report”).open()
    Here is an abbreviated version of the macro:
Sub Run_Report()
Dim oForm, oReportdoc
oForm = thisComponent.drawPage.forms.getByName("Customer_Form")
If oForm.isNew() then
oForm.insertRow()
Else
oForm.updateRow()
Endif
oReportdoc=thisDatabaseDocument.reportDocuments.getByName("Job Schedule Report")
oReportdoc.open()
End sub
#4

Sky,
I plugged in your macro instead of what I had.
Now when I run it, I get a Basic syntax error with “thisComponent.drawPage.forms.getByName(” highlighted.
Erik

#5

Replace the quotation marks with straight ones like " and don’t forget to do this also around Job Schedule Report

#6

Sorry… You have to remove the linebreak between “Scedule” and “Report”. It appears that way in the post only because I did not import the code, but merely typed it in the LO ask suggestion format.
The portion of code should actually look like

  • …getByName(“Job Schedule Report”) - No line break
#7

Sky,
My version doesn’t have a line break between “Schedule” and “Report”.
The run time error is at the line “thisComponent.drawPage.forms.getByName(”.
Any other possibilities?
Erik

#8

I implemented Wanderers suggestion - to replace the diagonal quotes with vertical quotes and am still getting an error at the same line, but it now the hole line highlighted as a problem. The highlighted line is " oForm = thisComponent.drawPage.forms.getByName(“Customer_Form”)"
Thanks, Erik

#9

Sky,
Actually now the error at that line says: “Property or method not found: drawPage.”
with “oForm = thisComponent.drawPage.forms.getByName(“Customer_Form”)” highlighted.
Erik

#10

Did you try my macro? You don’t need to understand the code. You just need to know how to use it.

#11

Wanderer,
Good advice. The quotes have gotten me before when I copy paste code.
Thanks, Erik

#12

Something is wrong with the name supplied for the actual form control. The form control name is indicated in the form navigator, not in the odb Forms list. To avoid this confusion, use the getByIndex() method instead of the getByName() method.

thisComponent.drawPage.forms.getByIndex(0)

This method avoids the confusion about which form name to use or quotations… and just calls the first control in the form drawpage, regardless of name. (nearly always the main form) Otherwise, verify the correct form control name while editing the form, selecting menu bar Form / Form navigator, then browse the list of form controls for the form in question, and its control name. (If it is the main form, it should be near the top of the list, under Form)

#13

Sky,
Done. That works, but I’m now getting a Variable not found error on the line: “oReportdoc = thisDatabaseDocument.reportDocuments.getByName(“Job_Schedule_Sheet”)”.
I tried substituting getByName in this line of code with getByIndex and get the same result. Thanks Erik
Here is the the macro for context:
Sub Run_Report()
Dim oForm as Object
Dim oReportdoc as Object
oForm = thisComponent.drawPage.forms.getByIndex(“Customer_Form”)
If oForm.isNew() then
oForm.insertRow()
Else
oForm.updateRow()
Endif
oReportdoc = thisDatabaseDocument.reportDocuments.getByName(“Job_Schedule_Sheet”)
oReportdoc.open()
End sub

#14

Sky,
I changed a few things to try to figure out the problem. The latest version of the macro is shown further below.
With this code, I am now receiving a different error “Property or method not found: drawPage.” at the same problematic line "oForm = thisComponent.drawPage.forms.getByName(“Customer_Form”).

Sub Run_Report()
Dim oForm as Object
Dim oReportdoc as Object
oForm = thisComponent.drawPage.forms.getByName(“Customer_Form”)
If oForm.isNew() then
oForm.insertRow()
Else
oForm.updateRow()
Endif
oReportdoc = thisDatabaseDocument.reportDocuments.getByName(“Job Schedule Report”)
oReportdoc.open()
End sub

#15

As per instruction, this should be

  • getByIndex(0)
#16

This is not a valid form control name. While editing the form, goto menu bar Form / Form Navigator and examine the control list for the correct form control name (“MainForm”??). The name is case sensitive so the form name in the macro must exactly match the form control name. Again, this is not the same name as is listed for the form in the Forms list.

#17

Sky,
The “getByIndex(0)” appears to work.
I’m now getting a Variable not defined error at the line " oReportdoc = thisDatabaseDocument.reportDocuments.getByName(“Job Schedule Report”)"
Erik

#18

Has oReportdoc been declared? - Dim oReportdoc
Try not even using oReportdoc and instead just use

thisDatabaseDocument.reportDocuments.getByName(“Exact Name”).open()

Verify that the report actually exists in the Reports list, and that the name in the macro is exactly as the actual report name.

To help debug try implementing this IF HasByName method in the macro code where you wish to open the report. (replace the last few lines of the macro where oReportdoc is used)

If thisDatabaseDocument.reportDocuments.hasByName("Exact Name") then
thisDatabaseDocument.reportDocuments.getByName("Exact Name").open()
Else
msgBox("A report with that exact name does not exist")
Endif

Of course, substitute your actual report name where I have “Exact Name”

#19

Contributors to this topic may wish to read this information about Markdown for displaying code.


Using blockquote can introduce discrepancies in the way the code is displayed, including converting quotes to ‘smart’ quotes and interpreting some Markdown formatting characters such as the asterisk. For example, this code in blockquote:

SELECT WRT FROM “MYTABLE” WHERE “ID” <> ‘Text’
If thisDatabaseDocument.reportDocuments.hasByName(“Exact Name”) then
thisDatabaseDocument.reportDocuments.getByName(“Exact Name”).open()
Else
msgBox(“A report with that exact name does not exist”)
Endif

would render as below using backticks ```

SELECT *WRT* FROM "MYTABLE" WHERE "ID" <> 'Text'

If thisDatabaseDocument.reportDocuments.hasByName(“Exact Name”) then
thisDatabaseDocument.reportDocuments.getByName(“Exact Name”).open()
  Else
    msgBox(“A report with that exact name does not exist”)
    Endif

Note the way quotes are displayed, lines not wrapped and spacing preserved, to mention just a few differences.

2 Likes
#20

Thanks for the info… I was wandering how this was done but never made time to search it out! Thought I could get by with just blockquotes. I will be using the backticks from now on!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.