Shrink-Wrapped or Do-it-yourself Queries

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Feb-2002 >

Shrink-Wrapped or Do-it-yourself Queries


David Cornelius


David Cornelius explains how to use multiple queries, both

canned and custom-built, to build an application using only

one form and a report. You can empower your users to

become more productive and efficient.


EXPERIENCED users and developers of Access

applications build queries to retrieve specific

data. But what about users who aren’t familiar

with developing queries in Access? Or, if the Access

query design features are inaccessible by users, are

users forbidden from generating new or modified

queries without your help?

The database that I’ll show you will provide you

with techniques that you’ll be able to implement in

your database applications. The application includes

predefined queries that are the most common at my

fictional company. However, I’ve assumed that there’s

also a need for users to generate custom queries. Since

query development support was hard to come by after

the initial design phase, there had to be support in

place that would allow the users to generate their

own queries while demanding little SQL knowledge

on the part of users. The only bit of SQL knowledge

that the user needs to understand is the concept of

what “And” and “Greater Than” represent in SQL

Select statements.


Starting out

The main menu that the user first sees upon starting

the application is laid out to present both the canned

query list and the fields that can be queried upon

(see Figure 1). If the user wants to run one of the

predefined queries, he or she can start the query in

one of two ways:

• Select the desired query and then click the Run

Report button.

• Double-click the query to have it fire automatically.

In either case, the query will run and return the

recordset to the form for display.

If the user wants to create a custom query, he or

she fills out the appropriate fields on the form with

the correct parameters and then clicks the Create

Custom View button to start the request. As Figure 2

(on page 12) shows, many of the

fields are combo boxes that list the

data that the user can select as

criteria. The other fields are freeform

fields that will automatically

append an asterisk to the end of

the selection to support wildcard

selection. The design of the custom

generation code doesn’t handle

the logical “OR” situation, only

“AND” logic.

The data returned from

either canned or ad hoc queries

is presented on the same form

since the data returned from either

type of query is the same (see

Figure 3, on page 12). The only

difference is the record source, and

this is controlled by the code

behind both respective buttons.

Before I discuss the data source



Figure 1. Main menu graphic.


Read More Here:

Shrink-Wrapped or Do-it-yourself Queries