Creating a user friendly query and reports interface.

Jay Williams
Jay Williams used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MIS Liason
Most Valuable Expert 2012
Commented:
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...

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
sorry for my lousy spelling, ...using a new keyboard...
;-)

Author

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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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...

Author

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 Liason
Most Valuable Expert 2012

Commented:
lol

Thanks Jay...
;-)

Jeff
Distinguished Expert 2017

Commented:
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.

Author

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?
Distinguished Expert 2017

Commented:
No but thanks for the offer :)

Author

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! :-)
Distinguished Expert 2017

Commented:
Sorry to hear that.  Good luck.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial