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:
Users select desired query or report from drop down lists.Is 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?
Microsoft Access

Avatar of undefined
Last Comment
PatHartman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

sorry for my lousy spelling, ...using a new keyboard...
;-)
Jay Williams

ASKER
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.
Jeffrey Coachman

You can certainly create Hyperlinks to objects IN an Access db,
Hyperlink to an Access Object...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...

Jeff
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Jeffrey Coachman

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...
Jay Williams

ASKER
Your solution was right on--and you read my mind to arrive at it.  I won't even deduct for spelling. ;-) Thanks a bunch!
Jeffrey Coachman

lol

Thanks Jay...
;-)

Jeff
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PatHartman

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.
Jay Williams

ASKER
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?
PatHartman

No but thanks for the offer :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jay Williams

ASKER
Actually, I won't be using your idea; my assignment was ended today.  I'll file it away, though; there will be another day! :-)
PatHartman

Sorry to hear that.  Good luck.