Forms for user to create own query?

[Dean Slindee]

Has anyone developed a generic set of form(s) where a user is empowered to
query on certain fields within a small database (like joining a couple of
tables)?

It seems like this would need to be a series of forms, like so:

1.. form to present the columns available, and user chooses which columns
2.. form to create a Where clause from the columns above, with a textbox
for each column to enter a value match
3.. form to specify the Orderby results columns and sequence
4.. form to display the results (datasheet view)
Thanks,
Dean Slindee

 

[Allen Browne]

Searching for "query by form" should bring you lots of discussion and
examples of what people have suggested.

Realistically, you are not going to create anything that is more generic and
simpler than the graphical query interface in Access. That allows you to
create any combination of fields, tables, joins, criteria, sorting,
grouping, calculated fields, subqueries, reading values from forms, and
specifying properties for de-duplication and formatting.

If that seems to offer too much power for an end user, you will want to
design a form that is more specific, handing specific fields of specific
types from specific tables, and offering specific options (e.g. ranges,
multi-select options.)

To get you started on that, download the sample database from:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The example is simple enough, but the concept can be extended to include a
form with lots of tabs that offer options from fields in related tables.
Typically, your WHERE string then contains subqueries that select the
desired records from the related tables (without duplicating the records.)
Then end result is a very powerful, but specific form that lets the user
find anything they want.

HTH.

 

[Duane Hookom]

There is a query by form applet found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. You can
import a few objects into your mdb to implement the solution. Other than
importing the objects, you only need to create a few "data source" queries
that combine related tables and possibly rename some fields.

 

[Dean Slindee]

Excellent Duane, that's just what I was hoping for....I owe you one (La
Crosse, WI)

 

[Duane Hookom]

Ok, I might just take you up on that! I have relatives down in Holmen and I
had done a lot of contract work in La Crosse (Gund/Luth, Xcel, Duratech,
....) I'm gonna write down your name and slip it in my wallet. Maybe a
Thursday night at the Recovery Room or better yet Oktober Fest...

Cheers
Duane (Eau Claire)

 

[Dean Slindee]

Sounds good, I live 10 blocks from the Recovery Room.

I looked into your QBF project some today and it will do what I need if ....
1. I can drop DAO and use ADO instead (the backend is SQL Server, with .adp
front end)
2. Find another means besides QueryDef's to set the .RowSource for
frmQBFFields (as I don't know that QueryDefs are supported in .adp or ADO.
Let me know if I'm wrong).

Dean S (La Crosse County)

 

[Duane Hookom]

You are on your own with attempting to change the solution for an ADP. I
have considered writing an ASP classic version of QBF but not an ADP.