Opinions needed pleas

[Dave]

I'm creating a database which will have one main form and several
subforms, from which all activities will occur.

I have two choices. To base the form on queries and select statements.
Thus read only and have buttons to bring up the record to edit and
buttons to add new records.

Or, base the form on the tables.

Which of the two is typically the most efficient? Any thoughts
appreciated.

Dave

 

[Tom van Stiphout]

On Wed, 19 Aug 2009 06:23:57 -0700 (PDT), Dave

A (sub-)form based on a query is not necessarily read-only. E.g.:
select myField1, myFIeld2 from myTable
where myField3 = myValue
order by myField4
is updatable and provides benefits over binding to a table. I never
bind a form to a table, because I want that extra level of indirection
where I can make quick changes in what columns I want to use, what
rows, and what sort order.

-Tom.
Microsoft Access MVP

 

[Klatuu]

My preference is to use queries as record sources for forms and reports.

The one problem I have with your design is having one main form and several
sub forms. It will cause your form to load very slowly. The more data a
form (including its subforms, combo boxes, and list boxes) has to fetch, the
slower it will appear to the user.

One way to improve this and still use subforms as you plan is to put each
subform on a tab page. That will hide the subform's until you need to use
them.
In design view, once you have built the subform for testing, remove the
record source.

Now, in the Tab control Change event, test so see which page the user
selected, and populate the subform that is on that page. Note, the first
page returns 0, the second page returns 1, etc.

Private Sub tabSubs()

Select Case Me.tabSubs
Case 0
Me.subOne.Form.RecordSource = "SELECT * FROM tblFoo;"
Case 1
Me.subTwo.Form.RecordSource = "SELECT * FROM tblBozo;"
Case 2
Me.subThree.Form.RecordSource = "SELECT * FROM tblCloth;"
End Select
End Sub

As an example of the increase in performance, I once built a form that had 7
list boxes. It took over a minute to load. I used a similar technique and
reduced it to a couple of seconds.

 

[Dave]

My preference is to use queries as record sources for forms and reports.

The one problem I have with your design is having one main form and several
sub forms.  It will cause your form to load very slowly.  The more data a
form (including its subforms, combo boxes, and list boxes) has to fetch, the
slower it will appear to the user.

One way to improve this and still use subforms as you plan is to put each
subform on a tab page.  That will hide the subform's until you need to use
them.
In design view, once you have built the subform for testing, remove the
record source.

Now, in the Tab control Change event, test so see which page the user
selected, and populate the subform that is on that page.  Note, the first
page returns 0, the second page returns 1, etc.

Private Sub tabSubs()

    Select Case Me.tabSubs
        Case 0
            Me.subOne.Form.RecordSource = "SELECT * FROM tblFoo;"
        Case 1
            Me.subTwo.Form.RecordSource = "SELECT * FROM tblBozo;"
        Case 2
            Me.subThree.Form.RecordSource = "SELECT * FROM tblCloth;"
    End Select
End Sub

As an example of the increase in performance, I once built a form that had 7
list boxes.  It took over a minute to load.  I used a similar technique and
reduced it to a couple of seconds.
--
Dave Hargis, Microsoft Access MVP









- Show quoted text -

Actually Dave, my subforms were going to be on a tab control. I like
your idea about the Tab control change event. Thanks.

If I base the record source of the forms on queries then don't I need
several additional forms just for data entry and editing? Won't this
bloat the database somewhat?

Dave

 

[Armen Stein]

If I base the record source of the forms on queries then don't I need
several additional forms just for data entry and editing? Won't this
bloat the database somewhat?

As Tom said, you can use queries instead of tables for your form's
recordsources. You don't need extra forms for this, the same ones
will work.

Perhaps you're wondering if the extra *queries* will be a problem? Not
really. Queries themselves are very small and don't contribute to
database bloat. You just want to name them carefully so that their
usage is easy to understand, and delete any that are no longer used.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Dave]

As Tom said, you can use queries instead of tables for your form's
recordsources.  You don't need extra forms for this, the same ones
will work.

Perhaps you're wondering if the extra *queries* will be a problem? Not
really.  Queries themselves are very small and don't contribute to
database bloat.  You just want to name them carefully so that their
usage is easy to understand, and delete any that are no longer used.

Armen Stein
Microsoft Access MVPwww.JStreetTech.com

If the Record source is a query, which is the way I'm leaning, then
how do you edit the record from this query?

Dave

 

[Dirk Goldgar]

If the Record source is a query, which is the way I'm leaning, then how do
you edit the record from this query?

It's no different from using a table as the form's recordsource. If the
query is updatable (and most simple, non-totals queries are), then you just
edit the records using the form, as you normally would.

You seem to be under the misapprehension that a query is inherently
uneditable. That's not the case. Some *kinds* of queries are not
updatable: totals queries, union queries, complex queries involving tables
in a one-to-many-to-one relationship. And even the latter can often be
updated in a form, if you set the form's Recordset Type property to "Dynaset
(Inconsistent Updates)".

 

[Dave]

It's no different from using a table as the form's recordsource.  If the
query is updatable (and most simple, non-totals queries are), then you just
edit the records using the form, as you normally would.

You seem to be under the misapprehension that a query is inherently
uneditable.  That's not the case.  Some *kinds* of queries are not
updatable:  totals queries, union queries, complex queries involving tables
in a one-to-many-to-one relationship.  And even the latter can often be
updated in a form, if you set the form's Recordset Type property to "Dynaset
(Inconsistent Updates)".

Oh! Then I must be doing something wrong because I can't type into any
of my fields.I have 'Allow Edits' set to yes.

Dave

 

[Dave]

Oh! Then I must be doing something wrong because I can't type into any
of my fields.I have 'Allow Edits' set to yes.

Dave- Hide quoted text -

- Show quoted text -

Nevermind. Please disregard that last post. I can edit the fields. The
one I chose to experiment with is a text box with an expression. Duh.

 

[Tony Toews [MVP]]

Select Case Me.tabSubs
Case 0
Me.subOne.Form.RecordSource = "SELECT * FROM tblFoo;"
Case 1
Me.subTwo.Form.RecordSource = "SELECT * FROM tblBozo;"
Case 2
Me.subThree.Form.RecordSource = "SELECT * FROM tblCloth;"

I replaced the Case 0, 1, 2 with the following:

Case Me.pagPartsConsumed.PageIndex

where pagPartsConsumed is the name of the tab control page. Thus I
could reorder tabs or whatever and never have to worry about the index
changing.

Tony

 

[David W. Fenton]

I replaced the Case 0, 1, 2 with the following:

Case Me.pagPartsConsumed.PageIndex

where pagPartsConsumed is the name of the tab control page. Thus
I could reorder tabs or whatever and never have to worry about the
index changing.

I just followed up yesterday with a comment on StackOverflow on this
very issue. I tend to do this instead:

Select Case Me!ctlTab.Pages(Me!ctlTab).Name
Case "pgeOnePage"
...
Case "pgeAnotherPage"
...
Case "pgeYetAnotherPage"
...
End Select

I think this is more efficient, because I believe the string values
can be compiled into the code so that the only thing that has to be
resolved at runtime is the name of the currently active page. Your
method requires resolving both the Select Case and then resolving
each of the Case statements in turn until it finds a match.

I'm not imagining that this is going to make a noticeable difference
in performance, since nobody's going to have enough tab pages for
this to make a difference, but I think it's more efficient to do it
this way.