What is the best way to feed Form selection criteria to a Query?

[Brad]

We are starting to investigate the use of Outlook to obtain data from various
data sources.

In most situations, we will have a simple “Selection†form in which our
users will enter selection criteria, such as Order_Number, Invoice_Number,
Invoice_Date, etc.
We have experimented with modifying the SQL String in the QueryDefs to plug
in the selected criteria.

This approach works fine, but before we start to build our system we would
like to know if this is the best approach or if there is another way which
more experienced Access users employ rather than modifying the QueryDefs.

Thanks

Brad

 

[Marshall Barton]

We are starting to investigate the use of Outlook to obtain data from various
data sources.

In most situations, we will have a simple “Selection” form in which our
users will enter selection criteria, such as Order_Number, Invoice_Number,
Invoice_Date, etc.
We have experimented with modifying the SQL String in the QueryDefs to plug
in the selected criteria.

This approach works fine, but before we start to build our system we would
like to know if this is the best approach or if there is another way which
more experienced Access users employ rather than modifying the QueryDefs.

I try to avoid modifying objects at runtime. Even though
modifying a QueryDef's SQL property is the least of this
kind of practice, it can still bite your after some time. I
strongly prefer to use non persistent properties to control
the behavior of an application,

In the case of constructing criteria to open a form or
report, I think it's cleaner and a little easier to use the
OpenForm/OpenReport methods' WhereCondition argument.

When I really have to construct the entire SQL statement, I
stuff it into the form/report's RecordSource property
(instead of the record source query's SQL property).

 

[Brad]

Marsh,

Thanks for the help.

I don't quite understand why modifying objects at runtime
(such as QueryDefs) is likely to cause problems in the future. I have seen
other comments along these same lines, so I would guess that doing a lot
of object modifying may cause some sort of corruption. Can this problem
be minimized by regular "Compacts/Repairs"?

I am fairly new to Access and trying to learn as much as I can.
You mentioned that it's cleaner and a little easier to use the
OpenForm/OpenReport methods' WhereCondition argument. This is a dumb
question. How do you set this up?

Thanks Again,

Brad

 

[Marshall Barton]

I don't quite understand why modifying objects at runtime
(such as QueryDefs) is likely to cause problems in the future. I have seen
other comments along these same lines, so I would guess that doing a lot
of object modifying may cause some sort of corruption. Can this problem
be minimized by regular "Compacts/Repairs"?

Any persistent object change will bloat your data base, some
faster that others. That means sooner or later, you will
have to use Compact to reclaim the space occupied by older
copies of the objects (kind of like a disk defrag
operation). Compact/repair should not be done lightly,
especially Compact on Close, because it rearranges and
rewrites every byte in the mdb file. That leads to the
issue of having a robust backup procedure integrated with
the Compact/Repair operation.

Furthermore, writing large blocks of information (i.e. an
object's design) opens a door for corruption to enter the
picture. This means that you backup procedure must be
frequent and very robust.

And, as the dog's dinner on top of that mess, object design
changes can not be done if you ever decide to distribute
your application as an MDE file.

I am fairly new to Access and trying to learn as much as I can.
You mentioned that it's cleaner and a little easier to use the
OpenForm/OpenReport methods' WhereCondition argument. This is a dumb
question. How do you set this up?

VBA Help is actually very good at detailing all the built-in
Access properties and methods. Just search for OpenForm to
see all the details. If you have trouble interpreting the
help topic, come on back with a specific question.

 

[Brad]

Marsh,

Earlier you provided me with some advice which I really appreciate.

In the case of constructing criteria to open a form or
report, I think it's cleaner and a little easier to use the
OpenForm/OpenReport methods' WhereCondition argument.â€

I have done some experimenting with Access VBA code in which I have inserted
a WhereCondition in the DoCmd.OpenReport. This works nicely. Thanks!

'DoCmd.OpenReport "Report1", acViewReport, "", "Cust_ID = 1 ", acNormal



You also wrote…

When I really have to construct the entire SQL statement, I
stuff it into the form/report's RecordSource property
(instead of the record source query's SQL property).â€

I set up a small report and then found the “Record Source†in the “Property
Sheet†for this report. Instead of pointing the Record Source at a Table or
Query, I copied in some SQL. This also worked nicely.

I am afraid that I am still a bit confused, however. The application that
we are building currently obtains data from a remote SQL-Server database via
the use of ODBC and a Pass-Through Query. The QueryDef’s SQL is modified at
execution time to reflect the appropriate “Where†conditions. This works
Okay, but I am concerned because of the number of forum posts that warn
against doing this. I am trying to better understand the alternatives. For
example, if I plug the SQL into the report’s “Record Sourceâ€, I will still
need to change it at execution time (the number of where statements change
depending on the request). Is dynamically changing the SQL in the report’s
Record Source better than changing the SQL in a Query-Def?

This is probably a dumb question, but I am still in a learning mode.

Thanks,

Brad

 

[Marshall Barton]

I set up a small report and then found the “Record Source” in the “Property
Sheet” for this report. Instead of pointing the Record Source at a Table or
Query, I copied in some SQL. This also worked nicely.

I am afraid that I am still a bit confused, however. The application that
we are building currently obtains data from a remote SQL-Server database via
the use of ODBC and a Pass-Through Query. The QueryDef’s SQL is modified at
execution time to reflect the appropriate “Where” conditions. This works
Okay, but I am concerned because of the number of forum posts that warn
against doing this. I am trying to better understand the alternatives. For
example, if I plug the SQL into the report’s “Record Source”, I will still
need to change it at execution time (the number of where statements change
depending on the request). Is dynamically changing the SQL in the report’s
Record Source better than changing the SQL in a Query-Def?

If you set a form/report's RecordSource in the form/report's
Open event, it will not persist (i.e. not saved in the
form/report's design).

The trick to that, when you can not use the WhereCondition
argument, is communicating the information to the
form/report. In some cases that may mean passing the SQL
statement string to the form/report in the OpenForm/Report
OpenArgs argument so the formreport's Open event can use the
line of code:
Me.RecordSource = Me.OpenArgs

Most of the time a form has the needed information and code
in the form can construct the SQL statement and stuff it
into the record source, no problem.

Reports are different in that the only place the record
source can be set is in the Open event. Most of the time
the needed information is in a form and it makes more sense
to construct the SQL statement in the form and pass it to
the report in OpenArgs.