A form can have only one table or query as it's record source. You can use
multiple tables to create a query for a form, but in many cases, queries
built from multiple tables cannot be updated. You can tell if a query is
updatable or not by opening it. If the navigation button to the right with
the * on it is greyed out, you can't edit, add, or delete any records. So
you have to be sure you can edit the recordset for you form.
If you need to update multiple tables, there are two situations to consider.
If the tables are not logically related to each other, then you will
probably want to use a different form for each table. If the tables have
some relationship. For example, you have a table of Clients and a table of
Contacts for each of the Clients, then the Contacts table will need to have a
field that defines which Client the Contact belongs to. This is done by
using the Client's primary key value in a field in the Contact table. This
is known as a foreign key.
In this case, you can use a form/subform. You would use the Client table as
the record source for the form and the Contact table as the record source for
the subform. To use a subform on a form, you put a subform control on the
form. There are 3 important properties for a subform control.
Source Object - This property is the name of the form to use as the subform
Link Master Field(s) - In this case, it would be the Client table's primary
key field.
Link Child Field(s) - In this case, it would be the name of the foreign key
field in the Contact table. That is the field that has the value of the
primary key field in the Client table that defines which Client the Contact
belongs to.
