Keep a Switchboard type Form from closing automatically

[JohnB47]

Thanks for that Villeroy. Very interesting.

[Villeroy]

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].

[JohnB47]

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.

[Villeroy]

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”:

• The filtering form refers to one distinct record in a filter table. SELECT * FROM "FLT" WHERE "ID"=0
• The filtering form allows for modification only, otherwise the user may tab into another record where the entered criteria may affect some other form or report.
• The subform query can be as complex as needed and as simple as simple as sufficient. Simple subforms map master fields and slave fields. In this sample I map the subform’s query parameters :pD1, pD2, pINT1 and :pTXT1 to their corresponding master fields D1, D2, INT1 and TXT1, so the query can handle missing values, <=, >= and pattern matching. See query “Filtered_Form” which is

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.

[JohnB47]

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.

[JohnB47]

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.

[Villeroy]

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.

[JohnB47]

Thank you. Got that.