Keep a Switchboard type Form from closing automatically

JohnB47 · August 21, 2023, 1:51pm

My system info is :
Version: 7.5.5.2 / LibreOffice Community
Build: caf8fe7424262805f223b9a233
Environment: CPU Threads: 4;OS: Windows 10.0 Build 19405
User Interface: UI render: Skia/Raster; VCL:win
Locale: en.GB (en_GB); UI: en-GB
Misc Calc: threaded

I have created a simple Switchboard form which I am slowly populating with Command Buttons. Each button will open a form, report or query.

I’m finding that when I open, say, a report from a button on the Switchboard form, and then close that report, the Switchboard form has also closed.

How can I get the Switchboard form to stay open until I close it myself?

Thanks.

sokol92 · August 21, 2023, 2:12pm

It will be much easier to answer if you upload a simple example (.odb file).

RobertG · August 21, 2023, 2:25pm

@JohnB47 : Which code do you use to open the report or another form?

JohnB47 · August 21, 2023, 2:39pm

This code, in a Macro, called up on Buttons ‘Execute action’:

Sub OpenReport_1
const sNewDocumentName=“rptSpend_Summary_2023_Parameter”
oNewReportDocument=ThisDatabaseDocument.ReportDocuments.getbyname(sNewDocumentName).open
End Sub

JohnB47 · August 21, 2023, 3:10pm

Just noticed that if I use a command button (on the same switchboard form) to open a Form using the following Macro code, the Switchboard form doesn’t close after closing the form:

Sub OpenForm_1
const sNewDocumentName=“frmSpend_Summary_2023_Parameter_Table_View”
oNewFormDocument=ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
End Sub

RobertG · August 21, 2023, 5:12pm

Don’t know if it makes a difference, but

Why do you define sNewDocumentName as const?
Why do you create a new object variable when opening form or report?

Sub OpenReport_1
 ThisDatabaseDocument.ReportDocuments.getbyname("rptSpend_Summary_2023_Parameter").open
End Sub

This would do all you want. And if you start different reports by different buttons you could sve the name of the report in the tag of the button:

Sub OpenReport(oEvent AS OBJECT)
 stTag = oEvent.Source.Model.Tag
 ThisDatabaseDocument.ReportDocuments.getbyname(stTag).open
End Sub

JohnB47 · August 21, 2023, 6:42pm

Thank you. I got the code suggestion from this video. If you go to point 6:40 on the timeline you’ll see the code. I have very little knowledge about code. I just beg, borrow, copy and modify it.

As to your suggestion, so I simply attach a macro with your code to the Execute action of a command button? However, I can’t see how the code knows what Form or Report to open.

Do you mean your code depends on the name of the report being saved in the tag of the button? If so, what do you mean by tag? I can’t see that word used in the Properties of a Button.

Thanks for the help with this.

JohnB47 · August 21, 2023, 7:24pm

OK, I got the code running by assigning the following to a button:

Sub OpenReport(oEvent AS OBJECT)
stTag = oEvent.Source.Model.Tag
ThisDatabaseDocument.ReportDocuments.getbyname(“rptSpend_Summary_2023_Parameter”).open
End Sub

Where I have put the name of the Report in the code. I’m still wondering what you meant by “And if you start different reports by different buttons you could sve the name of the report in the tag of the button”.

Could you expand on that please?

RobertG · August 21, 2023, 7:31pm

Open the properties of the button. General properties → Additional Information. Add the name of the form (or report) here. This is oEvent.Source.Model.Tag.

JohnB47 · August 21, 2023, 8:39pm

Excellent. I’ve got that working now. Presumably to open a Form the word Report would have to be changed to Form and the code would be:

Sub OpenForm(oEvent AS OBJECT)
stTag = oEvent.Source.Model.Tag
ThisDatabaseDocument.FormDocuments.getbyname(stTag).open
End Sub

If that’s correct I will add this code to my command button opening a Form now and will report back. Thanks.

RobertG · August 22, 2023, 9:21am

Code will be correct. Could also be expanded to write down “Form” or “Report” also in “Additional Information”. So you will need only one procedure for reports and forms. But then you have to split the tag and work with an array - a little bit complicated for starting with macros.

JohnB47 · August 23, 2023, 8:33am

