how to fill in a field in multiple tables?

[carlosguill]

I have four related tables and all have a field "PATIENTID, how can I update this information in all the tables using a form and sub-forms without having to type the info in each table? the patient ID is a text field with alphanumeric characters.

 

[PHV]

Provided the relationships are properly declared, simply follow the form/subforms wizard.
The basic idea is to play with the Master/Child links properties of the subforms.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[carlosguill]

I am very new to Access, could you be a little more specific about the Master/Child links properties? Thanks

 

[willir]

Carlos

If not already done, make sure your relationships are defined. Do this BEFORE creating your subforms.

304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

From the menu, "Tools" -> "Relationships".

Add your tables to the GUI window.

Then click and select the primary key and drag it to the foreign key in the related table. I.E. drag PatientID in tblPatient to PatientID in tblAppointment.

When to release the mouse, a window will popup to allow you to further define the newly created relationship. As a rule, always "Enforce referential integrity". This means you can not create a child record without the parent. You can not create an appointment without first creating a patient. This is a good thing.

Since your PatientID is alpha-numeric, you may want to "Cascade updates". (If you change a PatientID, the update is also applied to the other related tables) From a personal perspective, I do not "Cascade deletes". With cascade deletes, you delete a patient and all appointments made with the patient are deleted. I prefere to delete the appointments manually and then delete the patient (logically speaking of course ;-) I know of others that prefer to use cascade deletes.

Once you create your formal relationships, when you create a subform either with the wizard or by dragging and dropping form to be used as a subform onto the main form, Access will automatically link the forms using the defined relationships.

Hint: You may want to hide the PatientID text box on the subforms since the information is available on the main form, and may be a bit distracting appearing again on the subform.

Richard