Can someone help why I am not able to add new records following the underneath sql statement:
SELECT “crew-rollen”.“Id-rol”, “crew-rollen”.“Id-crew”, “crew-rollen”.“Id-prod”, “crew-rollen”.“rol-rol”, “crew-rollen”.“rol-acteur”, “t-toneelwerken”.“productie-toneelstuk” FROM “crew-rollen”, “t-toneelwerken” WHERE “crew-rollen”.“Id-prod” = “t-toneelwerken”.“Id-prod”
tables “crew-rollen” and “t-toneelwerken” have relationship “Id-prod” in relationship table.
Create a main form with one table and a subform with the other table, linked through their common id-prod.
Now you can edit/insert/delete records in the main form and the related records in the subform.
The following should be editable if both tables have a primary key:
SELECT *
FROM "crew-rollen", "t-toneelwerken"
WHERE "crew-rollen"."Id-prod" = "t-toneelwerken"."Id-prod"The SQL statement you provided is a Query. Its function is to display existing table data.
The SQL to add new records takes the form
INSERT INTO “table_name” (“column1”, “column2”, “column3”, ...)
VALUES ('value1', 'value2', 'value3', ...);
Instead of constructing and executing SQL statements like that, let Base do the work by following the advice provided by Villeroy.
I think, I explained wrongly my question. I have the subform “crew-rollen” in the form “f-crew”. In that subform I enter the data of the crew member such as ‘Id-prod’, ‘rol-rol’ etc. On entering the ‘Id-prod’ I would like to have displayed the title of the play connected to the Id-prod (this title can be found in “t-toneelwerken” and/or “t-producties” (I prefer this one). But I don’t find a way to do so, meanwhile I thought it would be possible through a query. But there I am stack, no possibility to enter data.
it’s a kind of lookup I am seeking
This is what subforms do.
Here is a small sample I revised a bit that may get you pointed in the right direction.
Title-Person_v1.odb (17.1 KB)
How are entering a value in “crew-rollen”.“Id-prod”?
A Listbox Control on the Subform for the field “crew-rollen”.“Id-prod” should be able to display the related play title.
It might be best if you upload an example of you database.