tab control and tables

[moona]

I am trying to get data from 4 tables to show up (for entry or editing) on 4 tabs of a tab control.


So here is the structure:

table1.col1 (PK)
table2.col1 (FK)= table1.col1 (PK)
table3.col1 (PK)
table3.col2 (FK) = table1.col1 (PK)
table4.col1 (FK) = table3.col1 (PK)

Basically Table2 is the child table of Table1
and
Table4 is the child table of Table3

Table3 is

tab control has 4 tabs (tab1...tab4)

and the data is displayed on:
tab1 = table1
tab2 = table2
...
tab4 = table4


Table1 and Table2 are displayed and synched correctly on tab1 and tab2

How can I get tab3 to display data from table3 - I am sure if I figure that out tab4 should display table4 correctly.

Is this even the right of UI design? Other suggestions are welcome.

[rpeare]

Your table 3 is related to your table 1 (FK to PK) so if you can get the tab controsl for table 2 and 4 work table 3 should work the same way (I'm guessing subforms on tabs 2, 3 and 4). Can you say what exactly the problem is you're encountering? If you are using subforms for tab 2, 3 and 4 are you able to link your Fk on table 3 to your PK on table 1 (the field be available in the data source for the subform to be able to link correctly)?

[moona]

May be I did not explain correctly...

able1.col1 (PK)
table2.col1 (FK)= table1.col1 (PK) ==> one to one relation
table3.col1 (PK)
table3.col2 (FK) = table1.col1 (PK) ==> many to one relation
table4.col1 (FK) = table3.col1 (PK) ==> one to one relation

I have not got table 2 and 4 to work. Table1 and Table2 are working correctly ==> means if I add a record in table1 the autogenerated pk in table1 syncs correctly on tab2 for table2 and allows me to enter data for table2 on tab2.
However, when I go to tab3 to enter data for table3 the error is on the pk field - no data is autogenerated for it - but the fk for table3 is showing correctly.

May be I need to ask:
How can I get the form for table3 on tab3 and also make it sync with the data from table1 via the table1.pk = table3.fk (one to many).

The data is as follows:

table1 = personal data (pk = autogenerated userid)
table2 = details of confidential personal data linked one to one via table2.fk = table1.pk (each person has specific set of confidential info)

table3 = bank information (pk = autogenerated bankid) related to table1 (many to one via table3.fk = table1.pk) i.e. a person can have multiple bank accounts at multiple banks
table4 = confidential bank information linked to table3 via (one to one tabl4.fk = table3.pk) - each account has specific set of confidential info


Two scenarios:

1) adding data:
I can currently add data on tab1 (and it generates the pk) and then go to tab2 and continue adding data - works
when I go to tab3 I see the userid but I can't get it to autogenerate value for the bankid.
tab4 is currently blank (no form)

2) editing data:
works fine for tab1 and tab2 -
tab3 can't add data into any fields.


So, how do I get tab3 to be in insert mode or even better what is the right way to design the form and it's data control source so that the records are correctly linked to tab1 and tab2. Of course I could have multiple records in tab3 that have the same userid but they will all have different pk - bankid.

makes sense? let me know if you need additional details.

[rpeare]

Each one of your subsidiary tables (table 2 through 4) all relate to your 'main table' (table 1) in a relationship of FK to PK. In your tabbed control if you make each one of the data entry screens for each one of the subsidiary tables a SUBFORM the FK will be generated whenever you enter new data. A subform is the only way you're going to get the FK to be populated automatically. Have you tried creating a subform for each of your additional tables and placing those subforms on the tab controls you want.

[moona]

That is an excellent hint....I never knew I could drag and drop a table onto the tab and it would create a form automatically....so once I did that for all 4 tabs, I basically have solved (most) of the issues.
So the pk on tab1 is reflected now in all the tabs as fk (just as you mentioned).
I have to now test is for various conditions....I'll report back if I find issues - I have changed the relations between tables too - I had found an issue with it also.
Thanks for your excellent response.

[moona]

Ok, after redesigning all referential integrity conditions, all four tabs work fine.

The only issue now is: to customize the sub forms. For some reason it defaults to a very basic - worksheet type form.

I'll search for a post that addresses this issue - else create one.

Thanks everyone for your help.