Using sub-forms vs queries

  • Thread starter Thread starter Mishanya
  • Start date Start date
M

Mishanya

I'm building my first DB.
So far, every time when it comes to design a new form (either for entering,
searching or for reading-only purposes) wish contains columns from different
related tables, I manage to build it as forms with sub-forms.
Still, from most examples or questions here in the forum, I realise, that
forms are mainly based on queries. So I get rather annoying feeling, that I
do something wrong or inefficial, due to my Access ignorance.
So, what are the sufficient and the obligatory conditions for puting query
under the table?
 
There is nothing necessarily *wrong* with using a table as the recordsource
for a form, but queries are preferred for a few reasons;

1) It is much easier to sort records in a query in various ways

2) If needed, you can add calculated fields to a query

3) In most cases you don't need (or want) to pull the entire recordset
every time you open the form. With a query, you can use criteria to
limit the number of records returned.
 
I'm building my first DB.
So far, every time when it comes to design a new form (either for entering,
searching or for reading-only purposes) wish contains columns from different
related tables, I manage to build it as forms with sub-forms.
Still, from most examples or questions here in the forum, I realise, that
forms are mainly based on queries. So I get rather annoying feeling, that I
do something wrong or inefficial, due to my Access ignorance.
So, what are the sufficient and the obligatory conditions for puting query
under the table?
Click to expand...

Well... yes and no. Forms and subforms are indeed the preferred way to handle
one to many relationships. It's occasionally useful to base a form on a
multitable query, but generally that's more hassle than benefit.

It *is* usually beneficial to base a Form on a Query selecting a subset of the
records in the table, if there is a logical way to do so, just for efficiency;
you don't usually need to pull the entire table into the form, just those
records that you're interested in seeing. But this does NOT imply that you
should create a Grand Master Query joining half a dozen tables as the
recordsource for a form!
 
Got it. Thanks, Sean!

Beetle said:
There is nothing necessarily *wrong* with using a table as the recordsource
for a form, but queries are preferred for a few reasons;

1) It is much easier to sort records in a query in various ways

2) If needed, you can add calculated fields to a query

3) In most cases you don't need (or want) to pull the entire recordset
every time you open the form. With a query, you can use criteria to
limit the number of records returned.
Click to expand...
 
So my way is OK as long as it works.
Thank You!

John W. Vinson said:
Well... yes and no. Forms and subforms are indeed the preferred way to handle
one to many relationships. It's occasionally useful to base a form on a
multitable query, but generally that's more hassle than benefit.

It *is* usually beneficial to base a Form on a Query selecting a subset of the
records in the table, if there is a logical way to do so, just for efficiency;
you don't usually need to pull the entire table into the form, just those
records that you're interested in seeing. But this does NOT imply that you
should create a Grand Master Query joining half a dozen tables as the
recordsource for a form!
Click to expand...
 
Back
Top