When you right click on a 'Report' and select properties, you can enter a 'Description' in a text box for the report. What table are these descriptions located in? I'm sure it's some sort of system table , anybody know?
It is probably stored in the MSysObjects table but not in one of the individual columns that you can get via a query. You would need to use VBA to extract the data from the reports collection.
Thanks Pat, anyway you can get me started in how I would do this?
If I establish a connection first to the appropriate database, then where do I go from here?
...don't really want to edit, just want to display the Report Description with the Report Name. I've populated a combobox in VB with a list of Access Report Names, but that doesn't tell much if I don't also show it's Description.
Thanks, you got me moving in the right direction. Here is what I came up with if anyone is interested:
Code:
Dim db As DAO.Database
Dim intCounter As Integer
Dim rst As DAO.Recordset
Dim strDesc As Variant
Dim sReportName As Variant
Dim lvItem As ListItem
On Error GoTo ER
Screen.MousePointer = vbHourglass
incounter = 0
With lvReports.ColumnHeaders
.Add , , "Report Name", 4800
.Add , , "Description", 5600
End With
Set db = DBEngine.OpenDatabase("\\Ntserver_sql\Access\...)
Set rst = db.OpenRecordset("Select [Name] From [msysobjects] Where [Type] = -32764 AND [Name] NOT LIKE '~*' Order By [Name] DESC")
With rst
.MoveFirst
Do Until .EOF
sReportName = rst!Name
strDesc = db.Containers("Reports")(sReportName).Properties("Description")
Set lvItem = lvReports.ListItems.Add(, , Trim(.Fields("Name")))
lvItem.SubItems(1) = Trim(strDesc)
Set lvItem = Nothing
intCounter = intCounter + 1
.MoveNext
Loop
End With
txtTotal = intCounter
Screen.MousePointer = vbNormal
rst.Close
Set db = Nothing
Exit Sub
ER:
If Err.Number = 3270 Then
strDesc = "No Description Listed"
Resume Next
Else
MsgBox Err.Number & " " & Err.Description ' you have some other error here to deal with
End If
Is there anyway to list the reports caption along with the Name and Description Property to a table?
I can get a query to do this.
First have qryReport (Pat's example) SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;
Then have another query (qryIndividualCaption) SELECT qryReports.Name, [Reports]![rpt1].[Caption] AS rptCAPTION
FROM qryReports
WHERE (((qryReports.Name)=[Reports]![rpt1].[Name]));
The problem is that this requries you to have the name of the report (in this case it's rpt1). Also, the report must be open!
So using this, I guess you could look through the records in the qryReports (setting it as a recordset using DAO) and then run the qryIndividualCaption and create the SQL in code replacing rpt1 with a variable of rst![Name].
You could then use DAO to insert the Name, Description & Caption into a table.
The Application object has properties that return collections of open Microsoft® Access objects. The Reports property returns a reference to the Reports collection that contains all currently open reports. The Forms property returns a reference to the Forms collection that contains all currently open forms. The DataAccessPages property returns a reference to the DataAccessPages collection that contains all currently open data access pages. You specify a member of a collection by using its name or its index value in the collection. You typically use the index value only when iterating through all the members of a collection because the index value of an item can change as items are added to or removed from the collection. For example, the following sample uses the form's name to reference the Open Customers form:
Code:
Dim rstCustomers As ADODB.Recordset
Set rstCustomers = Forms("Customers").Recordset
The next example closes and saves all open data access pages by looping through the DataAccessPages collection:
Code:
For intPageCount = DataAccessPages.Count - 1 To 0 Step -1
DoCmd.Close acDataAccessPage, _
DataAccessPages(intPageCount).Name, acSaveYes
Next intPageCount
The Forms, Reports, and DataAccessPages collections contain only open objects. To determine if an object is open, you can use the IsLoaded property of an item in the AllForms, AllReports, or AllDataAccessPages collections, or you can use the SysCmd method with the acSysCmdGetObjectState constant. You can also use the CurrentView property to determine if a form is open in Design, Form, or Datasheet view or if a data access page is open in Design or Page view. The following procedure uses the SysCmd method and the CurrentView property to determine if a form is open in Form or Datasheet view:
Code:
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Const OBJ_STATE_CLOSED = 0
Const DESIGN_VIEW = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> OBJ_STATE_CLOSED Then
If Forms(strFormName).CurrentView <> DESIGN_VIEW Then
IsLoaded = True
End If
End If
End Function
