A Form updating more than 1 table

[Thomadh]

I have only updated 1 table with a Form to date
Can you update more than 1 table from a single Form input?
The Wizard seems to allow only 1 Table to be selected as the data source
If so any quick pointers would be useful
Many thanks

[Andrew Thackray]

A form can only be bound to one record source. However if you can join the two tables in an updateable query you can point the form to the query and update fields from both tables. Otherwise you will have to update the second table in VBA by opening a recordset to the second table and programmattically move the field data from the recordset to the form controls and back again.

[MMcCarthy]

I have only updated 1 table with a Form to date
Can you update more than 1 table from a single Form input?
The Wizard seems to allow only 1 Table to be selected as the data source
If so any quick pointers would be useful
Many thanks

Create a query with the the tables you want to update. There should be a one to one relationship only between the tables.

The query should be updateable.

To check open the query and make sure you can add a new record.

You can now use the query as the record source of your form.

However, its more likely that there is a one to many relationship between tables. Standard practice in dealing with this is the use of subforms.

For example:

If you had a form based on a customer table.

You could create a subform based on an order table. The order table should have a relationship through customerID with the customer table.

You should now be able to see on the subform all orders attached to this customer.

[Thomadh]

Create a query with the the tables you want to update. There should be a one to one relationship only between the tables.

The query should be updateable.

To check open the query and make sure you can add a new record.

You can now use the query as the record source of your form.

However, its more likely that there is a one to many relationship between tables. Standard practice in dealing with this is the use of subforms.

For example:

If you had a form based on a customer table.

You could create a subform based on an order table. The order table should have a relationship through customerID with the customer table.

You should now be able to see on the subform all orders attached to this customer.

Thanks for this but I cannot seem to get it working

What I have is 3 identical tables of staffnames (Resource1,resource2 and resource3) (Don't ask why it was just a simple way for me to assign up to 3 people to a task via a drop down list!)

To update staff information therefore involves 3 identical actions at present and I wanted to have 1 form which would add,amend or delete a staff member details in all 3 Tables

Hope this explains what I am trying to do!

Any ideas most welcome

Sincere thanks

[MMcCarthy]

I hope you don't mind me saying this but your structure is terrible and is going to cause you endless problems. It would be much easier to change your structure that to keep doing what you are doing. Any union query of the three tables won't be updateable.

Suggestion:

Create a union query of the three tables. You will have to do this in SQL view of the query. Lets call it qry1.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
UNION
SELECT * FROM Table3;

Now create a new query as follows and in the access design view of the query change the query type to a make table query.

SELECT * FROM qry1;

You will now have one table containing all the records.

Add a field to the table called ResourceID. Make it the AutoNumber type and set it as the primary key.

This should be your record source from now on.