Build query from form (1 Viewer)

[tmyers]

I think I am wording my searches poorly so hopefully someone can point me in the right direction on where to read the how to and such.

I want to make a form that will facilitate the design/input for a query. More or less let the user select a table, fields and enter criteria all from a form rather then allowing them to use the query designer. This will be for SELECT queries only as I only want them able to find the info they want, not edit it.

Any reading material would be greatly appreciated

 

[Gasman]

A select quèry can be edited?
How is a user meant to know where each item of data is stored?

 

[tmyers]

Gasman, I am basing this idea off something my work recently introduced for our sales DB. Its a fully interactable SELECT query builder where you can select the table and start setting up which fields you want and what parameters you wish to use but you do have to have at least some idea of how it is structured so you can find the correct table and fields and such. It made me think that it would be neat to have something like that in my own Access DB but i havent had much luck with my Google Fu.

My Access app also only has 4-6 tables that would even be available to use so much simpler than the above and everything is easier to find.

 

[Minty]

This is doable but takes some work.

You'll need to select a table or pre-writtten general look-up queries from a list.
You'll need to then list the available fields - possibly in a form.
Then you'll need criteria entry. This would possibly best be done in the same form as the fields are listed in.
I would hide and make visible various text boxes, dates from and to for date fields, maybe Lower and Upper boundaries for number fields and a single text box for text fields.
You would need "And" & "OR" options at the end of each criteria line.

Then some ninja code to tie it all together in a SQL expression to view in a datasheet query - read only.

I did see an example somewhere but can't reference/find it.

 

[CJ_London]

so this is to build a query on a single table? not multiple tables?.

To select a table, use a combo with a rowsource something like

SELECT [Name] FROM msysObjects WHERE [Type] IN (6,4) AND Flags Not IN (0,1,8,-2146828288,-2147287040) ORDER BY [Name]

Use that to populate a multiselect listbox with the field names

Criteria a bigger problem - OK for =<> etc, but start throwing AND's and OR's....

Other potential problem - the need to aggregate data and perhaps sort as well

but you do have to have at least some idea of how it is structured so you can find the correct table and fields and such.

using the above, providing your tables and fields have meaningful names, should not be a problem. But if they are looking at orders and want the customer name rather than the PK, you are into multiple tables.

However you could build some query 'views' to use instead of a table.

Or you can build a view by looping through the tables (and/or select queries) and their field names to get something like this

 

[The_Doc_Man]

In essence, what you describe is like re-inventing the wheel except you don't want to let users see everything, just selected things.

Theoretically, you could start from MSysObjects, a system table that is usually not visible. However, even touching this table can be fraught with peril, as any change made to MSysObjects that DIDN'T go through the preferred Access interface could possibly break your DB on the spot and little or no chance of recovery. It is incredibly dangerous to get into. I've got 20 years of experience with Access and I have no interest in stepping into that particular can of worms.

I suspect that you would do FAR better if instead of going through the back door of MSysObjects, you should decide ahead of time what tables and fields you will allow your users to see and make a list that DIDN'T come from delving into system tables. You can also try looking at stepping through the AllTables collection and, for each table, step through the Fields collection. Which means that for reading, you would look at topics of Access Collections and the AllTables collection and the Fields collection to make your lists of things that users can select. Which means you would probably have to make a cascading combo box setup where your list of tables is the first combo and your list of fields (grouped by tables in which they reside) would be the second level of the cascade.

Your project will allow selection, but the question will then become whether your users need to filter stuff, because building a WHERE clause can be... interesting. You will find out why a query built using the query design grid has so MANY frickin' parentheses when you switch to SQL view.

Here's how you find AllTables:

CurrentData.AllTables property (Access)

Office VBA reference topic

docs.microsoft.com

You will need to be able to find out about the structure of things, so a LOT of browsing in this topic and its links will help a lot.

Access object model for Visual Basic for Applications (VBA)

This section of the Access VBA Reference contains documentation for all the objects, properties, methods, and events contained in the Access object model.

docs.microsoft.com

Start from the object model and then use the list of topics on the left to locate specific topics of interest.

I wish you luck in this. Over 20 years ago we started doing something similar for our ORACLE database of U.S. Navy Reserve personnel but things got complex so fast that we had to scrap it. Again, the goal was to allow users to select the fields they wanted from the tables they wanted without giving them a view into the internal structure.

The biggest problem was with users who suggested, then requested, and then finally DEMANDED more. But of course, the problem for us was that it was a personnel database and a lot of what they wanted was covered by the Privacy Act or by provisions of HIPAA and we could not allow wide-open access without going through proper channels. Going through MSysObjects is a technical can of worms, but giving users a little bit of what they want is a PROCEDURAL can of worms and a lifetime guarantee of employment but at the same time a dead end for your career because the folks who manage the system won't be able to let you go.

 

[tmyers]

Seems this may be a bit more complex then I thought it would be.
It might be easier to prebuild queries then have a form that would allow tweaks to it, such as which fields are visiable, criteria, and what aggregates to use. Even that seems complex now though. Maybe I am getting over my head a bit here

 

[CJ_London]

any change made to MSysObjects that DIDN'T go through the preferred Access interface could possibly break your DB

don't think a simple select query for a combo should be a problem, never has for me.... Besides which such a select query is not editable, and you can't add or delete records. Same applies for any of the critical tables. So Access does go some way to protect anything critical.

 

[GK in the UK]

Wow. You guys have just described what I'm polishing off right now. tmyers, I have more or less exactly what you want and I’m preparing it for upload here (some days, a week, maybe). However it builds from queries, not tables, so you would build a query for each table and use that instead; you could build a joined query across multiple tables and it will do aggregate queries, sub queries and has sorting options. The advantage is that your base query can be built to exclude stuff that you don't want seen. Then the end-user can set criteria on any field returned by the query - they can't by-pass the base conditions. More or less like Minty describes. Security-wise, similar to what Doc_Man is describing. And as Minty also suggests, it was a LOT of work, experimentation and testing (not so long since I was an Access beginner).

I've just got to get it packaged for upload with the required library modules, make sure it all works and with implementation instructions (it actually does a bit more than described here)

 

[Gasman]

Make tmyers a BETA tester?

 

[Gasman]

Wow. You guys have just described what I'm polishing off right now. tmyers, I have more or less exactly what you want and I’m preparing it for upload here (some days, a week, maybe). However it builds from queries, not tables, so you would build a query for each table and use that instead; you could build a joined query across multiple tables and it will do aggregate queries, sub queries and has sorting options. The advantage is that your base query can be built to exclude stuff that you don't want seen. Then the end-user can set criteria on any field returned by the query - they can't by-pass the base conditions. More or less like Minty describes. Security-wise, similar to what Doc_Man is describing. And as Minty also suggests, it was a LOT of work, experimentation and testing (not so long since I was an Access beginner).

I've just got to get it packaged for upload with the required library modules, make sure it all works and with implementation instructions (it actually does a bit more than described here)

Sounds like a great candidate for the DB sample section.