Inserting records in multiple tables

[Nikos]

Hello there, i began to make a simple db for my patients (i m a doctor).

I have a central table with the patient's data and others regarding their history, their visits etc



I ve made a form where i put fields from all the tables in the same form. I need to be able to type in the each new patient's data, with his significant history and then to type in each new visit for each patient.

For this reason i've put the Social Security Number in every table. It's the primary key in Patient's data and in patient's history and it's common but not primary key in the Visit's table. So i've made One-2-One relationships with patient's data and patient's history tables (i need 1 record for each patient) and One-2-Many relationship between patient's data and patients' visits' (many patients, many visits for each one).

The problem is that when i insert the patient's data in the form, and i go further to insert info in the fields of his history and visits, Access say "cannot find the value in the "SocialSecurityNumber" field of Patient's History table".

Is there any way to insert one time the SS number in the patient's data table and then automatically be inserted in all SSN fields, in all tables as new records??

[aytee111]

Presuming your tables are normalized - ie parent/child relationships?

The way you would do it is have the parent table bound to a form and then have subforms bound to each other table. That way you can have all the data on one form available to you all at one time. You would select the patient on the main form, or else add a new one, and the subforms would then be linked via the primary key which would be then inserted on to all new tables.

[Nikos]

Ok thank you, i'll try it!

[Nikos]

Well, i did it and although i've made the correct relationship, it didn't work. Still it searches each new record in the subtables, it doesnt record them in. I tried also to manage the subform's properties , giving the specific parent and child field, but still it doesn't work.
I think i need a button with a visual basic code. So that you insert data in the main form and then pushing the button, copying them to the others.

I've heard about an order INSERT INTO, but i don't know to manage it.

Any help?

[aytee111]

Can you attach your database?

[Nikos]

hehe, can you reed Greek?

[aytee111]

Oh dear!

I don't understand the problem. You say "Still it searches each new record in the subtables, it doesnt record them in" - does this mean your main form and your subform aren't being joined? If this is the case, then here is the answer. Go into design mode for the main form, click once on the subform to highlight it, go to its properties, make sure that the two "Link" properties have the right values. This will link your forms and will make sure the subform carries only the data from the main form. Access will do it for you, you won't need to create queries or code to get it done.

Was this the issue?

[Nikos]

Well this is what i said before. I checked (and double-checked) the parent-child relationships in the property sheet. Still the message is the same "microsoft access database engine cannot find a record in the "subtable" with key matching field "SSN" ", when i insert the values in the main form and go on to the subform.

[aytee111]

Looks like I am misunderstanding you, sorry!

Is this right - when you add a new patient you want to auto-add a record to the history table?
If so, in the AfterUpdate event - either of the SSN or the form itself, run an append query if missing. That is, create a select query for unmatched items - SSN's that reside in the patient table but not in the patient history table (there is a wizard for that) and then change it to an append query which will insert a record into the history table. Then requery the form/subform.