Placing Query By Design Grid On Form

  • Thread starter Thread starter James Napolitano
  • Start date Start date
J

James Napolitano

I would like to know if their is a way to place the query
by design grid on a form. I want to give users the ability
to build sql statements without writing sql.
Thanks
 
The technique is called "Query by Form," or QBF.

There are many ways to implement QBF ranging from simply tying a query to a
form all the way to using VBA to construct a SQL statement on the fly.

For a look at one of the versions, see
http://support.microsoft.com/default.aspx?scid=kb;en-us;209645.

I also have a demo in one of my "old" presentations. See "Access SQL and
VBA" at http://www.scobiz.com/Presentations.asp. Although it was for Access
97, it seems to run just fine in Access 2000, 2002, and 2003.

Good luck

Sco
 
Thanks for the reply. I am familiar with query by form and
this is not what I am trying to accomplish. I would like
the user to apply complicated logic (several and's, or's
and so on) to a field and contruct an sql statement on the
fly without having to know sql. Also since the table
schema is updated dynamically (new fields are added as
needed) I cannot have any predefined queries.
I am going to check out your demo.
Thanks.
 
There is a fairly complete QBF "applet" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You
import some forms and tables into your application then create some master
queries.
***NEW FEATURE***
An all Access mail merge feature was added Mar 6, 2004. This allows users to
create simple mailmerge output without using a word processing program such
as
MS Word.
*****************
The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH
QBF can replace many "canned" reports. The developer imports several forms,
tables, a query, and a report from the DH_QBF.mdb, creates some master
queries,
and deploys.

The developer creates one or more master queries that join tables, alias
field
names, create calculated columns, etc. The users can then select a master
query
(datasource) from a drop-down and then select up to 30 fields from the
master
query. Users can define sorting and criteria as well as grouping and
totaling. All of
this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main
form. The main form has options to send/export the records to print, Word
table,
Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats
allow
he user to automatically open the target application. The Word merge process
will
open a new Word document and link to the merge fields.
 
I would like the user to be able to get to it on a form.
The QBE grid is what I need, but I need it in a place
where the user can access it in a runtime environment on a
form. Thanks.
 
Thanks! I will check it out.
-----Original Message-----
There is a fairly complete QBF "applet" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hook om,Duane. You
import some forms and tables into your application then create some master
queries.
***NEW FEATURE***
An all Access mail merge feature was added Mar 6, 2004. This allows users to
create simple mailmerge output without using a word processing program such
as
MS Word.
*****************
The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH
QBF can replace many "canned" reports. The developer imports several forms,
tables, a query, and a report from the DH_QBF.mdb, creates some master
queries,
and deploys.

The developer creates one or more master queries that join tables, alias
field
names, create calculated columns, etc. The users can then select a master
query
(datasource) from a drop-down and then select up to 30 fields from the
master
query. Users can define sorting and criteria as well as grouping and
totaling. All of
this "design" information is stored in two tables for re- use.

The results of the queries are displayed in a datasheet subform contained in
a main
form. The main form has options to send/export the records to print, Word
table,
Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats
allow
he user to automatically open the target application. The Word merge process
will
open a new Word document and link to the merge fields.

--
Duane Hookom
MS Access MVP





.
Click to expand...
 
I checked it out. Unfortunately it lacks some of the
functionality I need. All the criteria in the "applet" are
joined with "And". I need the ability to choose "OR" as
well. i.e. CustomerID "CHOPS" or "AFLKI" I dont believe
this "applet" can do that. Thanks.
 
James Napolitano said:
I checked it out. Unfortunately it lacks some of the
functionality I need. All the criteria in the "applet" are
joined with "And". I need the ability to choose "OR" as
well. i.e. CustomerID "CHOPS" or "AFLKI" I dont believe
this "applet" can do that. Thanks.
Click to expand...

Presumably that utility comes with the source code. Make the changes
yourself to suit the functionality you require.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
James, James, James...

Have you never heard of normalizing and normal form 1?

If your schema is "dynamically" changing, this is an indication of a pretty
serious design flaw.

That aside, what you still need is some kind of "Query by Form." After all,
in the big scheme of things, that's all the grid in the query designer is.
It's just *very* sophisticated with code that is totally inaccessible to
normal human beings.

If you're writing code to change your database schema, writing a
sophisticated QBF should be like falling off of a log. Especially with all
of the samples that everyone has pointed you to. Also, I don't understand
one of your comments to James about ANDs and ORs. One of the samples I
pointed you to has the ability to dynamically change between AND and OR.

Bottom line, before spending any more time on querying, I'd look into the
design of the database first.

Good luck.

Sco
 
Back
Top