LO Base: I created a form to handle a table, but can not create new record

[Carlos_E_R]

I created a Form to access and edit records in an MySQL table. See photo:

I explain. It is the catalog of my personal library. The form was created with the form wizard, then modified.

The top half draws from a Query, so it is read only. Fields like “Autor” (author) or “Género” (Genre) are internally numeric, pointing to other tables that contain the text. The query does the searching and displaying the texts. This part works wholly, AFAICS. It can not create a new record, it is read only.

The bottom part (the subform in the wizard) draws from the books Table and is R/W. Some fields are drop lists doing the text lookup for fields like author or genre. All the editing works, AFAICS. However, the problem is that when I click “new record” (I have first to go to the last record in the top half, then click in the bottom half, then click the button in the tool bar below), a new empty record is created, but can not be saved because the Index field is not autoincremented, thus it contains a duplicate number (the Index field displays <AutoField>" and not a newly created index number; I can not edit the number directly, either). On save record I get this error:

Error inserting the new record /home/abuild/rpmbuild/BUILD/libreoffice-7.3.6.2/connectivity/source/commontools/dbtools.cxx:747

Duplicate entry '374' for key 'PRIMARY' /home/abuild/rpmbuild/BUILD/libreoffice-7.3.6.2/connectivity/source/drivers/mysqlc/mysqlc_general.cxx:119 

I can, though, create the new record directly in the table or using a different form (that has different issues), and then edit the record in this form, and save it.

(I have a similar form (my previous step) in which both top and bottom parts pull from the Books table (thus the top part displays ID numbers for fields such as author or genre), in which I can create new records, but obviously the top part is not useful, using numbers for names)

Do you know what is wrong, or how can I solve or bypass this issue? Thanks.

I am using openSUSE Linux Leap 15.3.
Regarding LO Base, I’m almost a novice.
If you need more information, please ask.

Version: 7.3.6.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 12; OS: Linux 5.3; UI render: default; VCL: gtk3
Locale: es-ES (en_US.UTF-8); UI: en-US
Calc: threaded

[Villeroy]

There is a wrong association between the parent form and the subform’s primary key. You try to insert a new record with 374 as PK value. This value exists already and therefore it can’t be used for a new record.
Open the form for editing.
Right-click anywhere in the subform and click “Form Properties…”
On the “Data” tab you find the Master->Slave relation which includes the subform’s primary key inherited from the parent form.
If this setup works for you except record insertion, you may want to use a separate form for new records.

[Carlos_E_R]

@Villeroy

Open the form for editing.
Right-click anywhere in the subform and click “Form Properties…”

If I right click “anywhere”, ie, the background, I don’t see “Form Properties”. If I click on a object in the bottom half, I do get Form properties.

On the “Data” tab you find the Master->Slave relation which includes the subform’s primary key inherited from the parent form.
If this setup works for you except record insertion, you may want to use a separate form for new records.

I have, on the bottom half:

[General]
Name:  SubForm
Submission encoding: URL
Type of submission: Get

[DATA]
Content type: Table
Content: Biblioteca.Libros    (correct table)
Analyze SQL command: yes
Link master fields: "Indice"
Link slave fields:  "indice"
Allow additions: Yes
Allow modifications: Yes
Allow deletions: Yes
Navigation bar: Yes
Cycle: Default

As far as I can see, every thing is correct.

On the top half:

[General]
Name:  MainForm
Submission encoding: URL
Type of submission: Get

[DATA]
Content type: Query
Content: QueryAllLO    (correct query)
Analyze SQL command: yes
Sort: "Indice" ASC
Allow additions: Yes
Allow modifications: Yes
Allow deletions: Yes
Add data only: No
Navigation bar: Yes
Cycle: Default

New record doesn’t work because it creates a duplicated key, that’s the problem, I know; but not how to make it create a new unique key. It should do it automatically. It works if the top half is not a query but a table, but then I get ID numbers instead of the autors names.

[Villeroy]

@Carlos_E_R

Link master fields: "Indice"
Link slave fields:  "indice"

This filters the subform by its “indice” having the same value as the parent form’s “Indice” AND every new record inherits this value. SInce “indice” is a primary key, you can not add another record with the same “indice” value. There is no simple solution.
You can restrict the subform with

Allow additions: No

and use another form for new records with

Add data only: Yes

Alternatively you may overwrite the inherited “indice” manually which will be difficult if the field is defined as automatic ID.

[Carlos_E_R]

@Villeroy

This filters the subform by its “indice” having the same value as the parent form’s “Indice” AND every new record inherits this value. SInce “indice” is a primary key, you can not add another record with the same “indice” value. There is no simple solution.

Ah! I understand. I see

Previous to this form I wrote another, in which the basic difference is that

[General]
Name:  MainForm
...

[DATA]
Content type: Table   <===
Content: Biblioteca.Libros
...

[General]
Name:  SubForm
...
[DATA]
Content type: Table
Content: Biblioteca.Libros
...

In that one, New Record works, although I think it is done on the top half. I don’t remember trying in the bottom half. The problem with that form is that the top half displays the numeric IDs of author, genre, etc, instead of the texts, so I now use a query.

Argh! Solve a problem, create another.

You can restrict the subform with

Allow additions: No

and use another form for new records with

Add data only: Yes

Ok, yes. I did that using using the previous form, manually, then returned to the current form for the edit job. A bit ugly.

I had a look at placing a button which starts a form, but did not see how. I googled, and everybody said this is very complicated. I thought it would be trivial: put a button, define action on push as “open form = suchname”, done.

I had a look on the 565 page tutorial, and the matter of creating buttons and doing things is basically not explained.

Alternatively you may overwrite the inherited “indice” manually which will be difficult if the field is defined as automatic ID.

It does not allow writing anything. First thing I tried.

[Villeroy]

Are you looking some kind of filter mechanism to look up a distinct record quickly?

[Carlos_E_R]

No…

When I look for a particular record, I change the sort order to the interesting column, and search visually.

With editing and searching capabilities I am happy. The hurdle now is adding new books.

Next job will be exporting to a calc sheet on the phone (google calc, perhaps), but not now.

I might also add remote access via ssh tunnel, but again, not now. Not important.

[Villeroy]

So copy your grey subform controls into a new form document and set the logical form’s property “New records only” = Yes. This form will always load to the new record.
Now we are going to introduce a user-defined type of push button.
You are working with Writer documents that are embedded in the Base document. Logical forms and their form controls are attached to these embedded form documents. Unfortunately, nobody has implemented a simple mechanism to open another embedded form (or report) with a hyperlink or push button.
The following Basic macro opens any embedded form or reports even if it resides in some embedded folder (you may organize embedded forms and reports in folders).

If this is your first use of macros:

1. Call Tools>Options>Security, button [Macro Security…], choose the highest security level and add your document folder or some of its subfolder(s) to the list of trusted sources. Do not include your Download folder!
2. Store your database document in the trusted folder or any of its subfolders.

Store the following macro to the global container “My Macros”, so it is availlable for all database documents.

1. Tools>Macros>Organize>Basic…
   2) On tab [Libraries] add a new library to “My Macros”, say “Base” for Base macros. Avoid the “Standard” library.[
2. Select your database document and click [New…]
3. Paste the below code into the Basic module.

Sub Open_Report_Button(e)
REM specify the hierarical name in the button's "Additional info" field
	sName = e.Source.Model.Tag
	OpenEmbedded(ThisDatabaseDocument, sName, bReport:=True)
End Sub

Sub Open_Form_Button(e)
REM specify the hierarical name in the button's "Additional info" field
	sName = e.Source.Model.Tag
	OpenEmbedded(ThisDatabaseDocument, sName, bReport:=False)
End Sub

Sub OpenEmbedded(odb, sHierachicalName$, bReport As Boolean)
	if bReport then
		container = odb.ReportDocuments
	else	
		container = odb.FormDocuments
	endif
	obj = container.getByHierarchicalName(sHierachicalName)
	obj.open()
End Sub

How to set up this macro to open any form or report embedded in the same database document:

1. Add a push button to your form document. It is not important to which logical form this button belongs.
2. The push button has a property “Additional Info” where you enter the name of the form or report to be opened. Objects in folders can be specified with slashes: folder/subfolder/My Form
3. On the “Events” tab, click the ellipsis near “Execute Action” and navigate to “YourDocument.odb” > library “Standard” > module “Module1” > routine “Open_Form_Button” (or “Open_Report_Button” if the name refers to a report).

A close button does not require any macro:
Add a push button.
Property “Action”: Open Document or URL
URL: .uno:CloseDoc (with leading point and case-sensitively). This calls the same UI command as manu:File>Close

[Carlos_E_R]

@Villeroy

Store the following macro to the global container “My Macros”, so it is availlable for all database documents.

1. Tools>Macros>Organize>Basic…, button [Organize…]
2. On tab [Libraries] add a new library to “My Macros”, say “Base” for Base macros. Avoid the “Standard” library.

Well, it refuses to go under “My Macros & Dialogs”. I highlight “My Macros & Dialogs”, hit the new button, name it, then it moves itself to “Standard”. I can not move it from there.

I don’t know if this is important.

3. Click [Edit…] which opens the Basic editor with a new module “Module1” in the new library. Rename “Module1” to something more descriptive, say “OpenEmbedded”, and replace the module content with this code:

I don’t get to see any modules :-?

See photo:

:-?

…

1. Add a push button to your form document. It is not important to which logical form this button belongs.
   …

Done.

When clicking the button, I get a Basic runtime error / Object variable not set, and highligts the line:

Sub OpenEmbedded(odb, sHierachicalName$, bReport As Boolean)

but I’m calling the open form sub.

The close button on the new form was easy, it closes the form. It is very simple, just a book name, a comment, and index.

And if I open that form externally, it does create a new book entry into the database.

[Villeroy]

You forgot to select the “Library” tab on the organizer dialog. Now you use the container “My Macros”, library “Standard”, module “Base”. I wanted you to avoid the “Standard” library, but this is not so important. Leve it as is.
Assign “Open_Form_Button” to the “Execute Action” event, not “OpenEmbedded”.

[Carlos_E_R]

@Villeroy

You forgot to select the “Library” tab on the organizer dialog. Now you use the container “My Macros”, library “Standard”, module “Base”. I wanted you to avoid the “Standard” library, but this is not so important. Leve it as is.

Oh, now I see. I deleted what I had and remade it, just a few minutes and I want to get it right.

Assign “Open_Form_Button” to the “Execute Action” event, not “OpenEmbedded”.

I did, now and before, see photo:

Still, the error is the same on button click:

Basic runtime error / Object variable not set, and highligts the line:

Sub OpenEmbedded(odb, sHierachicalName$, bReport As Boolean)

See photo:

Thanks for your help so far.

[Carlos_E_R]

The Sub “Open_Form_Button” calls OpenEmbedded(), that’s why it appears in the error.

[next morning]

I have added a breakpoint on line 12, the call to OpenEmbedded, and looked at vars.
obj, bReport, ThisDataBaseDocument are out of scope.

var value type
sName “Biblioteca.NuevoLibro” Variant/String <=== that’s the form to open, it is correct.
e com.sun.star.awt.ActionEvent

[Villeroy]

I’m sorry that things are so complicated. I’ll attach an old database of mine. The relevant 2 forms are in folder “Buttons” where I simulated your problem with the inherited primary key.
The buttons to open the other form call the macro that is embedded in the database document.
Indeed they end up with error “Object variable unset” when you call the same macro stored under “My Macros”. The macro is written to open any form or report embedded in any database document.

However, I stumbled upon the matter of fact that ThisDatabaseComponent refers to the database document embedding this code. This fails when the code resides in “My Macros”. Please store the macro in your database document. THe names of library or module don’t matter.
DummyPersons7.odb (102.1 KB)

[Carlos_E_R]

I applied your modified instructions, and they work instantly

It was complicated, but I am learning things along the way.

Thanks