Yes, sounds too complicated for me. Thanks for the help here. I’m still planning to put together an example database to illustrate my initial point. If my ‘problem’ is evident in that, perhaps I could make a suggestion for improvement to the developers?

RobertG · August 23, 2023, 8:41am

@JohnB47 : Should run without problems. Upload the file (without personal data) if you get buggy behavior. I have created many databases with forms where I could start the report from inside the form. But I don’t work with a switchboard. Open a default form when starting Base and switch from one form to another by choosing the form in a list box.

JohnB47 · August 23, 2023, 3:50pm

OK, I’ve done some more digging and I’ve discovered that the problem lies with a Parameter type query. Let me explain again. I’ll give the objects simple names to keep things clear.

If I open Form2, using a Command Button on Form1 and Form2 is fed with data from a Parameter type query, all is well. By that I mean when the Form2 is closed, Form1 (with the Command button) reappears. I believe this is as it should be.

However, if I open Report1, using a Command Button on Form1 and Report1 is fed with data from a Parameter type query, all is not well. By that I mean when the Report1 is closed, Form1 (with the Command button) has been closed.

Only if I remove the Parameter requirement from the query feeding the Report, does Form1 reappear when the Report is closed.

Surely this is not as intended?

How should I proceed? Thanks.

Edit: I should explain that the term Switchboard Form, for me, describes a simple ordinary Form, populated with a range of Command Buttons. Each Button opening a Form or a Report. Even a query. Nothing complicated.

RobertG · August 23, 2023, 4:43pm

I don’t use parameterized queries. I know who it works, but a query, which reads the specific values from another table ( I call this “tbl_Filter” here) with only one row, works well. I fill this table in the form with the parameters I need for a report and start the report. Nothing else needed like separate input boxes for a parameter.

JohnB47 · August 23, 2023, 8:19pm

Thank you. Sorry. I don’t understand. Is there something I should be seeing here? Have you attached some sort of database example I should be seeing? What do you mean by “I call this “tbl_Filter” here”? Were exactly? I’m happy that you are suggesting an alternative and I really want to understand how to use that but what about the problem I’m seeing? Is that not something that the developers should be aware of? Unless, of course, I’m doing something wrong, or I’m expecting something unrealistic. Thanks again.

RobertG · August 24, 2023, 5:52am

I haven’t added an example, but her is one:
Beispiel_Bericht_Rechnung_Firebird.odb (284.2 KB)
Open the form. There is a button, which starts a report. Content of the report depends on the content of field “ID”.
Have a look at the view “Bericht_Ware_gruppiert”. It contains the code

…WHERE "Rechnung_ID" = 
COALESCE ( ( SELECT "Integer" FROM "Filter" WHERE "ID" = TRUE ), "Rechnung_ID" )…

So if you change the value “Integer” in table “Filter” the report will be executed for this “Integer”. If there isn’t a value “integer” it will be executed for all data.

Upload your file and I will have a look to include a Filter-table…

JohnB47 · August 24, 2023, 8:02am

Thank you for taking the time to do that. Please leave it with me. I’ll have a good look at it and will get back to you. Thanks again.

JohnB47 · August 24, 2023, 6:56pm

Well I’ve had a look at your database and I think it’s marvellous how you combine dropdown or combo boxes to allow records to be filtered and supplied to a report. I thought I could modify it to my relatively simple needs but I think It would be too much for me. So I’ve decided to take you up on your kind offer. Please find my database attached.

It is quite simple, a way of tracking my Credit Card spend. I decided to do this when I realised that my Credit Card company supplies monthly statements as CSV files. I would probably need to filter only by Month and Year, although by Type might be useful too.

The records in tblSpend are for 2023 and months February, March and April only.

Also, I populate the Type fields in tblSpend by running Update SQL that I store externally. This is because Base doesn’t allow Update queries.

Thanks for your help with this. I hope it isn’t too much work. Oh and I’ve changed all the spend entries, so there’s nothing personal stored.
Spend_Sample.odb (35.0 KB)

Villeroy · August 24, 2023, 9:11pm

Expense.odb (25.0 KB) [second version]

Enter your category descriptions into the types table.
There is a form for new entries (type and tab).
The other form is for editing and filtering existing entries. The sum/min/max/average of the filtered data are displayed. Default sort order is chronological with latest on top.