How to update a joined field in another table which is not present in Current Form

[sarfraz]

I am self taught for Microsoft Access and have working with access databases for couple of years. I need to an expert way for the below scenario:



I have 2 forms, Form1 is used to enter staff personal and salary information while Form2 will be used to enter Bank information. I am using three tables to maintain this information:
1- Personal Info
2- Salary
3- Bank Details

All tables are joined in relationship by using a field StaffID which have to be entered manually as I can not use auto number. I have enabled cascade update and delete in relationships

The problem is if i use the Form1 to enter the personal & salary information, the StaffID which I enter in Form1 is automatically updated in salary table as Form1 is using both personal & Salary tables. But the StaffID is not updated in table Bank Details. This is crucial as this StaffID is a link between Personal and Bank table. Is there any way that when I enter StaffID in Form1 it will automatically append StaffID in Bank Detail table? Or can I use any query to append StaffID to Bank Details table.
Note: We are working on split database and the person who is having access to Form2 (Bank Details) should not have access to Form1(Salary Details). This is why I have to maintain separate tables to maintain a confidentiality.

[ranman256]

1. you should keep the STAFF ID field names the same in both tables to show they ARE the same field. tPERSONAL INFO and tBANK (but you dont have to)
2. you need to make a SUB FORM. Using tPERSONAL table the MASTER and tBANK as the CHILD. Set the relationship 1 to many
3. Create a DETAIL form that has 1 master record (personal), add a sub form that has many tBANK records. The STAFF ID will be automatically added because of the relationship.