Please help! (Reports and Queries)

[Mikey C]

Hi Guys

I need to be able to action a number of queries (& also reports) on my
database. Currently I have a large number of queries / reports ready
made so users can just choose the way they want the data sorted.
Obviously it would be impossible to include a query / report for every
possible situation. Many of these queries / reports have been set-up as
parameter queries to automatically filter the results. Additionally I
have included pivot tables which are a great way to sort the financial
data.

Finally! - My question is ...

Is there any way that I could build some kind of form where users could
choose the specific parameters they require to create custom queries /
reports (Using combo boxes)? At the moment, users have to amend
standard queries in design view (most do not know how & is not
particularly user friendly!).

Please be gentle (I'm not an access pro!!)

Appreciate any help you can give

 

[John Spencer]

Quote from Duane Hookom

You might want to consider the Query By Form applet at
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'


***FEATURES***

The DH QBF is a complete query by form applet that can be easily integrated
into any existing Access application. Typically, the functionality provided
by DH QBF can replace many "canned" reports. The developer imports several
forms, tables, a query, and a report from the DH_QBF.mdb, creates some
master queries, and deploys.

The developer creates one or more master queries that join tables, alias
field names, create calculated columns, etc. The users can then select a
master query (datasource) from a drop-down and then select up to 30 fields
from the master query. Users can define sorting and criteria as well as
grouping and totaling. All of this "design" information is stored in two
tables for re-use.

The results of the queries are displayed in a datasheet subform contained in
a main form. The main form has options to send/export the records to print,
Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most
formats allow he user to automatically open the target application. The Word
merge process will open a new Word document and link to the merge fields.

 

[Mikey C]

This applet was just what I needed - fantastic!

However, one more problem ....

I am creating workgroups on the database, to ensure that everyone does
not kill may database. However, for some reason the only user group
that can actually run a query in the applet is Admins. I have even
tried giving full admin rights to all objects to all groups and they
still cannot run the queries. I really need these users to be able to
design, create, run and delete queries but unable to add, edit or
delete data in the main tables.

Each time a user other than admins logs-in they receive the following
error message:

Error in cmdReport_Click

3033: You do not have the necessary permissions to use the 'MSysTables'
object. Have your system administrator or the person who created this
object establish the appropriate permissions for you

(I can't even find the 'MSysTables' object anywhere in the database and
it is definitely not listed in any of the permissions lists!!)

Any help will be much appreciated

 

[John Spencer]

Suggest you repost this as a separate question - Probably in the security
group.

By the way MSysTables are hidden system tables. You can view them by
changing settings under Tools: Options. Generally, no one can change them.

 

[Duane Hookom]

My solution depends on naming your master datasource queries with names like
"<....>". You don't have to do this. You can create a small table:

tblDataSources
====================
QueryName

Add your datasource query names into this table. Then change the Row Source
of Forms!frmQBFSource!cboSource to:

SELECT QueryName FROM tblDataSources ORDER BY QueryName;

I think this will get you at least to your next error message ;-)