Form with multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to have a form that can access and update multiple tables. I've
joined the tables and created a query. I can see all the fields and data in
the form, but cannot add or change the data through the form. What am I
doing wrong?

Thanks!!!
 
In general, it is best to write to just one table from a form. Redesign your
interface to use a subform for related data, or a combo for lookup data.

Your query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM
clause.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)

In addition, some fields may be read-only and others writable depending on
the joins in your query.
 
What I did was join the two tables by using the Contract Number field. My
goal was to create a form that can read/write data to 3 different tables ,
where the appearance and functionality to the end user is the same as what we
have today: one form - one table. Since I need too many fields to fit in one
table, am I asking Access to do something it isn't capable of?

Thank you for your help!!!
Dan
 
What I did was join the two tables by using the Contract Number field. My
goal was to create a form that can read/write data to 3 different tables ,
where the appearance and functionality to the end user is the same as what we
have today: one form - one table. Since I need too many fields to fit in one
table, am I asking Access to do something it isn't capable of?
Click to expand...

No; you are asking Access to adapt to an improper table design.

Access is a very powerful program - but using it incorrectly (with a
non-normalized table structure) can overload it and make it fail. If
you work WITH it rather than against it, you'll find that it's quite
capable of meeting your *real life needs*. I'd assert that designing
your table structure to fit a preconcieved form is a minor benefit,
but not a "need"; and that a logical, normalized structure using Forms
and Subforms will (after the first two hours of getting over the "it
doesn't work exactly the same as it used to") be simpler, more
logical, and better for the users.

Could you explain - as I've asked before - what all these scores of
fields are?

John W. Vinson[MVP]
 
Back
Top