design: single or multiple queries

[Edward Reid]

This is a question about designing queries and forms in terms of single
vs multiple queries and forms, differing sort order and differing
selection.

I need to response to user inputs with several choices of output.
However, I'm going to format all the outputs identically, just varying
the heading. So I could use the same form for all, and I could use the
same query by varying the selection conditions and sort order.

How would one normally do this? By using several similar forms pointing
to several different queries? Or by manipulating the attributes --
using the WhereCondition of OpenForm and ... well, I haven't figured
out how I would set the OrderBy property of the query, or even if
that's the right thing to do. (I'm figuring I'll have a switchboard
home form.)

But the question is, if the form layout and control bindings are the
same, would you attempt to use a single form and a single query and
alter the selection and ordering? Would you have a form and query for
each case? Something in the middle? (I seriously doubt it's the extreme
of a form and query for each case, but I don't know how feasible vs
confusing it will be to make it all work in one.)

Performance *is* a concern. This database currently has almost 2
million records in the main table, and the DB is about half a gigabyte.
It'll probably grow until it gets close to 2GB or starts showing
problems (that will take a few months), and then I'll have to decide
whether to delete old records, or move to MySQL or SQL Server.

And a couple of related questions:

1) How does the query OrderBy property relate to the Sort specs shown
in query design mode? I expected that the OrderBy property would simply
be ordering specified by the Sort specs, but I don't see the Sort specs
anywhere except in SQL view, and I do *not* see the OrderBy property in
the SQL view.

2) I may want to include some calculated values in the form as
displayed. Am I better off calculating these in the query or in the
form? I suppose it only really matters if I end up needing multiple
forms and/or multiple queries -- if I only need one of each, then I
suppose it doesn't matter much.

Edward

 

[Marshall Barton]

This is a question about designing queries and forms in terms of single
vs multiple queries and forms, differing sort order and differing
selection.

I need to response to user inputs with several choices of output.
However, I'm going to format all the outputs identically, just varying
the heading. So I could use the same form for all, and I could use the
same query by varying the selection conditions and sort order.

How would one normally do this? By using several similar forms pointing
to several different queries? Or by manipulating the attributes --
using the WhereCondition of OpenForm and ... well, I haven't figured
out how I would set the OrderBy property of the query, or even if
that's the right thing to do. (I'm figuring I'll have a switchboard
home form.)

But the question is, if the form layout and control bindings are the
same, would you attempt to use a single form and a single query and
alter the selection and ordering? Would you have a form and query for
each case? Something in the middle? (I seriously doubt it's the extreme
of a form and query for each case, but I don't know how feasible vs
confusing it will be to make it all work in one.)

Performance *is* a concern. This database currently has almost 2
million records in the main table, and the DB is about half a gigabyte.
It'll probably grow until it gets close to 2GB or starts showing
problems (that will take a few months), and then I'll have to decide
whether to delete old records, or move to MySQL or SQL Server.

And a couple of related questions:

1) How does the query OrderBy property relate to the Sort specs shown
in query design mode? I expected that the OrderBy property would simply
be ordering specified by the Sort specs, but I don't see the Sort specs
anywhere except in SQL view, and I do *not* see the OrderBy property in
the SQL view.

2) I may want to include some calculated values in the form as
displayed. Am I better off calculating these in the query or in the
form? I suppose it only really matters if I end up needing multiple
forms and/or multiple queries -- if I only need one of each, then I
suppose it doesn't matter much.

One form and one query! Anything else would cause a lot of
maintenave headaches.

A single set of bound controls really simplifies the problem
so one form is a no brainer.

The preferred method of filtering a form is to use the
OpenForm's WhereCondition argument, so you were on the right
track there.

