How to create a new record on multiple tables with one input

[Lavo]

I am new to Access and have created a database for Doctors information in work.
I created a database with 3 tables. The Primary Key is the Doctors staff Number, the Doctors staff Number is also included in the other 2 tables and this is both the primary and secondary key linking all 3 tables.

At the beginning when I created a new record in the 'Primary table' and saved it, there would be a record automatically created in the other 2 tables. These would be blank with just the Doctors Staff Number filled in. However this no longer works. It now creates a record in the Primary table but not the other 2. How do I fix this?

I also tried to create a form and included fields from all 3 tables. This works fine if I am updating information however if I try to create a new record I get an error message The Microsoft Access Database engine cannot find a record in the table 'Table 2' with key matching field(s) 'Doctors Staff Number'. Does this mean there is a problem with my key? as there is clearer a key in Table 2 named Doctors Staff Number.

Any help with this would be great. Thanks

[ranman256]

you could make sub forms, so when the user updates the master form:
it runs an append query to the other subform tables.
or
just runs append query to the tables. (the subforms aren't needed but good to see visually.)

[ssanfu]

Welcome to the forum.........

You should not have spaces in object names. "Doctors staff Number" has spaces - a better name would be "DoctorsStaffNumber"
Naming suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not begin an object name with a number.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


I have never had a record created in a related table because I created a record in the PK table.
Maybe you would post your dB. Make a copy of the dB, delete sensitive data (only nee a few records), do a "Compact and Repair", then Zip/Compress the dB and attach it to a reply.

I would use main form/sub form design, maybe with tabs.