figuring out the data source

[dbpcar]

I have to repair a report that is not based on a query. I am not able to figure out the source of the information-example: the report is for specific make and model, but there is not a select option, and there are hundreds of makes and thousands of models. I need to know how just one was selected. Is there some way to produce a map of the data sources besides the report design itself?

 

[tempclerk]

Tools, Analyze, Documenter in Access 2000.

 

[Databaseguy]

1. Create a table called tblSQL. It should have two fields, table & SQL. Make both fields BIG (100 or more characters)

2. Cut & Paste this code

Private Sub listSQL()
On Error Resume Next
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSQL")
'MsgBox dbs.QueryDefs.Count
With rst
    For Each qdf In dbs.QueryDefs
        .AddNew
        .Fields("Table").Value = qdf.Name
        .Fields("SQL").Value = qdf.sql
        .Update
    Next qdf
End With
End Sub

3. Any SQL statement that a report is based upon will show up in your table with a tilde preceding it. Like this: ~sq_ffrm-Report Input Dates



Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[jebry]

Hi!

One quick thought. If the report is opened from a button on a form, you may want to check what macro or event procedure is called by the button's click event and look at the actions or code behind the button. That may tell you how the record source is being limited.

hth
Jeff Bridgham

 

[MichaelRed]

Continuing the theme started by Jeff, If the report is 'gone', look a the entire sequence leading to the report. It probably includes one (or more) form(s). It is also probable that the recordsource for the report is closely related to the recordsource or one or more controlsources along that path. If you are really lucky, some of hte controls will be used as parameter sources for te query, and it (the query) will be instantiated as a querydef in the form.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[dbpcar]

Thanks everyone. I got the problem figured out, and found a few I didn't know about. Another 20 years and I'll be good at this!