Using a prebuilt query with the sorting included is not
reall an option. But using the form's OrderBy property is a
handy way to do it. Making a dynamic sort order is slightly
trickier and makes a third alternative a viable approach to
both this and the filtering question - create the form
record source SQL statment on the fly. Setting the OrderBy
property and setting the RecordSource property are nearly
the same amount of work so it more a matter of what you
prefer. The key to these is that the form needs to set it's
own properties as it's opening or the form needs to be open
before you start all this stuff. (Using OpenForm
immediately followed by trying to set its properties can run
into a race condition where the form might not be ready
enough for an outside mechanism to set the properties.) The
easiest way to communicate this to the form is to use
OpenForm's OpenArgs argument to pass the SQL statement (or
OrderBy string) and the data form to retrieve it in its Open
event.

Here's a general outline of the control form's code:

strSQL = "SELECT fld1, fld2, ... " _
& "FROM sometable " _
& "WHERE "
If Not IsNull(txtfldA) Then
strWhere = strWhere & " AND fldA = " & Me.txtfldA
End If
If Not IsNull(txtfldB) Then
strWhere = strWhere & " AND fldB = " & Me.txtfldB
End If
. . .
strSort = " Order By " & Me.txtSortfield
DoCmd.OpenForm "formname", _
OpenArgs:= strSQL & Mid(strWhere, 6) _
& " Order By " & strSort

Then the form can set its own record source using:
Me.RecordSource = Me.OpenArgs
in its Open event procedure.

If you prefer to use the WhereCondition argument, then you
would use most of the code above with:

DoCmd.OpenForm "formname", _
WhereCondition:= Mid(strWhere, 6),
OpenArgs:= strSort

and the form's Open event would be:

Me.OrderBy = Me.OpenArgs
Me.OrderyByOn = True

 

[Guest]

2) It doesn't matter if you do calculated values on the
form or in the query. I tend to use the query, because
it is easier for me to find in the query, but I can imagine
some one else doing all their work on forms, and never
looking at a query. There is no performance benefit
either way..

1) The query Orderby property affects the display of
a query in datasheet view. It also is used as the default
value by the wizard when you design a new report or
form based on a query.

0) I am working on the same application I was working
on 10 years ago. In order to make maintenance and
development easier, we try to minimise the number of objects
we have. So that we don't have to repeat design changes
across multiple objects.

But if all you do is create the object and leave, then it
is easier to just create a new object for everything. You
just copy an object, give it a new name, make a change,
and move on.

There is certainly a point at which single objects become
too complex, but it doesn't sound like you are anywhere
close to that yet.

For you, it's just a balance between the extra work you
do now, or the extra work you might have to do later.

(david)

 

[Edward Reid]

Marshall and David,

Thanks! That is good news. I've always hated code duplication and have
always been one who prefers to spend an hour coding to avoid five
minutes of repetition ;-), so one form/query is certainly where I
wanted to be. Thanks for the advice and clues; I will now dive in.

Splash.

Edward

 

[Marshall Barton]

Marshall and David,

Thanks! That is good news. I've always hated code duplication and have
always been one who prefers to spend an hour coding to avoid five
minutes of repetition ;-), so one form/query is certainly where I
wanted to be. Thanks for the advice and clues; I will now dive in.

Go for it ;-)

My sample code outline probably won't be very useful unless
you can wade through all my fat fingered typos and cut &
paste foopahs. Good luck.

 

[Edward Reid]

Couple little typos ain't nothing much if you learned programming using
an IBM 026 keypunch, with a nonfunctional ribbon so you had to read the
punches to figure out what you typed. ;-) (This summer is the 40th
anniversary of my first computer program. It's so much fun to get old
enough to hassle the kids about walking to school in the snow, ten
miles, uphill both ways. In Florida.)

Edward

 

[Marshall Barton]

Couple little typos ain't nothing much if you learned programming using
an IBM 026 keypunch, with a nonfunctional ribbon so you had to read the
punches to figure out what you typed. ;-) (This summer is the 40th
anniversary of my first computer program. It's so much fun to get old
enough to hassle the kids about walking to school in the snow, ten
miles, uphill both ways. In Florida.)

Edward

Glad you were able to wade through all the snow in Florida
as easily as through my keyboard fumblings ;-)

