update more than one table from a form

[shadowraven]

I have a database that im working on and I would like to be able to update more than one table at a time from a form so if I was to change data in the form say appointment time it will change in the releavent tables.
is this possible Im using access 97 a example with a description would be useful I am doing this for my AVCE ICT course and just was wondering how to implement this in one of my databases

 

[lloydmav]

Someone may have a much easier way of doing this but it is possible to do it by creating update queries. The after update event of your appointment field could run an update query to one table and then to another and so on. The criteria in the update queries would refer to that field.

Let me know if your interested and if you need further instructions!

 

[neileg]

Depends what you want to do.

Presumably you have two linked tables and want to update these at the same time. If there is a one to one relationship, you could create a query that gathers up the fields from both tables and base the form on the query. You could use the query wizard and the form wizard to help.

If there's a one to many relationship, create a form that holds the fields from the 'one' side of the relationship and add a subform to hold the 'many' side. Crete the subform as a separate form, and then drag and drop it onto your main form. You can use the form wizard to create both forms.

In either case, closing the form, or moving to the next record will save the data.

These approaches are easier than lloydmav suggestion which would involve an unbound form

 

[lloydmav]

Yeah go with neileg, thats much easier,

I didn't quite understand what you were trying to do and what the relationships were, but if the tables are related properly then you should either use neil's combined query option or the mainform/subform.

 

[shadowraven]

Update tables

What our task is to create a Doctors database which has a user friendly, well laid out screen data input forms with title labels, field names, set widths, pull down lists and instructions to enable data in to multiple tables. This is all over my head and as yet I dont even no what tables will need to be updated but I have to do this for my work.

I have attached my work but its very rough at the moment it will give you some idea of what I have got. Any improvents greatly recieved

The data we were given was in csv format which is table: exam data un-normalised

 

[lloydmav]

I've had a look at your database. First of all you need change the structure of your tables. They need to be normalised so you need to remove duplicate data between tables. Unrelated data should be removed so that each type of data has its own entity/table. These are then linked together with relationships.
I've noticed you need to have a unique primary key field in each of your tables.

For example you want to change your primary key field on the Doctors table and create a Doctor ID (autonumber) as the primary key. For a start you want to put the foriegn key fields Doctor ID and Patient ID into the Appointment tbl and create one to many relationships between them

remember to enforce referential integrity. Work out your relationships between the other tables eg a patient can have many appointments but an appointment can have only one patient.

I might suggest a main menu form, linking you to various other forms. Eg

Patient Appointment frm
Patient Details frm
Doctor Details frm

All of this can be done using wizards which are activated when you try to add an object or create an object.
Try to have a go at this, if you get stuck there are loads of past posts to search an examples,

Let me know if your stuck!