Display query results

[Abay]

Hello ... I have a form designed for accepting parameters as input to
queries. I would like to display the results in the bottom half of the
screen, I guess it would be a different form. After accepting the input
parameters to the various queries I would like to have command buttons
pressed depending on what was chosen .. a.go. Selecting all items and
displaying those by location (command button) or by Company, or country etc.

The results would be displayed on the second half of the screen ... please
excuse my ignorance but I don't know how to display the results as explained
above.

The way I see it I would have different queries performed depending on the
selection criteria .. how to display the results on the 2nd half of the
screen from all queries (the displayed fields would be the same for all),
and still have the criteria showing on the top half.

Apologies if this is not clear, and I would most appreciate a response to
this.

Abay

 

[Tom Wickerath]

Hello Abay,

The technique you are asking about is commonly referred to as Query By Form
(QBF). This is not the same as the QBF (or Query by Selection) that is built
into Access.

I have some examples that you are welcome to download and take a look at.
The first sample, Custom Dialog box, is the simplist QBF technique available.
It involves minimal VBA code, but it also has it's own limitations. This
download includes a Word document written by Michael Hernandez, along with a
sample database that I created based upon his document:

http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip

However, it does not include displaying the results in a subform as you seem
to want. The next download, Elements, includes displaying the search results
in a subform. It demonstrates the technique of allowing one to make multiple
selections with a list box. One can make multiple continuous selections by
pressing the Shift key when selecting, or multiple discontinuous selections
by pressing the Control <Ctrl> key. This sample has been kept as simple as
possible as far as the VBA code goes, to demonstrate how to 'iterate' the
..ItemsSelected property of a list box that has the MultiSelect property set
to either Simple or Extended. It does not allow for other features, such as
editing the records found in the search:

http://www.accessmvp.com/TWickerath/downloads/elements.zip

The next sample, Chap08QBF, is an enhanced version of the QBF form found in
chapter 8 of the book "Access 2000 Power Programming", written by F. Scott
Barker:

http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip

The last sample includes a QBF form based on a cut-down version of the
sample Northwind database that ships with Access 2003. It also includes a
Word document that attempts to explain how the technique works:

http://www.seattleaccess.org/downloads.htm
See the download "Query By Form"
Tom Wickerath, February 12, 2008

The last three samples have in common the following:

1.) Using VBA code to build the WHERE clause of a query on-the-fly. While
you can build other portions of a query on-the-fly, such as the SELECT or
JOIN clauses, doing so increases the complexity of the task in a significant
way. These samples only deal with modifying the WHERE clause.

2.) Displaying the results in the lower half of the form, using a subform
whose recordsource is set dynamically.

In addition, the third and fourth samples allow one to double-click a record
in the subform to open just that record for editing. I left this out of the
second sample, to keep the VBA code as simple as possible.

Finally, Access MVP Armen Stein has a QBF example available here:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
See "Report Selection Techniques"

His sample allows one to pick the criteria for generating a report.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Abay]

Hello Tom ... many thanks for your response, I will check out the options at
work later today and get back to hopefully to report success!

Your help is very much appreciated.

Abay