Base form one to one relationship

[fifou64]

Hi,
I have two tables linked by a one-to-one relationship.
In my form, I want the user to add the record in both tables at the same time (in one action like a clic on button).

Table 1 :

• id1 1 ,2 , 3, 4 primary key
• a bla, bla, hey!
• b data1, data2, data3, data4
• c 3 , 2, 26, 27

Table 2 :

• id2 2 , 3 primary key (that has to be in id1)
• d true, false
• e more, more

I don’t want the user to enter first table 1 and then table 2 in a subform because he can quit before filling table 2, and in this form, I really want the user to fill table 2.

In my form, I would like my user to fill :
a, b, c, d, e [push button]
The id would be autofield both for id1 and id2.

I made a query with data entry possibility for both tables but I can’t correlate id1 and id2 in the same table can I?
I thought about filters but here the main problem is the autofield on the primary key of table1, that I can’t reach to pass to table2 in one clic.
Is there a way to do this or several ways? I thought about a subform but I can’t manage it. Some examples around here?

[Ratslinger]

Hello,

There is no safe/easy method to deal with obtaining possible auto-increment fields in a multi-user environment. Also how to obtain is dependent upon the DB being used (you haven’t stated). For example using HSQLDB you can get the last used value with:

SELECT MAX("YOUR_FIELD") "LAST_USED_ID" FROM "YOUR_TABLE"

But if this is a multi-user environment MAX(“FIELD”) + 1 (this is the next available) may have been taken prior to the current record set being completed. Now you can improve the odds ( not 100%) by using macros and after some verification update record 1 and then retrieve the value given, insert into record 2 and then update that.

Firebird server & embedded in Base is again different. For a discussion on that see question, answer & comments in this post → Base: get autovalue data after insert.

Other databases may differ yet again.

Using a sub form may be the easiest but it would still require some macro coding. This would be to verify, before table1 is actually updated, that table 2 is entered.

[fifou64]

Yes I am using HSQLDB. But I don’t want macros in my document. The best solution for me would have been to have table 2 id2 filled by the same autofield as table 1 at the same time as table 1 (while inserting).
I can see there could be a problem with SELECT MAX because if I delete the last record, or some of the last records, the auto-increment won’t be equal to max (“myField”) + 1.
So I 'll stick to the subForm !

Thanks again for your answer.