Analysing Access Reports

[balachandar]

Hi,
We are in the process of converting access reports into Crystal Reports. In Access , How do I find out the source of Report fields. For example if there is a field called Name, I right click on the field (in Design Mode), Choose Properties and see the column name. But how do I find out Which query is used to populate these fields ?.

Regards
Balachandar Ganesan.

 

[THWatson]

In Design View, on the View menu, check the Report's properties. The Record Source will tell you the table or query that is populating all the fields in the report. Except for calculated fields, that is.

Tom

 

[chrishowell]

Go to "Tools", "Analyze", "Documenter". Select the "Reports" tab then the report that you are interested in. It will give you more information than you want. I recommend against selecting everything in "All Object Types" unless you have a while to let it run (and a lot of printer paper).

chris

 

[CajunCenturion]

Everything you need can be obtained programatically. Below are some pseudo-code snippets which outline the basics.

Sub BuildReport (<Param list>)
Dim TheReport           As Report
Dim TheCntrl            As Control
   
DoCmd.OpenReport &quot;ReportName&quot;, acViewDesign, vbNullString, vbNullString
Set TheReport = Application.Reports(rStr_ReportName)

You can access many of the report properties, specifically, the Query for the Report and Width are shown

ReportQuery = TheReport.RecordSource
ReportWidth = TheReport.Width

Then you can loop through all of the controls on the report

For Each TheCntrl In TheReport.Controls

Then you can evaluate all of the properties of each control. You have the basic properties

   TheCntrl.Name
   TheCntrl.Left
   TheCntrl.Top
   TheCntrl.Width
   TheCntrl.Height
   TheCntrl.BorderStyle

and many others. Use a Watch window to see all of them. You also need to pay attention to a number of the special properties such as the following:

   Select Case (TheCntrl.Section)
      Case acPageHeader, acHeader
      Case acDetail
      Case acPageFooter, acFooter
   End Select

   Select Case (TheCntrl.ControlType)
      Case acLabel
         TheCntrl.Caption
      Case acTextBox
         TheCntrl.ControlSource
         TheCntrl.TextAlign
         TheCntrl.FontName

'(and the other font properties as well)

      Case acSubform

' (A Subreport)

         TheCntrl.SourceObject

'(this is the name of the subreport)

         lCol_SubReports.Add TheCntrl.SourceObject

' This used later on to recurse through all subreports

     Case acLine, acRectangle
   End Select
Next TheCntrl

There are of course many other types of controls that you may have included in your reports. Close the main report and now run through all of the subreports - recursive calling the same routine

DoCmd.Close acReport, rStr_ReportName, acSaveNo
   
For lInt_Idx = 1 To lCol_SubReports.Count
   BuildReport lCol_SubReports.Item(lInt_Idx)

' Recurse through the sub-reports

Next lInt_Idx

In reality, you'll need to get the left and top offset of the subreport and add them to the individual controls for absolute positioning since the subreport control positions are relative to the start of the subreport.


Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[balachandar]

Hi All,
THWatson,CajunCenturion,chrishowell

All your suggestions have been very helpful to me. And I thank you all.

Regards
Balachandar Ganesan.