Page 1 of 1

[Solved] LibreOffice Base Macros

PostPosted: Tue Jun 18, 2019 4:47 pm
by BasilVSA
I'm very new to Libreoffice Base but I need to convert from MS Access to Base so that my colleague on a Macbook can use our database. I am struggling to understand how to refresh a form once I have selected a value in a list box to display all the remaining fields on the form. e.g. I select a record number from my input table via a list box I have inserted on the form and all I want is the remaining associated fields of that record to be displayed on the form (not a subform). This is for updating the records as/when required.

I am finding the Libreoffice macros very difficult and confusing versus the MS Access macros.

Thanks in advance for any assistance from a real newbie.

Re: LibreOffice Base Macros

PostPosted: Tue Jun 18, 2019 6:15 pm
by Villeroy

Re: LibreOffice Base Macros

PostPosted: Tue Jun 18, 2019 8:15 pm
by BasilVSA
Hi Villeroy, I have tried what you suggested but I can't seem to get anything to work. I'm not using a subform at all. All I want is all the data from a single record as along as it corresponds with the ListBox entry I have selected. I have tried a ListBox with a push button (refreshform action) but none of the fields update on the form and on top of it all every time I try to get a record it wants to update the record.

Very frustrating not being able to simply select a recordID and then populate the form. What I did in MS Access was select the recordID and on the Listbox AfterUpdate option using a macro got the form to refresh WHERE the table recordID = recordID of the Listbox value.

How difficult is it to do this in a LibreOffice macro?

Re: LibreOffice Base Macros

PostPosted: Wed Jun 19, 2019 7:47 am
by Villeroy
My example file does use a subform. The main form's source is a single dummy record in a fiter table taking the filter criterion.
Assuming that your field to lookup is integer, menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Filter" (INT1 INTEGER, ID INTEGER PRIMARY KEY);
INSERT INTO "Filter" VALUES ( NULL, 0);

menu:View>Refresh Tables
Now you have a single record with row ID 0 in a table named "Filter".
Create a form with source type SQL and source
Code: Select all   Expand viewCollapse view
SELECT * FROM "Filter" WHERE ID=0

Disallow deletion and insertion, hide any navigation toolbar, allow modification only.
Add a list box to the form with SQL source:
Code: Select all   Expand viewCollapse view
SELECT "whatever" FROM "some table" ORDER BY "whatever" ASC

Set its bound field property to 0 which means the first and only field. You may also choose source type "Query", save the statement in a query and specify the query name as list box source.
Create a subform linked to your data table with master field INT1 and your integer data field as slave field.
Add a grid control with your data table and a refresh button to the subform.

Re: LibreOffice Base Macros

PostPosted: Wed Jun 19, 2019 8:30 am
by Villeroy
This is the built-in feature:
Create a form with your data table as source.
Bind the ID field to a text box. Set property "Filter proposal".
When using the form, push the "Form Filter" button on the form navigation toolbar.
The form switches to another mode where you enter filter criteria. The text box turns into a list box. Another small toolbar replaces the navigation toolbar.
Use the list box and switch back to normal data entry mode.

Re: LibreOffice Base Macros [Solved]

PostPosted: Wed Jun 19, 2019 1:59 pm
by BasilVSA
Thanks for the help Villeroy, the last option you suggested works perfectly for my needs.

Re: LibreOffice Base Macros

PostPosted: Wed Jun 19, 2019 4:33 pm
by JPL
Another suggestion is to use the Access2Base library, which is a standard feature of LibreOffice.
The VBA code that you used in MSAccess can be reused as such (or with minimal change) in LibreOffice.

See http://www.access2base.com/access2base.html (and particularly the Requery method) for more info.

JPL