List and Open Reports through a Combox in a Form (1 Viewer)

vinsavant · Sep 1, 2017

Hello!

I would like to create a functionality in one of my forms that would allow me to list all my reports in a combobox/dropdown list and then allow me to directly open the report upon selecting the desired report name from the list. If possible, I would prefer the report to open in edit mode, in other words, not in presentation view but rather in regular view to allow me to do right click filtering, etc.

I have already created a table to list all my reports. THe table values are:

tblReports
ReportFormID (Autonumber)
ReportFormSelectionName (Text)

Thanks!

isladogs · Sep 1, 2017

The problem with using a table to list all your reports is that you need to keep it up to date.

There is an easier way of getting the report list which gets around this issue. Create a query using the hidden system table MSysObjects. You will need to make system objects visible temporarily so you can see the table in the query designer window
Include the Name and Type fields and filter for Type =-32764.
That strange number is used for reports.

Use the query as your combo box row source ... or if you have room on your form use a listbox instead.

In the after update event of combo or listbox add code to open the report in your preferred view.

Make sure you hide system objects once you've got this working

NOTE for more info on object types In MSysObjects see this link
https://www.access-programmers.co.uk/forums/showthread.php?t=293579

vinsavant · Sep 1, 2017

Thank you for your prompt reply! I was able to create the query and populate the dropdown control using a code I found online.

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name;

Would you please show me how to write the AfterUpdate code? I am not very adept in writing VB.

Thanks!!!

isladogs · Sep 1, 2017

That's exactly the query I meant!

The VBA will be something like this - replace MyComboBox with the name of your control

Code:

Private Sub MyComboBox_AfterUpdate

	DoCmd.OpenReport Me.MyComboBox, acViewReport
End Sub

Not sure which report view you want
The choices are:

Code:

acViewDesign = Design view 
acViewLayout = Layout view 
acViewNormal = (Default) Normal view 
acViewPreview = Print Preview 
acViewReport = Report view

moke123 · Sep 1, 2017

Cant you also use the allReports collection?

Code:

Private Sub ListReports()

    Dim VarRpt As Variant

    For Each VarRpt In CurrentProject.AllReports
        Me.Combo0.AddItem VarRpt.Name & ";"
    Next

End Sub

you can also use an option group to select the view you want.

heres the basics

isladogs · Sep 1, 2017

Hi Moke

Yes you could do it that way as an alternative

isladogs · Sep 1, 2017

You might be interested in the attached item:

This allows you to view all objects in the database.
Depending on the object type, you can choose to open it in design view or run it direct from the form.

NOTE:
1. So you can see the form in action, I have included a number of dummy objects e.g. Table1, Query1, Query 2 etc

2. You will need to copy the following 3 items to use this in your own databases:
- Form frmDatabaseObjects
- Report rptDatabaseObjects
- Table tblSysObjectTypes

3. No additional VBA references are required for this item

4. It will work in 32-bit or 64-bit Access

sxschech · Sep 8, 2017

In general, I also use msysobjects. Would like to point out there is one case where using a table, despite not being dynamic may be of use and that is if you'd like to have a lengthy description associated with it.
For reports:

Code:

rptID, rptName, rptDescription
443, rptSBRQ, Sales by Region Quarterly Review Grouped by Company Name
521, rptSBRN, Sales by Region Grouped by Name - All years

For All Objects, add another col:

Code:

ID, ObjType, ObjName, ObjDescription
443, Report, rptSBRQ, Sales by Region Quarterly Review Grouped by Company Name
521, Report, rptSBRN, Sales by Region Grouped by Name - All years
297,Query, qrySBR, Used for Sales by Region Reports
105,Table, tblSales, Sales data

You could also add cols for other details such as name of person who wrote the report or whether the report is active or not that way if you want to exclude objects from the list you can, since using msysobjects, you'd need to add where clause or join with separate table to filter out items you'd rather not display.

isladogs · Sep 8, 2017

Hi

sxschech said:
In general, I also use msysobjects. Would like to point out there is one case where using a table, despite not being dynamic may be of use and that is if you'd like to have a lengthy description associated with it.
For reports:

Code:

rptID, rptName, rptDescription 443, rptSBRQ, Sales by Region Quarterly Review Grouped by Company Name 521, rptSBRN, Sales by Region Grouped by Name - All years

For All Objects, add another col:

Code:

ID, ObjType, ObjName, ObjDescription 443, Report, rptSBRQ, Sales by Region Quarterly Review Grouped by Company Name 521, Report, rptSBRN, Sales by Region Grouped by Name - All years 297,Query, qrySBR, Used for Sales by Region Reports 105,Table, tblSales, Sales data

You could also add cols for other details such as name of person who wrote the report or whether the report is active or not that way if you want to exclude objects from the list you can, since using msysobjects, you'd need to add where clause or join with separate table to filter out items you'd rather not display.

