Report/Query viewing by colleagues

[wrightyrx7]

Hi all,


Im not an Access wiz but have managed to build quiet a few different queries in Access which are from tables linked to the software that holds all our data.

Im looking for some way to put all these queries in one please for all my colleagues to VIEW ONLY. I just want some sort of really simple dashboard for the person to pick a report they want from different categories and it will export it to Excel for them.

I have thought of building this in Excel because I have some knowledge of VBA in Excel but before i start want to see how you guys would do this.

Thanks in advance.

 

[Minty]

You can create a list box on a form that lists your queries and opens them in a read only view and has the option to export to excel if a tick box is ticked.
I have stolen this code from on here somewhere.

List Box Source;

SELECT MsysObjects.Name, Mid([Name],5) AS QueryName FROM MsysObjects WHERE (((Left([Name],4))="rep_")) ORDER BY MsysObjects.Name;

Simply store your queries with the prefix of rep_ and this will display them.
Add a double click event to open the selected query in read only view.

 

[CJ_London]

1. create a new form
2. add an unbound combo or listbox to list all your queries call it cboChoose - see below
3. add a subform - follow the prompts to add one of your queries and call the subform sfQueryView
4. in your cboChoose control afterupdate event put the following code

sfqueryview.sourceobject="[Query." & cboChoose & "]"

5. to ensure the user can resize the query window, in your form resize event put the following code

 sfqueryview.width=insidewidth-sfqueryview.left-60
 sfqueryview.height=insideheight-sfqueryview.top-60

re cboChoose. If your queries have meaningful names put the following in the cboChoose rowsource property

 SELECT [Name] FROM msysObjects WHERE [TYPE]=5

Note just seen Minty's post - that would work for here instead

 

[wrightyrx7]

Thank you both for your replies.

I think your way might be a little to complicated for me CJ_London haha.

Minty just tried yours and managed to get it to list the queries in the listbox. Now I just need to find the code for Excel.

This has definitely set me off in the right direction.

Thank you both again for your replies

Chris

 

[CJ_London]

I think your way might be a little to complicated for me CJ_London haha.

but less complicated for your client

 

[wrightyrx7]

but less complicated for your client

In that case im going to give it a go

 

[wrightyrx7]

Hi CJ_London,

Trying it your way an i have everything setup as instructed. However when i make a selection from the combobox I get an error - Microsoft cannot find the object 'sfqueryview'

I have double checked that the subform is named sfQueryView. Cannot think of what else it could be.

 

[CJ_London]

try typing

me.sf...

after typing the '.' this should autocomplete like a combobox so you should find the subform name there - and just to check, it is a subform and not a separate form?

 

[wrightyrx7]

Thanks for your reply. Think I have fixed this part now, but when i make my selection the subform does not show the new data...does the subform need refreshing?

 

[CJ_London]

I thought it did it automatically, but if not try

sfqueryview.sourceobject="[Query." & cboChoose & "]"
sfqueryview.requery