Today we will look at using dialog boxes when editing data in the LibreOffice Base (OpenOffice Base). As we found out in previous posts, the LibreOffice Base Form (OpenOffice Base) is actually an instance of the text editor LibreOffice (OpenOffice) Writer. For the organization of the window interface inside the Form, Dialogs are used - which are conveniently created in the built-in visual interface, but do not have support for communicating with database tables. This connection will be organized by macros OO Basic.
Let's start in order. Create two tables in the database:
products
- id (integer, primary key)
- name (string)
orders
- id (integer, primary key)
- productId (integer)
- count (integer)
- date (date data type)
')
Create the orders form, add a Form element with the name orders to the form. And also Table Control with the orders data source. How to do this was discussed in
Part 2 of the series.
When setting the date column properties, please pay attention to the properties marked in the figure with arrows that allow you to display data in the required format and use the drop-down calendar to select a specific date.
Now, right in the table, you can add and modify data. Up to a certain point, this is convenient, similar to the usual work of a client with a tabular processor. But at some point, problems begin that are also characteristic of working with tabular processors. Data can be changed randomly without even noticing it. And noticing it is impossible to roll back everything as it was. There is little space for new speakers, and unpleasant horizontal scrolling can occur. The column headings also have a size limit (otherwise they will not fit horizontally), and this does not allow for a detailed description of the data.
As a half-measure, you can place usual data entry fields next to the table (Text Control, Numeric Control, etc.). As the data source, set the appropriate fields in the orders table and everything will work. When you navigate through the data set, the data in the input fields will change. If you change the data in the input fields, the data in the database table will change. But this solution will not look very nice.
Therefore, we implement such functionality using Dialogs. Dialogs have one major limitation. Dialog input fields are not associated with a database table. Therefore, filling in the input fields of the Dialogue from the database table, and saving the input fields to the database table will need to be done with the OO Basic macro. However, this is good, because allows you to consider the possibilities of programming OO Basic (before that, in my messages, work with the environment was described only, but all this was preparation for today's message).
To bring up the Dialog Editor, you must select Tools-> Macros-> Organize Macros-> LibreOffice Basic-> Organizer-> Dialog-> New | Edit | Delete from the menu. I would like to have a faster way to get to this editor. After that, the dialog editor will open (alas, not the most convenient), in which we will create fields with the names of the database table fields. As you remember, the Dialogs are not automatically linked to the database tables, so we will write a macro to do this. And as a naming convention, we define the names of the input fields and the fields of the database table are the same.
The editor with the finished form will look like this:
Next, add two buttons to the Dialog. The names of the buttons will be arbitrary, but beginning with an underscore, to distinguish them from the database fields. Each button in the properties palette can be assigned an action. Let's assign one button action OK - it will close the Dialog with confirmation of the action. And the second - Cancel - it closes the dialog without confirming the action.
Close the dialog editor and return to the form editor. Create a button that calls the dialog and assign an Order_Edit procedure to it, in which we will fill in the Dialog from the FromBaseToDialog (oForm, oDialog) database table, and save the FromDialogToBase (oDialog, oForm) database from the Dialogue.
Sub Order_Edit(Event) Dim oDialog As Object Dim orders As Object orders = Thiscomponent.DrawPage.Forms.GetByName("orders") DialogLibraries.LoadLibrary("Standard") oDialog = CreateUnoDialog(DialogLibraries.Standard.dialogOrder) FromBaseToDialog(orders, oDialog) If oDialog.Execute() = 1 Then FromDialogToBase(oDialog, orders) orders.UpdateRow() End If End Sub Sub FromBaseToDialog(oForm, oDialog) Dim I Dim sName As String For I = 0 To Ubound(oDialog.Model.ElementNames) sName = oDialog.Model.ElementNames(I) If Mid(sName, 1, 1) <> "_" And Mid(sName, 1, 5) <> "Label" Then ODialog.GetControl(sName).SetText(oForm.Columns.GetByName(sName).String) End If Next I End Sub Sub FromDialogToBase(oDialog, oForm) Dim I Dim sName As String For I = 0 To Ubound(oDialog.Model.ElementNames) sName = oDialog.Model.ElementNames(I) If Mid(sName, 1, 1) <> "_" And Mid(sName, 1, 5) <> "Label" Then oForm.Columns.GetByName(sName).UpdateString(Trim(oDialog.GetControl(sName).GetText())) End If Next I End Sub
It is assumed that the Dialog has been saved in the Standard library under the name dialogOrder. Naturally, you can choose other names. Names of controls beginning with an underscore are skipped and not processed. Also, names beginning with Label, which are used for text field headers, are not processed.
Calling oDialog.Execute () = 1 displays the Dialogue inside the Form window and pauses the macro until the OK or Cancel button is pressed. Clicking on the OK button will equal the return value to one.