Solved

View Report Source Code ??

Posted on 2002-04-06
15
253 Views
Last Modified: 2008-02-26
Hi Gurus

I wam wanting to write a application that opens a access database and searches through all the reports.

for each report i want the application to identify what each one does..

i.e. what tables it uses, sort bys, group bys e.t.c.

However when you open a report, you cannot see any source code.

I thought my only chance was to parse the syntax and use a string compare function to identify keywords and then produce a word document for each report that the owners of the database then can use to identify what each of their reports do.

The reason being...my customer has 150 reports and cannot remember what each one does.

any ideas???
0
Comment
Question by:onegui
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 9

Expert Comment

by:Volibrawl
Comment Utility
How about Tools/Analyze database documentor?
0
 
LVL 9

Expert Comment

by:Volibrawl
Comment Utility
hmmm I guess that does not give you much of what you need.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I've never used report information to show users what they are used for.
Normally a user defines what he needs to see and I build the report.
To build a report I start with making reportlayouts with data so the user can see what's on the report. That's telling him much more as just the table and the sort/grouping on fieldnames he doesn't recognize....

Best action will be to create a print of each report and show that to the user.

Nic;o)
0
 

Author Comment

by:onegui
Comment Utility
HI Nic.

thanks for your advice...
The report information was actually going to be used for other programmers/analysts to look at and since there are 150 plus reports, then this was going to be a useful time saving tool, but it looks like it is impossible to create an application that does this??

cheers onegui
0
 

Author Comment

by:onegui
Comment Utility
HI Nic.

thanks for your advice...
The report information was actually going to be used for other programmers/analysts to look at and since there are 150 plus reports, then this was going to be a useful time saving tool, but it looks like it is impossible to create an application that does this??

cheers onegui
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
You can extract the info you need by accessing the Access container object from VBA code. Just checkout the helpfile for some sample code on containers and the documents stored in them.

But as I stated above, when the fieldnames are "cryptic" to the users(including programmers/analists), this will be of little use.

I normally design applications based on reports the user shows me, so if you want a redesign of the system, I would start with the print of reports with data.
Next try to group them, as your 150 reports will certainly have features in common like orderdata per day/week/month/year or by producttype etc.

Having categorized the reports, I would assess the fields and recreate from them the database structure needed for the application. This is known as the data-driven design approach. Then you're able to judge or the present table design is appropriate.

Nic;o)
0
 
LVL 3

Expert Comment

by:SE081398
Comment Utility
Just adding my 2 cents.

150 reports, wow !! that's a lot.  any way that you could code some logic that would modify existing reports so you don't have to create so many different reports.

you can control grouping, sorting, and new controls all through code and you could use one report to produce 30-40 different outcomes.

I've created logic and used these methods to use one report that produces hundreds of different combinations of outcomes for reporting.  this way I only have one report object but I can produce hundreds of different outputs.

look up createreportcontrol   and you'll see what I mean.

I know this may not address your specific question but you may want to consider it due to the limitation of objects available in Access.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:onegui
Comment Utility
thanks SE and NIC5038 for your replies....

SO NIC5038..if i understand yuo correctly..

Youre saying that i can access the source code that produces each report, but because of its cryptic nature i still wont be able to identify what fields are being used???

I dont think i need to produce a detailed report on what fields are produced as such, but a general overview of the main table accessed, and what type of sortby and groupbys are used??

does this sound any better??

onegui
0
 
LVL 2

Accepted Solution

by:
kiddiec earned 50 total points
Comment Utility
Try this to see if it will give you what you require

Sub ItterateReportDetails()
    Dim obj As AccessObject, dbs As Object, rpt As report
    Set dbs = Application.CurrentProject
    ' Search for open AccessObject objects in AllReports collection.
    For Each obj In dbs.AllReports
        DoCmd.OpenReport obj.Name, acViewDesign
        If obj.IsLoaded = True Then
            ' Print name of obj.
        MsgBox Reports(0).Name
        MsgBox Reports(0).RecordSource
        DoCmd.Close acReport, obj.Name, acSaveNo
        End If
    Next obj
End Sub

Of course the message boxes will need to be changed to debug.print, or even to pront to a file or save the data in a new table
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Good code kiddiec, only there is still the problem of reports based on a query that involves more then one table and for sorting you'll need to access the "ControlSource" property of the "GroupLevel"(s) that is/are possibly present...

Nic;o)
0
 
LVL 9

Expert Comment

by:ornicar
Comment Utility
Nico forgott to mention the eventual sub-reports. You have to browse the controls to check for subreports.
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Good point ornicar !
That triggered the thought that sometimes the recordsource is created dynamically from VBA....

Nic;o)
0
 

Author Comment

by:onegui
Comment Utility
Hi Gurus..

firstly..thanks a bundle for your help!!!

ist question...
The syntax reports(0).recordsource displays the whole select query. so if there is mutiple tables, then they are shown also, so thats fine.

I need to clarify..subreports.
When you create a report that may have mutiple tables and may include many groupings..

i.e. Date..then this creates sub reports correct ??
If then i need to somehow identify what the group by is?

I noticed that reports has a property Orderby though this is not returning any values when used with reports(0).orderby.

Is there anyway of establishing the sortby, groupby and orderbys ??

much appreciated
0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
I already stated:
For the sorting you'll need to access the "ControlSource" property of the "GroupLevel"(s) that is/are possibly present...
The queries OrderBy is "suppressed" by the sort of the grouping.

Subreport(s) can be placed on a report linked to the main report and have their own datasource.

You still will miss the reports that have dynamically generated queries and also the above mentioned Grouplevels can be manipulated by code....

Nic;o)
0
 

Author Comment

by:onegui
Comment Utility
Hi kiddiec

thanks for your syntax

i will now attempt to use your syntax in addition with the control source for identifying any sorts, groups e.t..c

much appreciated

onegui
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now