How to create a data entry form based on a query?

[michielper]

I need to make a data entry form in which not all fields are from the same table. So I suppose I need to base the form on a query which produces all the fields I need. Some fields will be read-only and some will receive data (although the query, produced via the designer is only based on a SELECT statement).

However, when I create the form, I cannot enter data in it although all field properties seem to be set non-read-only.

What can be wrong? Is it at all possible to create a data entry form based on a query? Does the query have to be of a particular form?

Thanks for any help!

Michiel

[Ramne]

Hello Michiel!
I can’t explain why your query-based form doesn’t work but I know that it will not. I think it only works to edit data in a query if the query just connected to one table.

For you form you will need to decide which table should be your main table and which ones would be tables for one or more subforms.

You also have to make it possible to link between your tables.

Example: If you want table A to get a value from table B then table A need a column for a foreign key to (the primary key in) table B. This could be the case for your read-only values.
And vice versa: For table B to receive data it has to have a (foreign key-) column to store the link to (the primary key in) table A.
You may also have a many-to-many relation and in that case you will need a third table – a junction-table – between the two tables of that relation. Or maybe a one-to-one relation and in that case you just connect the tables primary keys together.

The actual relations between the tables are then created in Tools – Relations…

I hope this will help!
/Ramne

[michielper]

Thanks for your answer Ramne!

Of course I know about foreign keys and so. You say that a data entry form only works on one table! I have in fact noticed that it does work when I use one table and not a query.

I experimented a bit more and found out that although a form based on a multi-table query does not allow data entry, a form based on one table plus sub-table does allow data entry in fields of the main table. So this solves at least some of my problems. Unfortunately, only one sub-table is allowed.

Regards, Michiel

[w_whalley]

You can have multiple subtables, but you need to install them using the Form Navigator. How to do it is a bit tricky. Start here, http://forum.openoffice.org/en/forum/viewtopic.php?f=83&t=32757

[Ramne]

That’s a very good tutorial. If you don’t wnat to read so much maybe this can be an alternative: http://www.youtube.com/watch?v=TcudVETPePY

[michielper]

Ok, thanks. That tutorial does not talk about subforms but perhaps that is easy once you know about the Navigator… I have also gotten good advice on http://en.libreofficeforum.org/node/5402#comment-22998