How to display available reports in a listbox 1

[Chucklez]

I have a project that allows the user to create new reports, and save them in the .mdb. I now want to show in a listbox any available reports for the user to use. Anyone have any idea on how to accomplish this?

I would like to be able to scan the .mdb, find all the reports, and display them in the listbox. he user will then select which report they want to run by double clicking the listbox, then the report will run.

Thanks in advance for the help.

 

[MIKElaw]

Try this It will show all reports that start with Lrpt and show then in a ListBox strReportList

On Error GoTo Err_OF
Dim db As Database
Dim I As Integer
Dim contr As Container
Dim strReportList$
Dim StrReportName$
Dim Length%

Set db = CurrentDb()
Set contr = db.Containers("Reports&quot

strReportList = ""
For I = 0 To contr.Documents.Count - 1
StrReportName = contr.Documents(I).Name
If Left(StrReportName, 4) = "Lrpt" Then
If strReportList <> &quot;&quot; Then strReportList = strReportList & &quot;;&quot;
Length = Len(StrReportName)
StrReportName = Right(StrReportName, (Length - 4))
strReportList = strReportList & StrReportName
End If
Next I

Me!RptLstBox.RowSource = strReportList

 

[Chucklez]

thanks, this helped alot and it worked fine. The only problem I can think of now is if the user mistakenly names their new report something like: &quot;Daily Sales&quot;. since this dosent have the &quot;lrpt&quot; begining, this bit of code will not find it.

Is there a way to find all reports regardless of their name?

 

[MIKElaw]

strReportList = &quot;&quot;
For I = 0 To contr.Documents.Count - 1
StrReportName = contr.Documents(I).Name
strReportList = strReportList & &quot;;&quot; & StrReportName
Next I


try that

Just remember though it will list ALL reports there not just the ones you want the users to see

Mike

 

[mrgulic]

Here is a little query that should do this eaisly:

SELECT MsysObjects.Name AS Tables, IIf([Type]=1,"Table",IIf([Type]=5,"Query",IIf([Type]=-32768,"Form",IIf([Type]=-32766,"Macro",IIf([Type]=-32761,"Module",IIf([Type]=-32764,"Report","NULL")))))) AS MyType
FROM MsysObjects
WHERE (((MsysObjects.Type)=-32768 Or (MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=-32766 Or (MsysObjects.Type)=-32761 Or (MsysObjects.Type)=-32764) AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys"))
ORDER BY MsysObjects.Name, MsysObjects.Type;

I think that you will find that the output is very nice.

 

[MichaelRed]

and just for the "Hack" of it:

A brief (incomplete and incorrect) module to Translate the Type to a Name:

Public Function basObjectNames(ObjType As Long) As String

    Dim Tmp As String

    Select Case ObjType

        Case Is = 1
            Tmp = "Table"

        Case Is = 5
            Tmp = "Table"

        Case Is = 3
            Tmp = "System Object"

        Case Is = 6
            Tmp = "Linked able"

        Case Is = -32768
            Tmp = "Form"

        Case Is = -32766
            Tmp = "Macro"

        Case Is = -32764
            Tmp = "Report"

        Case Is = -32761
            Tmp = "Module"

        Case Else
            Tmp = "Unknown"

    End Select

    basObjectNames = Tmp

End Function

The incomplete arises from the simple fact that there are more / other object types, buy it is late and I'm not looking them up tonight. The Incorrect is the few I added to Sir Hackstr's list are from my woefully faulty memory. But the concept is just to simpllify the nested immediate IIF's for the elderly and otherwise infirm.

Next, a query to use the above to associate the "TypeName" with the individual Type (Item). I call it "qryDbObjects":

SELECT MSysObjects.Name, MSysObjects.Owner, MSysObjects.Type, basObjectNames([Type]) AS TypeName
FROM MSysObjects
WITH OWNERACCESS OPTION;

(the "With OwnerAccess Option" is often not necessary (for unsecured dbs).

and Finally a simple organizatnal results:

TRANSFORM Max(qrDbObjects.Type) AS MaxOfType
SELECT qrDbObjects.Name
FROM qrDbObjects
GROUP BY qrDbObjects.Name
PIVOT qrDbObjects.TypeName
WITH OWNERACCESS OPTION;

This one I didn't actually name, as it is just 'thrown together' for the moment. Include the Module in a general module, add hte two queries (careful of the name of the first one) and make sure you use the same name in the second one as you gace the first one.

Run The SECOND one.

Look at the Output / Display. If you 'regard" each of the Row Values (fields) as Boolean", the ones with values represent the Object as being of the TypeName in that column. The "UnKnown" column represents the Types which neither AAthe hackster or I researched sufficiently to identify here. Other "errors" in the typing may still exist, at least for the couple I introduced.



MichaelRed