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?
Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
If it were me, I would only let the users pen Reports.
You can provide the report filter criteria, ...this is not so easy to do for Queries...
Just create a report form the query.

I am still a bit confused...
Were are you getting those descriptions from?
Explain "Hot link"?
Do you want o just pen he report or also provide criteria?
Do you need to store a "Friendly" name for the objects?

Can you post a sample db of what you have (working) already and explain exactly what you need?

Ifl you need is a way to list Reports and queries then use a listbox.
Create a 2 column listbox with a rowsource of:
SELECT msysobjects.Name, msysobjects.Type
FROM msysobjects
WHERE (Left$([Name],1)<>"~") AND (((msysobjects.Type)=5)) OR (((msysobjects.Type)=-32764));

Set the code on the after update event to be something like this:
Private Sub List0_AfterUpdate()
'Query Selected
If Me.List0.Column(1) = 5 Then
    DoCmd.OpenQuery Me.List0
'Report Selected
ElseIf Me.List0.Column(1) = -32764 Then
    DoCmd.OpenReport Me.List0, acViewPreview
End If
End Sub

Open in new window

But again, we really need to see a sample db of what you have now, ...then explain, in detail, exactly what you are looking to do...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
sorry for my lousy spelling, ...using a new keyboard...
Jay WilliamsOwnerAuthor Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
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...

Jeffrey CoachmanMIS LiasonCommented:
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 WilliamsOwnerAuthor Commented:
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 CoachmanMIS LiasonCommented:

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.
Jay WilliamsOwnerAuthor Commented:
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 :)
Jay WilliamsOwnerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.