Standalone form with macro

[jodybingo]

I have made a standalone “switchboard” with buttons to launch the individual forms in DB (hsqldb 6.4.2) and they work great.
1- I have a few buttons with macros assigned to them to launch the various reports I have. When I launch each report manually within the LO db they work fine. When I use the switchboard form within LO the User Parameter dialog box I put in my queries appears, I enter the data (a date range as an example) then it hangs. When I use the button for my reports on the standalone form I get an error.
2- LO is recommending I migrate to Firebird. If I accept, will I have to redo anything in my forms or queries?

Here is the macro I have

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


Sub OpenBDayEmail '(oEvent As Object)'  
    RptName = "Birthday_Email"  
    ThisDatabaseDocument.ReportDocuments.getByName(RptName).open  
End Sub

Sub OpenCorpOrder '(oEvent As Object)'  
    RptName = "Corp_Order_Form"  
    ThisDatabaseDocument.ReportDocuments.getByName(RptName).open  
End Sub

Sub OpenEvent_Invoice '(oEvent As Object)'  
    RptName = "Event_Invoice"  
    ThisDatabaseDocument.ReportDocuments.getByName(RptName).open  
End Sub

Sub OpenParty_Order_Form '(oEvent As Object)'  
    RptName = "Party_Order_Form"  
    ThisDatabaseDocument.ReportDocuments.getByName(RptName).open  
End Sub

Sub OpenResto_Report '(oEvent As Object)'  
    RptName = "Resto_Report"  
    ThisDatabaseDocument.ReportDocuments.getByName(RptName).open  
End Sub

Sub OpenTheme_Email '(oEvent As Object)'  
    RptName = "Theme_Email"  
    ThisDatabaseDocument.ReportDocuments.getByName(RptName).open  
End Sub

Thanks to anyone who can pitch in

[jodybingo]

This is the line in each macro that is returning the error
ThisDatabaseDocument.ReportDocuments.getByName(RptName).open

[EasyTrieve]

Use ‘code’ to format your code. Also add two spaces after lines to cause them to break. And also just to keep this site happy, adding ’ (tick) before AND after comments, makes them appear correctly.

[Ratslinger]

Hello,

The message to migrate is because under Advanced (from Tools->Options) Experimental features is turned on.

Have just re-tested using LO v6.2.0.3 and still works. See answer by @Kridtbandit → how to open Base reports based on queries in standalone.

Edit:

Another alternative to the switchboard is in the answer by @doug on this post → How do I ensure that the LO GUI is minimised on startup. Have used this in past and preferred it over switchboard.

Edit #2:

Unfortunately this is seen too often. People want to use macros but know little about them. The routine works with the exception of two line which are not needed and noted in my comments. Also a bit confusing may be that FormName is being passed as a parameter. That is the Report Name. Add to that confusion all the duplication you have in your code.

First you only need one SUB. oEvent can be read to determine which button was pushed and therefore execute the appropriate report. Make sure each push button you use has a unique name such as pbReportOne, pbReportTwo etc.

Then each button is attached to the same routine:

Sub PrintReport(oEvent)
Rem Get name of Control
   sControlName = oEvent.source.Model.Name

Rem Check for button pushed
   If sControlName = "pbReportOne" Then
       ReportName = "Birthday_Email"
   EndIf
   If sControlName = "pbReportTwo" Then
       ReportName = "Corp_Order_Form" 
   EndIf
Rem add more If Statements as needed or even better a CASE statement.
   oContext = CreateUnoService("com.sun.star.sdb.DatabaseContext") 
Rem set registered DB name here 
   oFont = oContext.getRegisteredObject("YOUR_REGISTERED_BASE_NAME_HERE")
   dbForms = oFont.DatabaseDocument.ReportDocuments 
   oAConnection = oFont.getConnection("","") 
   Dim pProp(1) As New com.sun.star.beans.PropertyValue 
   pProp(0).Name = "ActiveConnection" 
   pProp(0).Value = oAConnection 
   pProp(1).Name = "OpenMode" 
   pProp(1).Value = "open" 
   oForm = dbForms.loadComponentFromURL(ReportName, "_blank", 0, pProp()) 
End Sub

Tested this as is (with exception of my registered DB & my report names) and works without issues.

You really need to learn more about macros if you are going to continue using them. There are a lot of samples available but you cannot just copy & paste them and expect them to work. It takes time and experience to write macros in LO - using any language.

[jodybingo]

So far, no success anywhere. I’m gonna head to bed and try again after some sleep

[Ratslinger]

@jodybingo,

Please see Edit #2 in answer.

[jodybingo]

@Ratslinger wrote:
//You really need to learn more about macros if you are going to continue using them. There are a lot of samples available but you cannot just copy & paste them and expect them to work. It takes time and experience to write macros in LO - using any language.//

You are absolutely right. i know nothing about Macros and only started fiddling around recently with them and got in a bit over my head with this project and the little amount of time I had to get it prepared. I am very grateful for the help you have provided here.
In your opinion, where would be the best source for learning macros (for noobs)?

Oh, as for the earlier question of migrating to Firebird embedded, should I or should I leave it in HDSQLDB? And if I do migrate, is there any danger to data I have already entered (or the macros you provided)?

Thanks,
Jody S

[Ratslinger]

For more info/links to Base and Macro coding, see this post → To learn LibreOffice Base are there introductions or tutorials?. Note that if you don’t understand a lot about Base itself, macros can waste time. There are many things you can do in Base and SQL which may eliminate the need for a macro. Understand Base and SQL first.

As for which database? HSQLDB embedded v1.8 is VERY old and thus Firebird embedded. There are issues still to be worked out. It is decent but you need some experience to work around the shortcomings. You can also use a newer version of HSQLDB with a split database - data is separated from the .odb of Base. See this post for more → Create a new ‘split’ HSQL 2.x database.

There is much, much more. Another problem here is that I have no idea what you are doing and its use.

[MicahSpecial]

is there link about removing editing tools or toolbars for external form odt file?

[Ratslinger]

@MicahSpecial,

This should be a new question. Please see → Macro to remove all menus and toolbars except for the toolbar I created for my document