Link to home
Start Free TrialLog in
Avatar of Jay Williams
Jay Williams

asked on

Creating a user friendly query and reports interface.

This project will require many filters, queries and reports for 20 or so different departments.  I have a good filter form, but I want to make it easy for the users to find and open the right queries and reports.  My general idea is to create a table of query and report object links and let users select and open them using combo boxes, like so:
User generated imageIs there a way to put a hot link to each object in the combo box (table), or do I need to write some code to execute each one?  Do I need to use different methods for each type of object?  Can I declare the selected objects as a variable and execute each one the same way?
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry for my lousy spelling, ...using a new keyboard...
Avatar of Jay Williams
Jay Williams


Thanks for that, Jeffrey, you really did answer most of my questions, even though I made you shoot in the dark.

The users do need to have both queries and reports, because sometimes they'll have to copy data out to paste in elsewhere.  I'm mostly concerned with getting the results out easily once the filters are set.

My "hot link" question was asking if there was a method that worked like a hyperlink address--a "click and go" inside Access.  Your response with a code sample would indicate that the answer is "no," but the code does look like something that might work.  I'll give it a whirl, and if I get stuck, I'll post what I've got.  Thanks again.
You can certainly create Hyperlinks to objects IN an Access db,
User generated image...however, the hyperlink will only perform the default "Open" command.
In other words, you cannot specify criteria. (or any other option for that matter)

Hope this helps a bit more...

The users do need to have both queries and reports, because sometimes they'll have to copy data out to paste in elsewhere.

Ok, thanks for that explanation...,makes sense...
Your solution was right on--and you read my mind to arrive at it.  I won't even deduct for spelling. ;-) Thanks a bunch!

Thanks Jay...

I do not advise allowing users to open queries.  It is too dangerous since they will almost certainly be updateable.  Instead, I export the queries to Excel using TransferSpreadsheet.

In the table where you list the objects, include a type indicator so you know whether to open a form/report or export a query to Excel.

In most apps, I have a table of reports.  In addition to a name and a description, I have a number of flags for selection criteria.  This of course would change from application to application but each application has some smallish (fewer than 20) number of attributes that are commonly used for filtering.  For each flag, I have three values - Required, Optional, Not Supported.  I use this to hide/show combos on the report form and then as I am building the criteria string, I use the flags to determine what is required.  The other option on the form is how to display the data - report-print, report-preview, report-pdf, query-Excel.  There is a flag in each record to indicate whether an export to Excel is an option and the name of the query in case it is different from the name of the query used by the report.

This scheme is not directly transferable from application to application but more of a template.  I know what I want to do and I have the basics of the controlling form/tables/code.  I just have to customize it for the variables used by each app.
Your flagging strategy is really great stuff, Pat, and I'll be using it.  Thanks for passing it along; you probably anticipated and headed off whole other line of questions, not only from me in this forum, but internally from my users.

As far as the queries access issue, this database is a tool that pulls and processes Enterprise data (that is refreshed daily) from SAP to test, share and track the results of hypothetical design options changes between product lines and models.  It doesn't allow users' write privileges to any critical SAP or tracking data.

Thanks again!  Does EE give points for "value added" comments?
No but thanks for the offer :)
Actually, I won't be using your idea; my assignment was ended today.  I'll file it away, though; there will be another day! :-)
Sorry to hear that.  Good luck.