[Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes

Post by Villeroy »

Hands-on example for testing
Open movies_5.odb with 2 forms. Both forms reflect a many-to-many relation between movies and genres. One form lets you assign movies to genres, the other lets you assign genres to movies. No macro involved. Just an ordinary many-to-many relation, reflected in both directions by 2 Base forms.
This is the database relation between movies and genres
This is the database relation between movies and genres
This is the form hierarchy between the genres in main form, movies in a subform and an additional form to add new movies.
This is the form hierarchy between the genres in main form, movies in a subform and an additional form to add new movies.
GenreMovies_FormNavo.png (17.67 KiB) Viewed 9876 times
Both forms have an additional form to add new genres or movies respectively.
After you have saved a genre name, you can go back to the grid of genres belonging to the current movie, refresh the list box in that grid and then assign the newly inserted genre to that move.
In the other form: After you saved a full set of movie attributes, you can go back to the grid of movies belonging to the current genre, refresh the list box in that grid and then assign the newly inserted movie to that genre.
The navigation toolbar provides a second refresh button when a combo box or list box is focussed. This button allows you to refresh only the focussed list/combo box without reloading the entire form.

How to refresh the list boxes of genres/movies automatically?

1. Open attached Writer document, save it in a "trusted directory" (according to the macro security settings) and hit the install button to install the Python code to subfolder Scripts/python/pyDBA/AutoRefresh.py within your profile folder.

2. Use the form navigator to add a new hidden control named "AutoRefresh" (case sensitive) to the "NewMovie" form (or to the "NewGenre" respectively) and add the following hierarchical path to the item you want to refresh, so the value reads ../MainForm/SubForm/SubForm_Grid/MovieID (or with trailing /GenreID if you are editing the other form).
From its containing form, the path goes one level up (..) to the parent container "Forms", then down to the MainForm/SubForm/SubForm_Grid and the list box named "MovieID" or "GenreID respectively.

3. Assign the above macro [MyMacros > pyDBA > AutoRefresh > formAction] to the form's event "After Record Action". Do not assign the code to any form control's event!

4. Save and reload the form document.

How does it work?
The little macro is triggered by form event "After Record Action". A form's record action is either one of insert, delete or update (in other words adding, removing, editing of a whole record). In this particular example it is row insertion since this is the only action provided by the "NewGenre"/NewMovie" forms.
When this has happened, the macro reads the text value from the named control "AutoRefresh", navigates from the containing form to the form control that is specified by the hierarchical path name and refreshes that item.
Without any modifications to the Python code, this tiny macro is able to refresh forms, combo boxes and list boxes. The same form event can refresh multiple items. Just separate their path names with semicolons. All you need is a form event, a hidden control "AutoRefresh" (case sensitive) with one or more path names (case sensitive) pointing to one or more refreshable items (forms, combo/list boxes).


Caveat
There is barely any error handling. You get plain Python errors as message boxes.
no-such-element errors when an addressed item does not exist
attribute errors when you use control events instead of form events or when the addressed item is not refreshable (not a form, combo, list box).
Attachments
AutoRefresh.odt
Installer for <profile folder>Scripts/python/pyDBA/AutoRefresh.py
(20.61 KiB) Downloaded 1178 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base, Python] Tiny Macro Refreshing Forms, List/Combo B

Post by Villeroy »

Having a "power filter" form and a filtered subform with a refresh button, you fill out the filter criteria and then click the refresh button which belongs logically to the subform. When the button gets the focus, the filter form is stored automatically because the button belongs to another form. Then the button refreshes the subform where it belongs to. Any modified form record is stored when you move to another record, move to another form or when you hit some button to save the record. This works without any macro being involved.
----------------------
Some users want to change this elegant solution because it requires Ctrl+Tab or a mouse click to change the focus from the filtering form to the refreshing subform button.

In order to change the behaviour of such power filter form, so a simple hit on the Enter key refreshes the subform:
1) Move the refresh button from the filtered subform to the filtering parent form and change its action property from "refresh form" to "save record". Set its "Default Button" property to "Yes" (trigger this button by Enter key).
2) Apply AutoRefresh.py to the filtering parent form. Add a hidden field named "AutoRefresh" with the name of the subform and assign the macro to the same form's record action event.

Now you trigger the default button when you hit the Enter key while the filter form has the focus (*). The button saves the current record.
This saving of a record triggers the form's record action and calls the macro. The macro reads the list of items to be refreshed from the control named "AutoRefresh" and performs the refresh actions.
The same macro would also be triggered when you save the record any other way or when you delete a record or when you insert a new record. Deletion and insertion should be disallowed for any "power filter" form because losing the referenced power filter row is not wanted in this special use case.

* not counting the Enter which selects a listbox item and not counting the Enter which starts a new line in a multi-line text field
Attachments
FilterData.odb
The well known "Filter Form" with and without AutoRefresh
(122.42 KiB) Downloaded 535 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes

Post by Villeroy »

A most simple example involving a combo box suggesting already entered item names.
The combo box is updated when you store a record, delete a record or add a new record. When you remove the only instance of a name, it vanishes from the combo box. When you edit or add a not yet existing item, it will be added to the box. When you modify the only instance of a name it will be changed in the box.

Download AutoRefresh.odt and install the macro.
Store the attached AutoRefresh_Combo_Box.odb to a trusted directory.


My Cascading list boxes with and without macros makes use of the exact same macro.
Attachments
AutoRefresh_Combo_Box.odb
(56.75 KiB) Downloaded 104 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply