Theoretical question

  • Thread starter Thread starter Dennis Snelgrove
  • Start date Start date
D

Dennis Snelgrove

I don't know if this question will make sense, and if it doesn't, then I
won't worry about it.

When running a report, we usually use a query that has fields from more than
one table. That's the basis of a relational database in action. Is it
possible to set the recordsource of a form to the same type of a query,
where we have fields from more than one table? If so, would the
Relationships setting cause new records to be created in the appropriate
tables, etc?

I know, that question doesn't make sense.
 
Dennis,

A query with more that one table, where one or more one-to-many
relationships exist, is not updatable, so you can't make changes or
additions to it. Consequently, a form based on such a query has the same
limitation. The workaround is to use a form with (a) subform(s), so the
main form and each subform is based on one table only, while the links
between them reflect the relationships; in that case, you get full
functionality.
It's different with reports, in that in a report data is only read, so
it works fine in that case.

HTH,
Nikos
 
Dennis said:
I don't know if this question will make sense, and if it doesn't,
then I won't worry about it.

When running a report, we usually use a query that has fields from
more than one table. That's the basis of a relational database in
action. Is it possible to set the recordsource of a form to the same
type of a query, where we have fields from more than one table? If
so, would the Relationships setting cause new records to be created
in the appropriate tables, etc?

I know, that question doesn't make sense.
Click to expand...

The difference is that a report is only reading the data and not trying to
make edits or insertions. For a read-only form you have no more
restrictions on using a query than you do in a report. If you want the form
to do updates and insertions though then things get a lot more restrictive.

While it is possible to use a multi-table query for doing updates, it has to
be constructed "just right" and going beyond two or possibly three tables is
pretty rare. It is easier to include multiple tables if you are only
concerned with edits being written to one of the tables and the others are
just supplying lookup values. Getting edits to go to multiple tables, again
is doable, but it is difficult to get set up correctly.

My general practice and recommendation is to use only one table for editing
and use subforms to provide that capability for more than one table at a
time.
 
Nikos Yannacopoulos said:
Dennis,

A query with more that one table, where one or more one-to-many
relationships exist, is not updatable, so you can't make changes or
additions to it.
Click to expand...

This is not strictly true, Nikos. A multi-table query *can* be
updatable, though there are definitely restrictions on that, as
documented in the help file. A simple query with just two tables joined
one-to-many is usually updatable.
 
{Answering the question I think that you are asking!}

You can base a Form on any Query, including one which references
multiple other Tables and Queries. However, there are quite a lot of
restrictions on the structure of Queries on which a Form that is to be
used for data entry/editing may be based - _Updatable_ Queries. Look
at Help on "updatable queries"

I don't know if this question will make sense, and if it doesn't, then I
won't worry about it.

When running a report, we usually use a query that has fields from more than
one table. That's the basis of a relational database in action. Is it
possible to set the recordsource of a form to the same type of a query,
where we have fields from more than one table? If so, would the
Relationships setting cause new records to be created in the appropriate
tables, etc?

I know, that question doesn't make sense.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Dirk said:
This is not strictly true, Nikos. A multi-table query *can* be
updatable, though there are definitely restrictions on that, as
documented in the help file. A simple query with just two tables joined
one-to-many is usually updatable.
Click to expand...
Thanks for this Dirk, you're correct as always. I guess I was too
dogmatic trying to talk Dennis out of a practice I consider "not
recommended", in that, unless one knows exactly what they're doing, it's
more likely to produce problems, than the desired result.

Regards,
Nikos
 
Nikos Yannacopoulos said:
Thanks for this Dirk, you're correct as always. I guess I was too dogmatic
trying to talk Dennis out of a practice I consider "not recommended", in
that, unless one knows exactly what they're doing, it's more likely to
produce problems, than the desired result.
Click to expand...

I have a query with 7 tables. It is updateable (although it isn't actually
used this way).
 
Nikos Yannacopoulos said:
Thanks for this Dirk, you're correct as always. I guess I was too
dogmatic trying to talk Dennis out of a practice I consider "not
recommended", in that, unless one knows exactly what they're doing,
it's more likely to produce problems, than the desired result.
Click to expand...

I can't fault you for that, Nikos, but you have to remember that
anything you post to a newsgroup is likely to be seen by others than
just the OP, and often they will take it as gospel -- especially when it
comes from one of the more knowledgeable respondents, like you. So it's
best to avoid statements that can easily be misinterpreted. I know I
often have trouble trying to simplify an explanation without making a
statement that is technically false.
 
Joan,

I suppose the "unless one knows exactly what they're doing" part says it
all in your case :-)

Regards,
Nikos
 
Back
Top