Create A Form and SubForm In LibreOffice Base

The following video and article will show you how to create a form and subform in LibreOffice Base (or Open Office base) without needing any code, enjoy.

After launching LibreOffice Base, choose Forms then "Create Form in Design View…".

LibreOffice will then launch a Form document, on that form document got to "View" then "Toolbars" and expand that menu item to reveal another menu, on that menu ensure that "Form Controls" and "Form Design" are selected, you may also want "Standard" selected, but probably no other ones as your interface could get too busy, making it difficult to find controls.

Next, it’s time to build the form so…

1. Click on the "Form Navigator"
2. When the "Form Navigator" appears then right click
3. and choose "New" and "Form"

The paradigm we’re working with here is slightly non intuitive in my humble opinion, you could actually think of a "form" more as a "Data Connector" especially if you’re coming from the Microsoft side of things and are used to Access Forms.

This is because there’s the canvas that you’re putting controls onto (In Microsoft Access or Excel this would be "The Form"), and then the data hierarchy that controls those controls, which is managed by the Form Navigator.

You can add multiple "forms" via the Form Navigator (again think data controls) these forms (data controls) are bound to a Data Source and you then add controls to these forms.

What does this look like in practice?

You can see from the image above that the form navigator now has two forms (data controls) added (frmYears and frmVideos).

The first one frmYears is selected, I right clicked on that to get the "Form Properties" to it’s right (image above).

The "Content Type" drop down in Form Properties has three choices "Table", "Query" or "Sql Command", I chose "Query" and used "qryYears", which is the third element visible in the picture above.

In the above image the second datasource is being set for frmVideo, in this instance we’re using a content type of "Table" and using tblVideoPosts.

So right now we’ve created two datasources however the form canvas is still blank as no controls have been put on the form yet.

Let’s do that right now…

The way the controls work is as follows, whichever form i.e. datasource is selected when you draw a control on the canvas, then the control is bound to that form element (datasource).

So let’s add what I would call a Grid Control, in LibreOffice it’s a Table Control.

1. Click on Table Control in the "Form Controls" toolbar (toolbars are not always located in the same area so have a look around your screen). If you’re having trouble with this refer back to the beginning of this article where I talk about setting up your toolbars correctly.
2. Then draw the form control by dragging a rectangle to whatever size you want.
3. Here I’ve anchored it "To Page" rather than character or paragraph etc, this make the movement of the controls behave more like "normal" forms.

Here you can see that after changing the Anchor method Of the Table control, I was able to reposition it using my keyboard.

To populate the grid Right Click over the top of the Grid and you’re presented with various controls that will be data bound to whatever source frmYears in this instance is bound to, in this case qryYears giving a unique list of Years.

Given that years above is a numeric field, you can see above "Decimal accuracy" is circled, this is because the default is two decimal places, here I’ve changed it to zero so that the year 2021 does not show up as 2021.00.

Now we’re going to add the video posts view to the form, this is going to give us the ability to click on a given year and see how many videos were created in that year.

Unlike in the example above this time we’re going to use the form control wizard to add the second grid to the form.

First ensure the form control wizard is selected (leftmost circle below), then click on the Table Control on the Form Controls toolbar below.

Draw the grid on the form, then the table control wizard will activate.

Here I’ve moved all the fields to the right with a single click using ==>>.

Then moved the ID column back to the left using <-, so that the "ID" column doesn't display on the grid.

Having added the fields that we want in the wizard, we need to remove decimal places from the Year, Month and Day fields by setting "Decimal accuracy" to 0.

At this point, you can test your form by clicking on the design mode protractor to toggle the form to live view.

All the data should be coming through, but the form isn’t yet ready to "go live" as I want the detail grid on the right to be filtered based on the year grid on the left

Again click the protractor to go back into design mode, it’s now time to link the two grids together.

In order for the data linkages to work, the frmVideos data control (form) must be a subform on the Form Navigator of the frmYears control, so I now need to make that change (this is done by dragging frmVideos into frmYears) look at the image below.

That’s it, all you need to do now is save your form and it’s good to go, click on any year on the left grid and the grid to the right will change.