All true and I can see the potential benefits.
However, you're back with the issue of maintaining an object list which is impractical (at least for me)

MSysObjects also includes other fields such as date created & date modified which could easily be added to my example if required

Similar to your suggestion, I'd like to include the description that can be added to objects in the navigation pane. For example:

These are obviously stored in one of the system tables but I've never been able to work out which table / field.

If that could be retrieved using a query, it would do what you're suggesting without needing to maintain a separate table

If anyone can enlighten me, I'd be very interested to know where it is...!

isladogs · Sep 8, 2017

Just to answer my own question from the last post, the description field is stored as part of the object properties and not in a system table

However, this link may be of interest: https://stackoverflow.com/questions/10607896/ms-access-retrieving-table-description-through-query

Basically, you can retrieve table descriptions using a function

Code:

Public Function GetTableDescr(stTableName As String) As String
On Error Resume Next
GetTableDescr = CurrentDb.TableDefs(stTableName).Properties("Description").Value

On Error GoTo 0
End Function

and for queries, use

Code:

Public Function GetQueryDescr(stQryName As String) As String
On Error Resume Next
If CurrentDb.QueryDefs(stQryName).Properties("Description").Inherited = False Then
    GetQueryDescr = CurrentDb.QueryDefs(stQryName).Properties("Description").Value
End If

On Error GoTo 0

End Function

Then use a query like this for tables:

Code:

SELECT MSysObjects.Name, MSysObjects.datecreate, MSysObjects.dateupdate, GetTableDescr([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6));

NOTE:
type 1 = local table; 4 = linked SQL table; 6 = linked Access/Excel table

I added descriptions to 2 tables and filtered the query like this:

Code:

SELECT MSysObjects.Name, MSysObjects.datecreate, MSysObjects.dateupdate, GetTableDescr([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like "MSys*") AND ((GetTableDescr([Name]))<>'') AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6));

It took a while to run as I have over 300 tables in the test database

Result:

Next I tried doing it with queries:

Code:

SELECT MSysObjects.Name, MSysObjects.datecreate, MSysObjects.dateupdate, GetQueryDescr([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*" And (MSysObjects.Name) Not Like "MSys*") AND ((GetTableDescr([Name]))<>'') AND ((MSysObjects.Type)=5)) ;

Result: My computer froze & I had to reboot...
Admittedly I was testing on a large db of around 1500 queries but for me at least this was unusable in practice

Pity....

UPDATE:
Just retried this on a smaller db and it worked well with no delay

BTW - the queries are read only as they access system tables

sxschech · Sep 8, 2017

I think I tried playing with editing the description through code some time back and encountered an issue so didn't go further with it. Perhaps I'll revisit, since you have found the description area. I think you would still need a table or query to handle objects that were created that you don't want to display in the combo, but want to keep in the database. Examples would be if you have lots of queries, but only want to see the ones you use frequently rather than have to scroll through all the queries. I did click the thanks on your post but at least on my end, I don't see that it showed up, hope you got it.

isladogs · Sep 8, 2017

sxschech said:
I think I tried playing with editing the description through code some time back and encountered an issue so didn't go further with it. Perhaps I'll revisit, since you have found the description area. I think you would still need a table or query to handle objects that were created that you don't want to display in the combo, but want to keep in the database. Examples would be if you have lots of queries, but only want to see the ones you use frequently rather than have to scroll through all the queries. I did click the thanks on your post but at least on my end, I don't see that it showed up, hope you got it.

It depends very much on what you want to do with the object list.
You could use custom groups in the navigation pane if you want to easily find items you use frequently.

Coming back to object descriptions, you could probably use the forms collection to get the description property though I haven't tried that. Similarly for reports.

As for your final comment, all I can say is

Code:

 me.thanks.visible=false

sxschech · Sep 9, 2017

I was able to get the thanks to show up from my home computer. Another work computer quirk like the report that didn't show all the text, but was visible on other computers?

isladogs · Sep 9, 2017

That's weird. Happened to me also ...
Clicked the thumbs button.
Nothing happened except the thanks button disappeared.
Went to another computer
As you can see it worked but thanks button has gone for that post.

Forum glitch rather than 'you' in this case?

sxschech · Sep 10, 2017

The steps you mentioned were exactly my experience. Glad to know it was not my computer after all. I wonder how many thanks went missing and where did they go???

List and Open Reports through a Combox in a Form (1 Viewer)

vinsavant

New member

isladogs

MVP / VIP

vinsavant

New member

isladogs

MVP / VIP

moke123

AWF VIP

Attachments

isladogs

MVP / VIP

isladogs

MVP / VIP

Attachments

sxschech

Registered User.

isladogs

MVP / VIP

Attachments

isladogs

MVP / VIP

Attachments

sxschech

Registered User.

isladogs

MVP / VIP

sxschech

Registered User.

isladogs

MVP / VIP

sxschech

Registered User.

Similar threads

Users who are viewing this thread