[Solved] LibreOffice Base Macros

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
BasilVSA
Posts: 10
Joined: Tue Jun 18, 2019 4:34 pm

[Solved] LibreOffice Base Macros

Post 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.
Last edited by robleyd on Thu Jun 20, 2019 2:27 am, edited 1 time in total.
Reason: Tagged [Solved]
I am using LibreOffice 6.2.4.2 on both Windows 10 and Linux Mint 19.1 Cinnamon. I also want my colleague to be able use the Base database on his latest Macbook
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Base Macros

Post by Villeroy »

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
BasilVSA
Posts: 10
Joined: Tue Jun 18, 2019 4:34 pm

Re: LibreOffice Base Macros

Post 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?
I am using LibreOffice 6.2.4.2 on both Windows 10 and Linux Mint 19.1 Cinnamon. I also want my colleague to be able use the Base database on his latest Macbook
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Base Macros

Post 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

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

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

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.
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: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Base Macros

Post 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.
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
BasilVSA
Posts: 10
Joined: Tue Jun 18, 2019 4:34 pm

Re: LibreOffice Base Macros [Solved]

Post by BasilVSA »

Thanks for the help Villeroy, the last option you suggested works perfectly for my needs.
I am using LibreOffice 6.2.4.2 on both Windows 10 and Linux Mint 19.1 Cinnamon. I also want my colleague to be able use the Base database on his latest Macbook
JPL
Volunteer
Posts: 130
Joined: Fri Mar 30, 2012 3:14 pm

Re: LibreOffice Base Macros

Post 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
Kubuntu 22.04 / LibO 7.5
Access2Base (LibO).
BaseDocumenter extension (LibO)
ScriptForge (LibO)
Documentation on https://help.libreoffice.org/latest/en- ... bPAR=BASIC
Post Reply