subsubform does not show existing records

[LeoP]

OS: Linux 5.4.0-42-generic x86_64

LO: 6.4.4.2, build ID: 1:6.4.4-0ubuntu0.20.04.1

For a small inventory database I have (among others) the following tables and relationships: 

I also have a form that looks like this: 

Class and type are selected from appropriate tables, manufacturer is selected with the SQL statement SELECT "nickname", "status", "role" FROM "companies" WHERE "status" <> 'O' AND "role" = 'M'

The middle table section is a subform for the parts-vendor table, allowing multiple vendors to be linked to the same part. The vendor is selected with the SQL statement SELECT "nickname", "status", "role" FROM "companies" WHERE "status" <> 'O' AND "role" <> 'M'
This works like a charm, no problems there.

The table in the lower left corner is a subsubform, to allow multiple pricebreaks for each vendor/order_code combo. The vendor and order_code fields are linked from the subform and not shown, the label field above it is taken from pricebreaks.order_code.
Here the problem starts: while I can enter multiple pricebreaks, already existing pricebreaks are not shown.

Any attempt to create a relationship from part-vendor to pricebreaks results in an SQL error -170; linking pricebreaks to companies and part-vendor is possible but is not saved.

Most likely I do something wrong, but can't figure out what.
Any help is greatly appreciated.

[Ratslinger]

Hello,

A sample would help a great deal. You have not specified what database you are using making it difficult to research the SQL error. It would also help if the full error message were provided.

It is not usual to have multiple fields as a primary key. This may be part of the problem. It is also better to have the primary key as an integer and not a varchar field (not part of the problem - just a note).

Not even certain what the purpose of the part-vendor table is and based upon that answer, if there isn’t a better way to handle pricebreaks (no details on this).

The situation with this post is that it is really more than one problem ( subform * SQL) and not enough information to solve either one.

[LeoP]

First I added a single key to the part-vendor table to eliminate any ambiguities, no luck on the subsubform.
First I attempted a connecting table but was unsure how to implement that, so I directly used the new part-vendor key to get to the pricebreaks: no luck.
After much ado (and a bug or two) I found the culprit: apparently the default for the setting “Add data only” on a manually created subsubform is “Yes”…
The manual explained that setting, and after I flipped it to “No” the subform behaves as intended.

As for the database type: the bottom line of the main window says “HSQLDB Embedded”.
I just went with the default given (could not change it anyway)…

[Ratslinger]

Hello,

Glad you got things working. As a note, have manually added (no method to do so otherwise) a number of subsubforms and do not recall any one of these defaulting to Add data only.

There are many ways to select the database used when creating a Base file.