Display SQL for Recordsource

[nomus1]

Does anyone know of a way to display the report recordsource as a text string using vba code?

I am trying to clean up a database and want to see if the tables and querries are actually being used anywhere.

I would like to be able to look for the name of the table or querry in all reports to see if they are used. I am having difficulty in searching the recordsource and thought if I could display it as a text string then I could search for the name of the table / query.

 

[mac318]

Place the code:
Msgbox Reports!ReportName.Recordsource
in the appropriate place; i.e., Report_Open

mac

 

[nomus1]

This is a case where the computer is taking me literally and not giving me what I need.

holdname = reportname
holdname = "Reports!" & holdname & ".recordsource"

When I try to do something like you suggest,
It reads it as
"Reports!reportname.recordsource" instead of the sql code that this represents.

Any suggestions?

 

[mac318]

The old fashion way, nomus. Rename each query, one by one, and run the code. If it crashes, you need it. I've been there, done that. Let me know if you find a way around the labor.

mac

 

[nomus1]

I had been able to do the controls on forms and reports, as well as the modules, but the record source was being skipped.

We finally figured out the recordsource
I had to set the report name and then call for the recordsourcs

Set rpt = Reports(DcIndx.name)
holdname = rpt.RecordSource
stringfound = ""

If Len(holdname & "&quot > 0 Then
For iLen = 1 To Len(holdname) - (Len(strtofind) - 1)
strCurrChar = Mid(holdname, iLen, Len(strtofind))

If stringfound <> &quot;found&quot; Then
If strCurrChar = strtofind Then
ModuleList = ModuleList & &quot;Report Control,&quot; & DcIndx.name & &quot;: Record Source,&quot;
stringfound = &quot;found&quot;
End If
End If
Next iLen
End If


Thanks for looking into it.

 

[mac318]

Fantastic Nomus. We should have thought of that also. By setting the report, you turned your variable(pointer) into an object which contains the properties you needed. Great work.

mac