This month starts my 44th year of programming, so I can
certainly relate to kidding the kids, who could care less.
;-)

 

[Edward Reid]

my keyboard fumblings ...
This month starts my 44th year of programming

So if you're like me, it's less a matter of fat fingers and more a
matter of arthritic DIP joints and "I need new comptuer glasses".

OK, I'm straying OT, but I got to the point that I need to set the
RecordSource for a subform. Can I do that? The subform itself doesn't
have an open event, and apparently the form contained in the subform
does not receive an open event. (I made a Sub linked to the Open event
with a MsgBox call, and the MsgBox didn't happen.) Oh, this relates to
the original topic because I want to use a form both as stand-alone and
as a subform. I thought for a while I could do

Me![subformcontrolname].SourceObject.RecordSource = "whatever"

in the Open procedure for the main form, but then I figured out that
SourceObject is just a string naming the source object, not the source
object itself. A discussion worthy of Lewis Carroll.

Edward

 

[Gary Walter]

my keyboard fumblings ...
This month starts my 44th year of programming

So if you're like me, it's less a matter of fat fingers and more a
matter of arthritic DIP joints and "I need new comptuer glasses".

OK, I'm straying OT, but I got to the point that I need to set the
RecordSource for a subform. Can I do that? The subform itself doesn't
have an open event, and apparently the form contained in the subform
does not receive an open event. (I made a Sub linked to the Open event
with a MsgBox call, and the MsgBox didn't happen.) Oh, this relates to
the original topic because I want to use a form both as stand-alone and
as a subform. I thought for a while I could do

Me![subformcontrolname].SourceObject.RecordSource = "whatever"

in the Open procedure for the main form, but then I figured out that
SourceObject is just a string naming the source object, not the source
object itself. A discussion worthy of Lewis Carroll.

PMFBI

When you add a subform (say "frmSubxxx")
to a main form (say "frmMain"), that subform
exists in a *subform control* which may or may
not have the same name as "frmSubxxx."

If it is the same name (check Properties of
your subform control), then the short answer
for code executed in the main form to set the
recordsource of the subform to strSQL:

Forms!frmMain!frmSubxxx.Form.RecordSource = strSQL
or
Me!frmSubxxx.Form.RecordSource = strSQL

the part above

"Forms!frmMain!frmSubxxx"
(or "Me!frmSubxxx")

gets us to the subform *control*.

Within that control is a Form (the "actual subform")
which has a RecordSource.

Another way of looking at it

===============
Dim ctl As Access.Control
Dim frm As Access.Form

Set ctl = Me!frmSubxxx
Set frm = ctl.Form

frm.RecordSource = strSQL
=================

 

[Marshall Barton]

my keyboard fumblings ...
This month starts my 44th year of programming

So if you're like me, it's less a matter of fat fingers and more a
matter of arthritic DIP joints and "I need new comptuer glasses".

OK, I'm straying OT, but I got to the point that I need to set the
RecordSource for a subform. Can I do that? The subform itself doesn't
have an open event, and apparently the form contained in the subform
does not receive an open event. (I made a Sub linked to the Open event
with a MsgBox call, and the MsgBox didn't happen.) Oh, this relates to
the original topic because I want to use a form both as stand-alone and
as a subform. I thought for a while I could do

Me![subformcontrolname].SourceObject.RecordSource = "whatever"

in the Open procedure for the main form, but then I figured out that
SourceObject is just a string naming the source object, not the source
object itself. A discussion worthy of Lewis Carroll.

What Gary said pretty well sums it up. The key to this is
that the SourceObject is irrelevant (it can be changed on
the fly), so you need to go through the Form property of the
subform control.

Me![subformcontrolname].Form.RecordSource = "whatever"

 

[Edward Reid]

Ah yes, the .Form property ... thank you.

I quickly learned that a large part of dealing with Access is using
properties, but I'm a long way from learning what all those properties
are. Some days I wish I had a map of them that I could paste on the
wall -- much better than trying to find them all from the help files.
Except the map would probably paper my entire house ... so perhaps a
sort of dynamic genealogy chart that I can scroll through on my screen
.... I can dream ...

Edward

 

[Marshall Barton]

Ah yes, the .Form property ... thank you.

I quickly learned that a large part of dealing with Access is using
properties, but I'm a long way from learning what all those properties
are. Some days I wish I had a map of them that I could paste on the
wall -- much better than trying to find them all from the help files.
Except the map would probably paper my entire house ... so perhaps a
sort of dynamic genealogy chart that I can scroll through on my screen
... I can dream ...

I usually go to the Help topic for the object I'm working on
and drop the Properties list at the top of the page. Then I
can scoll through the list until I find one that looks
promising. A click will take me to the page for the
property so I can see if it really is the one I want.

An alternative is to use the VBA window's Object Browser,
which is much closer to the virtual map you are dreaming
about.

 

[david epsom dot com dot au]

have an open event, and apparently the form contained in the subform

does not receive an open event. (I made a Sub linked to the Open event

It works for me. (But I'm using a late bound subform)

the original topic because I want to use a form both as stand-alone
and as a subform. I thought for a while I could do

The subform will have a parent property than you can use. To
avoid handling the exception, I loop through the Forms collection
to check if the form has been opened stand-alone before using the
parent property. I do that in the open event.

(david)

my keyboard fumblings ...
This month starts my 44th year of programming

So if you're like me, it's less a matter of fat fingers and more a
matter of arthritic DIP joints and "I need new comptuer glasses".

OK, I'm straying OT, but I got to the point that I need to set the
RecordSource for a subform. Can I do that? The subform itself doesn't
have an open event, and apparently the form contained in the subform
does not receive an open event. (I made a Sub linked to the Open event
with a MsgBox call, and the MsgBox didn't happen.) Oh, this relates to
the original topic because I want to use a form both as stand-alone and
as a subform. I thought for a while I could do

Me![subformcontrolname].SourceObject.RecordSource = "whatever"

in the Open procedure for the main form, but then I figured out that
SourceObject is just a string naming the source object, not the source
object itself. A discussion worthy of Lewis Carroll.

Edward

 

[Edward Reid]

I usually go to the Help topic for the object I'm working on
and drop the Properties list at the top of the page. Then I
can scoll through the list until I find one that looks
promising. A click will take me to the page for the
property so I can see if it really is the one I want.

I've been severely underutilizing that feature and will try to remember
it.

An alternative is to use the VBA window's Object Browser,
which is much closer to the virtual map you are dreaming
about.

Which is what I've mainly been using. What I miss in the Object Browser
is upward (or to-the-left) links -- what contains an object. That's
often in the help -- but I also find that often the help text refers to
the "applies to" list and the list isn't there.

Anyway, my project is up and usable. Version ... oh let's call it v0.3.
Lots of things could be improved. But it's a developer's tool, and for
now I'm the only user, so it's one of those "if you don't like it, fix
it" things.

Thanks again (to all) for the help and encouragement. ;-)

Edward

 

[Edward Reid]

I usually go to the Help topic for the object I'm working on
and drop the Properties list at the top of the page. Then I
can scoll through the list until I find one that looks
promising. A click will take me to the page for the
property so I can see if it really is the one I want.

I've been severely underutilizing that feature and will try to remember
it.

An alternative is to use the VBA window's Object Browser,
which is much closer to the virtual map you are dreaming
about.

Which is what I've mainly been using. What I miss in the Object Browser
is upward (or to-the-left) links -- what contains an object. That's
often in the help -- but I also find that often the help text refers to
the "applies to" list and the list isn't there.

Anyway, my project is up and usable. Version ... oh let's call it v0.3.
Lots of things could be improved. But it's a developer's tool, and for
now I'm the only user, so it's one of those "if you don't like it, fix
it" things.

Thanks again (to all) for the help and encouragement. ;-)

Edward