Obtaining a list of reports

[Tim L]

I would like to put a list of the reports in a database into a listbox/combo box so that a user can select from the list to view the report.

I can populate a listbox and carry out all the other actions that I desire to do but how would I go about obtaining a list of reports in a currently open database?

Tim

 

[ghudson]

Use this as the SQL for your combo/list box...

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

 

[Tim L]

Use this as the SQL for your combo/list box...

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

Thanks ghuson, the code works a treat, but then I wouldn't have expected otherwise .

Would it be too much to ask for an explanation of how it works? In particular, what is the <>"~" for and I gather that MSysObjects.Type = -32764 refers to reports? What about queries and other objects, how would I found out what their type numbers are?

Thanks again.

Tim

 

[ghudson]

<>"~" ensures no "~sq_" objects are found. [not sure what they are but they relate to forms]

Just open up the SQL in the design view of the query and remove the "type" criteria and select the "show" option to see all the type numbers available.

If you use standard naming conventions then you will easily be able to see which type numbers correspond to which objects in your db.

Avoid messing with any tables or objects that prefix with "MSys" for those are sytem objects [usually hidden, and for good reason] because you could corrupt your db it you mess with the data or design of a "MSys" object.

 

[The_Doc_Man]

what is the <>"~"

Access creates queries on your behalf when you use the query wizard to build a report or form from a table and don't select all fields. These queries have a funky name that begins with the "~" sign. They are sort of "hidden" but in a bizarre way.

MSysObjects.Type = -32764 refers to reports?

Yup.

Technically, it is a DOCUMENT (documents collection) that represents a report. It ain't a true report unless the document has been opened, too. That is, it is in the documents collection but NOT in the reports collection, 'cause the latter is reserved for open reports.

(Same is true for FORMS, btw. 'tain't a form 'til it's open. 'til then, it's a document - with a different type code than for reports.)

 

[RoyVidar]

The only place I've seen MSysObjects documented, Microsoft warned about the systables being subject of changes between versions.

See: http://support.microsoft.com/default.aspx?scid=kb;en-us;275563, method 2.

They've served me well thru several versions, I use it when developing, but never deploy functionality based on them.

List of reports, I'd never expose my report names to the users, anyway, cause the naming conventions I use puts users off. In stead I build a table containing both the real report name (PK), a "user friendly" report name, perhaps a description, and usually some other interesting properties of the report and perhaps even a related table to handle different scenarios/selections.

 

[RoyVidar]

Nah - they'll still loose hair by seeing my trimmed report names

My issue with intruding, is twofold
1 - do we take Microsofts warnings seriously? I do.
quote linked KB
"Although this method will work with Microsoft Access 2000, it is not a highly recommended method, as the structure of system tables may change within future versions of Access.
2 - as you also stated, one may gain a lot more interessting possiblities using a separate table listing all reports (or at least those the users may select from), in addition to the information having a more userfriendly design. In a lot of projects, the same reports are used for different reporting, with just some minor tweaks (labels, controlsource, recordsource, filters, headings...), then I want to list them separately, even though it is the same "physical" report.

 

[Mile-O]

If you have used a naming standard, you can "trim" your prefix or suffix in the query so the user only sees the rest of the name. Granted it won't have any special characters or embedded spaces but it shouldn't be hard to read.

If I were doing this I'd write probably write a function that goes through the form names, for example, looking for the upper case letters and inserting a space before them so that my names were broken out.

Public Function EditName(ByVal ObjectName As String) As String
    Dim intCounter As Integer
    Dim strChar As String
    For intCounter = 1 To Len(ObjectName)
        strChar = Mid$(ObjectName, intCounter, 1)
        If Asc(strChar) >= 65 And Asc(strChar) <= 90 Then
            EditName = EditName & Chr(32) & strChar
        Else
            EditName = EditName & strChar
        End If
    Next intCounter
    strChar = vbNullString
End Function

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

 

[Tim L]

Listing Access Objects

Thanks to everyone that replied, all very helpful answers.

Tim

 

[RoyVidar]

SJ McAbney, sorry, I don't have any problems with this, and I probably wont have if/when Microsoft decides to change the structures of the systemtables, or if/when I, or the customers decide to upsize to ADP with MSDE or SQL-server either, but then I've started to take Microsofts warning seriously. This is one of the reasons I entered this thread, where ghudson alredy had solved the initial question. Microsoft does not recommend using MSysTables, and I wanted to share that warning. It is not up to me how you or others relate to Microsoft's warning, but I think I've done my part, by sharing it.

If I'd been asked to produce a dynamic list, I'd probably go for method 1 in my link, and probably exposed it to some of the methods you're using, but again, my preference is to create static table(s) with report information.

 

[IfButOnly]

Here is another method I came across. Particular example ignores report name if it starts with sub (for subReport) and as with SJ's solution inserts a space before Capital letters.

-----


Public gsReportList() As String
Public giNumReports As Integer


Sub BuildList()
Dim l_db As Database, l_container As container, ipLoop As Integer
Dim spName As String, spTotArray As Integer, spReport As String

spTotArray = 0
Set l_db = DBEngine.Workspaces(0).Databases(0)
Set l_container = l_db.Containers("Reports")

giNumReports = l_container.documents.Count - 1
For ipLoop = 0 To giNumReports
If UCase(Mid(l_container.documents(ipLoop).Name, 4, 3)) <> "SUB" Then
spTotArray = spTotArray + 1
End If
Next ipLoop
ReDim gsReportList(spTotArray - 1, 2) As String
spTotArray = 0
For ipLoop = 0 To giNumReports
If UCase(Mid(l_container.documents(ipLoop).Name, 4, 3)) <> "SUB" Then
gsReportList(spTotArray, 0) = l_container.documents(ipLoop).Name
spName = l_container.documents(ipLoop).Name
gsReportList(spTotArray, 1) = FullName(spName)
spTotArray = spTotArray + 1
End If
Next ipLoop

giNumReports = spTotArray - 1
End Sub