I'm working on a form that allows the user to build SQL strings from several options. This is mainly to build queries across several databases and run reports from them. The convinient thing is that each of these databases have identical tables.
Bearing in mind that the avarage user this is aimed for has little to no knowlege of SQL the main function of this is to allow the user to build a custom (if limited) "filter" (changing the where clause) and run that SQL with a pre-designed report "template".
Now, most of that I can do, but I'm having difficulty, due to my lack of VBA knowlege making the code 'inteligent'.
Basically, I have 5 checkboxes for databases that the user selects.
Afterwards there are 10 tables that need to be selected for the database (this is pre-set in a separate table, but lets assume that there are 10 choises). Since all tables are identical I dont need to have the choises change for different databases.
So it should be just a matter of running loops to generate the string (atleast the first part of it, without the where clause).
Unfortunatly I dont know how to write a loop (for i, next i?) that would check if a checkbox is selected and then add the checked tables.
The tables are linked into 1 Mdb file, all 10 names are identical with the exception of the first letter which signifies which database they are from (A,B,C,D,E) making a total of 50 tables (5 sets of 10).
The end result needs to be
SELECT A1.* A2.* ..... (depending on what is chosen) From A1.* A2.* ..... (joins will be predetermined and hard coded)
Another function would then run the Where clause and be called in at the end of each Select. So if databases A and B were selected id need an identical select Unioned to the first one, with all As replaced with Bs.
If anyone can help me out with coding these loops I'd be most gratefull.
Here's a method that offers lots of potential and is easy to expand, but requires a bit of work:
1st: Create 1 front end, with a link to every table you want to be able to query. Also create 3 temporary tables: tblFields, tblSort and tblFilter, each with an AutoNumber and 2 Text Fields.
2nd: Create one query that has all the fields you want the user to use. It will be a basic select query except you will want to mask each field name. The masking is not required, but it creates a "Reference Library" effect that will allow you to put user friendly names onto each field. Let's say the name of the query is qryTheLst. By doing this, you don't have to worry about the joins.
3rd: Create a form.
1. add a list box called lstSource. The RowSource for lstSource will be qryTheList and the RowSourceType will be Fields.
2. add 3 more list boxes, lstFields, lstSort and lstFilter. Set each RowSource to the matching table and pull in the 2 fields for each.
3. Add 3 buttons: cmdField, cmdSort, cmdFilter.
a. The On_Click event for cmdFiled will open tblFields and add the selected field name in lstSource, then requery lstFields.
b. The On_Click event for cmdSort will open tblSort and add the selected field name in lstSource to the first field and "Asc" to the second field, then requery lstSort.
c. cmdFilter is the most complicated: again, when clicked, add the fieldname to tblFilter, but also find out what type of filter they want. I use a fancy tabcontrol based panel that responds to the datatype and provides options, but a simple method is to determine the datatype of the field and then use an InputBox to get the user's value. If it's a text field, enclose the value in single quotes. Enclose the filter sting in parenthesis and add it to the table in the second text field.
d. you can add buttons next to each list box to delete the select item or to change it (the sort could change to “Desc” if you want).
4. Add a "Clear All" button that clears the 3 temp tables and requeries the list boxes.
5. Add an export button: this can be done several ways. Most directly, you have in the 3 temp tables all the components of the SQL string to create a custom query, whose source will be qryTheList. Make sure that you place " AND " between each filter criterion, and place ", " between each field name and sort criterion.
One way to be a bit fancier would to write some code that creates a form, adds each field in tblFileds, then opens it using the sort and filter criteria and exports it to Excel, then closes without saving. Do all this "hidden" and as far as the user sees, the "Export" button simply dumped the desired data into Excel with the correct sort and filter.
Export to Excel tip: the control name becomes the column heading in Excel.
To add/edit the available fields, simply edit the query. To use multiple combinations of the data, use several queries and add a list box to select the appropiate query. There is a lot of fluff you can add to this model to make it real nice for the user.