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?
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.
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.
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.
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?
@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.
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.
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.
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.
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…
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.
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)
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.
Thanks for that Villeroy. Very interesting.
Instead of a switch board, macros and stuff, you can store input forms as stand-alone Writer documents and use ordinary desktop links, hyperlinks or hyperlink buttons to open them.
Install this little macro suite of mine, open your database document, navigate to Tools>Macros>My_Macros>FreeHSQL>FreeForms and run the “Main” routine. The forms will be extracted to the same directory as the database document under the same name as the embedded form with .odt extension. The macro re-connects all forms to the database document and stores the form documents in read-only mode with no password, so you can modify form data but not the document itself. In order to modify the document itself, call menu:Edit>Edit Mode [Ctrl+Shift+M].
Hi again Villeroy. I’ve been inspecting your Expense.odb just to see how it works and I’ve got one problem and one question.
Firstly, the problem. On the ExpenseForm, the Date fields don’t seem to work properly. When I select a date in one, then the other, both refresh with the same date (I have entered a few records in my copy of the db with various dates). Please see the following screenshots showing the controls for those fields. I would fix the lower date field myself (change it’s name to D2) but when I click on … for the lower ones Label Field control, I get a curious dialogue called Label Field Selection which doesn’t include ‘Until date (D2) Label’, which I think should be there.
So, is this something that needs to be fixed, how is that done and where does that information in the Dialogue ‘Label Field selection’ come from? Is that something you constructed yourself and if so, how? Where does it live? Thanks.
Pick “Until date (D1”). I think, I’ve made the second date by copying of the first which is why both date fileds are named “D1” and their labels “D1 label”. It does not matter anyway as long as you do not access these controls by macro code (which I strictly avoid anyway).
Important facts about “power filter forms”:
SELECT * FROM "FLT" WHERE "ID"=0
SELECT * FROM "EXP" WHERE
( :pD1 IS NULL OR "D" >= :pD1 ) AND
( :pD2 IS NULL OR "D" <= :pD2 ) AND
( :pINT1 IS NULL OR "TID" <= :pINT1 )
AND ( :pTXT1 IS NULL OR "DESCR" LIKE '%' || :pTXT1 || '%' )
P.S. I forgot to add the filtered report. A query for a report may look like this:
SELECT "EXP".*, "TYPES"."DESCR" AS "Type" FROM "EXP", "TYPES", "FLT" WHERE
"EXP"."TID" = "TYPES"."ID" AND
"FLT"."ID" = 0 AND
( "FLT"."D1" IS NULL OR "D" >= "FLT"."D1" ) AND
( "FLT"."D2" IS NULL OR "D" <= "FLT"."D2" ) AND
( "FLT"."INT1" IS NULL OR "TID" <= "FLT"."INT1" ) AND
( "FLT"."TXT1" IS NULL OR "DESCR" LIKE '%' || "FLT"."TXT1" || '%' )
The report query does not need to be editable, so it merges all tables to show the type descriptions (TYPE.DESCR) and I refer to the filter table record directly without parameters.
In the input form, the type descriptions are displayed by the listboxes. The form is editable because it starts with SELECT * FROM "EXP" A record set from one table including the primary key is editable. If the FROM clause includes many tables, the record set is read-only in most cases.
Thanks. I changed it to “Until date (D1 Label”) even though your code, elsewhere, seems to refer to that field as D2. The initial problem still exists - if I select a From Date, then an Until Date, they both jump to show the same date.
I would still like to know about that little dialogue box called ‘Label Field Selection’. How do the entries in that box get created and/or modified? Is it something you created? I ask because I think the solution is to set the second Date fields name to D2 and set it’s Label Field entry to ‘Until Date (D2) Label’ rather than ‘Until Date (D1) Label’ . Thanks again.
Never mind, I’ve sorted this. I was getting hung up on the Label Field thing (which I don’t understand). All I needed to do was open the control properties of the Until Date field and change it’s Data Field from D1 to D2. I also changed it’s label to D2 label. Works fine now.
I’m still wondering about that Label Field Selection dialogue (which changed to reflect my changing the label to D2). What’s the purpose of that? Thanks.
The labels not that unimportant. The association between label and control allows you to define shortcuts.
Change the label to “~From Date” and it will appear as “From Date” with underlined F. Alt+F focusses the control which is linked to the label. The tab order is more important. And more than that, the binding is important. I copied the first label and date control, changed the label of the copied one but forgot all the rest. Both date controls are linked to field D1 which makes them synchronized.
I’ll replace my upload with a repaired version having a proper report query and fixed filter form.