multiple tables in one query

[Carrie]

Hi, I have built a form for the user to input data into 4
different tables. The form is tied into the Main Table
through a query (Main Query) which has a primary key RecNo.

Each of the other Tables (I'll call them A,B & C) also has
a RecNo. field and I have put them all in another query
(SaskQuery) and built a subform (Sask_Sub).

I can search for the right record and pull it up, but I
can't enter any data in to the subform - it says the
Recordset is not updatable. I have checked all the
properties and nothing is locked and the Recordset type is
Dynaset.

I think the problem is in the way I have my query set up
because I also can't enter the data in the query. Any
suggestions would be appreciated (I don't know VB at all)
Thanks!

 

[Wayne Morgan]

You have guessed correctly as to where the problem is. If all 3 tables are
in the same query and there is no relationship between the tables, instead
they are all related to the main table on RecNo, then there isn't anything
to let Access know which of the 3 tables to put your data into. This will
result in a non updateable query.

 

[Carrie]

Thank you - I actually managed to make the form updateable
by changing the Recordset to Dynaset(Inconsistent
Updates); however, it only seems to save the information
for TableA (I included the RecNo. on the form) - not the
other 2 tables. If I try to put in the RecNo. for tables
B&C also it says that the RecNo. could refer to more than
one in my SQL statement. I am at a loss for how to make
the form/query update all three tables and keep the data
in the form for viewing as well. Thanks a lot.

 

[Wayne Morgan]

If the 3 tables aren't related to each other, just to the main table, then 3
subforms would probably be in order. This would let you update each of them
since each would refer to only one table.