User flexibility to drag different fields in report query to excel

[johnseito]

Hello,

I was thinking of ideas on how to give user the untilmate flexibility in the kind of report they want to see in Access. Either by UI with drop down selection, input box to enter info and one other way is to select different fields and different queries to create report.

The drop down and input I understand I can do that, the challenge would be to give user the ability to select any field and query they want to see without it predefined, how can I do this, please advise ?

[June7]

With a great deal of complex coding that modifies query and/or report design properties.

I normally advise not to implement code that routinely modifies db structure. So, I have done this only once out of desperation because requirement was for very dynamic filter criteria. Used QueryDefs to create query and export query to Excel. Another approach uses SQL actions such as DROP, MAKE TABLE, SELECT INTO.

Much easier to give user choices of predefined queries/reports.

[johnseito]

Thanks for letting me know.

[CJ_London]

for what they want to see in a row (i.e. column headings)

one way is to use two listboxes. The first to list the queries the user can select, the second is a multiselect listbox with its rowsource set to the name of the query selected in the first listbox and rowsource type set to field list.

you then have code to generate a query on a query, the code basically modifying the select part of the query to only include those fields selected in the second listbox. To display, the easiest way is to assign the modified code to a querydef. then set a subform sourceobject to display the data 'query.newquerydef' which will display as a datasheet

A very simple example - assumes you have a query called repQry - can be a query of anything since the sql will be over written

and reportable queries available are say

Q1
Q2
Q3

rowsource for first listbox

SELECT [Name] FROM msysObjects WHERE [Name] Like 'Q*' AND [Type]=5

after update event for first listbox

Private Sub lbx1_afterUpdate()
    lbx2.Rowsource=LBox1
End Sub

user selects the fields required and clicks a button called btnShow

Private Sub btnShow_Click()
Dim itm as variant
Dim fStr as string

    for each itm in lbx2.itemsselected
        fstr=fstr & ", [" & lbx2.itemdata(itm) & "]"
    next itm

    subformname.sourceobject=""
    
    currentdb.querydefs("repQry").SQL="SELECT" & mid(fstr,2) & " FROM " & lbx1)

    subformname.sourceobject="Query.repQry"

End Sub

with regards filtering, that gets more complicated as June suggests, but there are plenty of examples on this and other forums as to how that might be achieved - but will get even more complicated if you start to allow users to mix ANDs and ORs in the criteria or they want to combine different queries