I am trying to create a db with a many2many relationship. But when I create a form to enter the relationship (say between movie and genre) I keep getting error message ‘The contents of a combo box or list field could not be determined. Column not found: 2’ SQL status S0022 Error code - 28. The SQL in the form to show the lookup is SELECT “Genre Name” AS “GenreID” FROM “Genre”. If I run this as SQL it works but not as part of the form. I have carefully followed the guide in Simple many-to-many relational database in OpenOffice Base - YouTube I get the same message when I try to make my own database of Photo and Person using the M2M link of lnkPhotoPerson.
For a combobox the SQL-code
SELECT "Genre Name" AS "GenreID" FROM "Genre"
will work, but the alias is needless.
SELECT "Genre Name" FROM "Genre"
will do the same.
For a listbox the SQL-code must show two fields. Might be
SELECT "Genre Name", "ID" FROM "Genre"
The “Genre Name” will be shown for the user, the “ID” will be connected to the data source (foreign key).
You have to set in control properties → Data → Bound field: '1'. The fields are count as fields in an array. ‘0’ is the first field “Genre Name”, “ID” is the second field ‘1’. Error message, which appears for you must be an error in this bound field of a listbox. It has been set to ‘2’ …
Many thanks. Putting two fields in the SQL code did it. The Bound field was 1 so I left it as that and it works. I hope now I can include this approach in my photo database.