Using a Form to filter a Report, Best Practices?

[Kari]

I would like to build a form with several combo boxes to select criteria for
a report (in Access 2007). After searching the discussion boards I have
found two different ways to do this (thank you to all of you who answer
questions!).

1. Create a parameter query that references the fields on my form, then
base my report on that query.
2. Use VB to concatenate the contents of each cell into a SQL string, which
can then be used with DoCmd.OpenReport as the Where clause.

My question is, which method is better? The second seems more complicated
to me, but that may just be because of my inexperience with VB. Is one way
inherently better/faster/more stable than the other? Will one way cause
problems for me down the road that I can’t even imagine? Will future
generations of programmers curse me for giving them clunky code to have to
maintain???

Please give me the benefit of your experience and point me in the right
direction. I’d like to do this right the *first* time.

TIA

Kari

 

[Marshall Barton]

I would like to build a form with several combo boxes to select criteria for
a report (in Access 2007). After searching the discussion boards I have
found two different ways to do this (thank you to all of you who answer
questions!).

1. Create a parameter query that references the fields on my form, then
base my report on that query.
2. Use VB to concatenate the contents of each cell into a SQL string, which
can then be used with DoCmd.OpenReport as the Where clause.

My question is, which method is better? The second seems more complicated
to me, but that may just be because of my inexperience with VB. Is one way
inherently better/faster/more stable than the other? Will one way cause
problems for me down the road that I can’t even imagine? Will future
generations of programmers curse me for giving them clunky code to have to
maintain???

I think the second method is better because the report's
record source query is not dependent on the form. With the
first method, you would have to modify the query if you
should ever change the name of the form or a combo box.

Unfortunately. the second method can not be used to filter
records for a subreport.

 

[Duane Hookom]

If you have read any of my posts, you can tell I prefer the 2nd option. I
find this much more flexible since the report does not rely on the form being
open. Also, this method allows me to build a where condition that includes a
clause like " SomeField IN (' ',' ',' ') " from a multi-select list box. The
code can also ignore controls where the user has made no selection so the
field isn't included in the where condition. When I have lots of possible
criteria, I use code to loop through controls to dynamically build the where
condition.

The only times I use the 1st option is when:
- the report's record source doesn't include some filtering fields
- I want to filter a subreport

I haven't noticed any performance issues using either solution. I think the
2nd option is more stable since it will still work if you change the form
name.

 

[Klatuu]

I don't know that one is any better for a specific technical reason. In
practice, I use a combination of both and in some cases will actually hard
code a filter into the query. Here are how I make those decisions:

Assume you have a table that has Active and InActve records determined by a
boolean field Named ActiveClient. In the report, we want to see only Active
clients. In this case, it is a static condition that will always apply. I
would hard code it into the query as
WHERE ActiveClient = True

Now let's say we only want to include records for the current or selected
year and the user has a combo box to select the year. This can be done
either by referring to the form control from the query or by using the Where
argument of the OpenReport method. In this case, I would probably use the
form control reference method.

Now, we can get very complex with the reporting. Let's say you have
multiple combo and or text boxes the user can use to filter the data by a
date range, a zip code, an order volume, A customber type, etc, etc. This
actually becomes more difficult using form control references. In these
cases, I build the Where string in VBA and use it as the Where argument in
the OpenReport method.

And, in reality it is allowable to combine any or all of the methods.

So, it boils down to selecting the correct tool for the job.

 

[Kari]

Dave, Duane and Marsh,

Thanks for your discussion regarding my question. I especially appreciate
that you each took the time to discuss the pros and cons of each method--that
will help me on future projects as well.

Although I am starting simply, ultimately my filtering needs may be complex,
so I guess I'll go with the VB method this time.

I will keep checking back to see if anyone else has opinions on this matter.

Thanks again,

Kari