[Solved] LibreOffice Base Macros

Creating a macro - Writing a Script - Using the API

[Solved] LibreOffice Base Macros

Postby BasilVSA » Tue Jun 18, 2019 4:47 pm

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

Re: LibreOffice Base Macros

Postby Villeroy » Tue Jun 18, 2019 6:15 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26971
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Base Macros

Postby BasilVSA » Tue Jun 18, 2019 8:15 pm

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

Re: LibreOffice Base Macros

Postby Villeroy » Wed Jun 19, 2019 7:47 am

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26971
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Base Macros

Postby Villeroy » Wed Jun 19, 2019 8:30 am

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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26971
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: LibreOffice Base Macros [Solved]

Postby BasilVSA » Wed Jun 19, 2019 1:59 pm

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

Re: LibreOffice Base Macros

Postby JPL » Wed Jun 19, 2019 4:33 pm

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
Windows 7 / LibO 6.0 / AOO 4.1
Ubuntu 18.04 / LibO 6.1
Access2Base extension (AOO) or standard (LibO).
BaseDocumenter extension (LibO)
Documentation on http://www.access2base.com and http://www.access2base.com/basedocumenter
JPL
Volunteer
 
Posts: 103
Joined: Fri Mar 30, 2012 3:14 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests