Ask Your Question
1

How can I add linked database records to multiple tables using a form? [closed]

asked 2014-05-27 06:36:54 +0200

Frungi gravatar image

I have a database where one table has a foreign key matching an auto-incremented index in another table. I want to use a Base form to add a record to both at once, automatically including the foreign key for the new record in the second table. Is this possible?

Simple illustration: Table A has six rows, and table B has four rows. In a single form entry and without knowing either number, I want to add rows that look like this:

           A                          B
+----+------+-----------+    +----+-------------+
| id | b_id | name      |    | id | content     |
+----+------+-----------+    +----+-------------+
|  7 |    5 | blah blah |    |  5 | yadda yadda |
+----+------+-----------+    +----+-------------+
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-23 21:26:05.512344

2 Answers

Sort by » oldest newest most voted
1

answered 2014-05-28 08:39:13 +0200

frofa gravatar image

updated 2014-05-28 12:49:47 +0200

I want to use a Base form to add a record to both at once, automatically including the foreign key for the new record in the second table. Is this possible?

Logically the table B record must exist before a linked table A record is created (assuming you are enforcing primary and foreign key relationships, you probably would get an error if you tried doing it the other way round).

That said, you should be able to setup a main form sub-form pair with appropriately configured master and slave fields defined in the sub-form data properties to 'automatically' add a new record to table A with the appropriate b_id value as you describe once you have selected a row in your table B. In Base this is all 'built-in' to form/sub-form functionality. (You might also use a LIST BOX to select a row in table B (by content) and the ID value would be written to table A in a newly added row/record.)

When you say 'automatically', you still would have to add a row to table B if it didn't exist already, and also enter the value of the name field in the linked table A row. Do you mean something else by 'automatically'?

edit flag offensive delete link more

Comments

I meant that I wanted to be able to type into the form (in my simplified example) both name and content and have the subform add the table B row, and then the form add the table A row. If I understand your answer, this is not possible, but please respond to confirm this. Thank you.

Frungi gravatar imageFrungi ( 2014-05-29 05:31:14 +0200 )edit

I cannot see how you can just 'type in' values for your name and contents fields without first creating new rows (records) in your two tables to 'hold' these values (where would you type them in?). But maybe I don't really undertand what you are trying to achieve.

frofa gravatar imagefrofa ( 2014-05-29 09:59:46 +0200 )edit

Into the form. A Base form for adding new rows.

Frungi gravatar imageFrungi ( 2014-05-29 10:01:15 +0200 )edit

Are you trying to create a new row in the second table (i.e. the related table) 'automatically' (i.e. 'triggered' by adding a new row to the first table)? Maybe that would need a macro (coded) solution?

frofa gravatar imagefrofa ( 2014-05-29 10:29:47 +0200 )edit

I want the subform to create the new row in the second table, and then the main form to create a new row in the first with the foreign index, except all in one go. I tried to set this up with subform master/slave fields, and both rows are added when I enter new data for both, but the foreign index is blank.

I’m willing to accept that it’s impossible, but that would make me sad.

Frungi gravatar imageFrungi ( 2014-05-29 12:10:11 +0200 )edit

A form with a properly configured sub-form should allow automatic insertion of the foreign index/key in table B. I would suggest you look carefully at the way you have configured your sub-form data properties accessed via the form navigator. The link master-lave link fields must be correctly set. A screenshot of your FORM NAVIGATOR window (showing all the elements of the hierarchy and the data tab for the sub-form) might help diagnose your problem further.

frofa gravatar imagefrofa ( 2014-05-30 03:40:32 +0200 )edit
0

answered 2014-05-27 17:31:58 +0200

m.a.riosv gravatar image

Maybe samples in documentation can help:

https://wiki.documentfoundation.org/D...

edit flag offensive delete link more

Comments

If there was anything in there about adding from a form and subform at the same time, I couldn’t find it. Did I miss it?

Frungi gravatar imageFrungi ( 2014-05-27 20:40:33 +0200 )edit

Could you be more specific, @mariosv?

bencomp gravatar imagebencomp ( 2014-06-10 22:22:07 +0200 )edit

Question Tools

2 followers

Stats

Asked: 2014-05-27 06:36:54 +0200

Seen: 3,189 times

Last updated: May 28 '14