Entering data simultaneously to several tables on a form

[PhilipK]

My data are disposed across several linked tables. It is possible to display these in a single form (with controls dependent on subforms) as shown. This contains a main form and six sub-forms. When consulting existing entries, it is possible to edit any of the fields in any of the controls. However, when I want to add a new record, it seems that I can enter data into the controls belonging to the main form (the 'Stamp" area), but they neither prompt display of linked data in the other fields (e.g. if I enter the number of an existing potter), nor enable me to enter new records (e.g. if I enter a new potter no.). I used to be able to do this in Paradox almost without thinking.
Is there something I am missing, or is Base not capable of supporting this level of complexity? (I should add that the properties of each subform are set to allow additions/modifications/deletions.)

Further elaboration on 19 May: this is quite a complicated scenario to explain on this website, so I attach a dummy version of the database, with just a few records, OCK_sample.odb and a text file which gives a fuller explanation of the problem.
OCK sample exp.docx. If this seems inappropriate for this forum, it can surely be deleted.

[Ratslinger]

Hello,

It appears you have not established the linking between main form and sub form(s). This is a property on the sub form.

The links to the LO documentation, on-line & manuals, can be found on the main page of questions on this site. Look in right hand column near the top under Resources.

Quick link to Base → LibreOffice Base Guide. See Chapter 4 - Forms. There is a section Main forms and subforms explaining this.

Edit 2020-05-16:

Simple form with master and two subs. Used table controls in subs but that is not relevant.

Sample ------ MultiSub.odb

[PhilipK]

No, that is not where the problem lies. The subforms are all linked to the main form, and when I scroll through the records on the main form, the sub-form entries change to match. They just don’t cope when entering a new record. (Do I need repeated refreshes?) I find the LO Base Guide disappointing - but realise that I have been spoiled by the Paradox documentation which (in the 1990s) came with the software in hard copy running to some 2,000 pages in three volumes!

[Ratslinger]

I use many forms with multiple sub forms and do not have any problem. Have also dealt with sub sub forms and further.

Can’t say where your problems lies with what is provided. It would be helpful if you posted a scrubbed version of the Base .odb file.

Edit:

Added basic sample in answer.

[Ratslinger]

@PhilipK,

Have looked at you sample.

You are using primary keys of related tables as a foreign key also. This is actually better as a separate field. It is better to have these tables with an auto increment field for the primary key and the a separate field linking back to the master record. This can be seen in the sample attached in the answer.

You have tables in sub forms which need to have records entered before the main form can be entered.

In your main table there are three field necessary for the key to be established - Pot No. & Potter No. & Stamp Type

“Pot No.” could be an auto increment number. Potter No. & Stamp Type cannot be entered until those records are created in their appropriate tables as they are primary keys there. Those entries as sub forms for new records here are useless.

So now you cannot create the main record until two other table entries are complete.

What you are attempting cannot be done with this design. Keys are conflicting.

[PhilipK]

Thank you. The database works, and its structure works; it contains already over 36K records, input over a period of some five years. The design of the entry form is also identical to that used in Paradox. The issue here must be, I think, a difference in the way that Paradox and LibreOffice handle an uncommitted record. P was capable of using entries in controls on a form - not yet committed to their underlying table - to reference other tables. LO is presumably not going to do that without some explicit handling in macros. I hope this is possible, as the data input is otherwise going to become much more laborious - but it is probably going to require a lot of thought to make it work.

[PhilipK]

Now see separate answer above.

[PhilipK]

OK, I believe I have resolved this, through several moves. (1) Delete all table relationships within the database definition. Referential integrity is enforced by code on forms, and tables are never expected to be manipulated directly. (In this phase, I am the only user, so this is not a serious weakness.) (2) Enter data into all three of the fields which compose the primary key of the master table/form before attempting to access any of the fields in sub-forms. (3) Add macros to the ‘After Updating’ events for those fields which link to the subforms/dependent tables, forcing a reload in those subforms. (4) Add validation macros which check that all foreign keys are satisfied (moving the cursor if necessary into the empty fields) before moving to another main record. In this way all dependencies can be seen/added from the one form.