Fields list from report at runtime

[Guest]

I am new here, so please let me know if there is a more appropriate newsgroup
for me to post this question.

Reports in Access 2000 and 2002.

I am opening a report at runtime and I need to be able to generate a list of
fields available to the report. This would match what appears when I click
View, Field List in the Design View of a report. I need this to work for
reports whose recordsource contains parameters. The recordsource may contain
the full query string or a saved query reference. I use the following code
to open the report object:

Dim app as New Access.Application
app.OpenCurrentDatabase "C:\northwind.mdb"
app.DoCmd.OpenReport "Employee Sales by Country", acViewDesign

Also, is it possible to retrieve a list of parameters available to the report?

TIA!

 

[Allen Browne]

You can read the names of the Fields and Parameters from the QueryDef that
serves as the RecordSource of the report.

Function ShowQueryFields(strQuery As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)
Debug.Print "Fields:"
For Each fld In qdf.Fields
Debug.Print , fld.Name, fld.Type
Next

Debug.Print "Parameters:"
For Each prm In qdf.Parameters
Debug.Print , prm.Name, prm.Type
Next

Set db = Nothing
End Function

 

[Guest]

Exactly what I needed. Thank you!!

You can read the names of the Fields and Parameters from the QueryDef that
serves as the RecordSource of the report.

Function ShowQueryFields(strQuery As String)
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs(strQuery)
Debug.Print "Fields:"
For Each fld In qdf.Fields
Debug.Print , fld.Name, fld.Type
Next

Debug.Print "Parameters:"
For Each prm In qdf.Parameters
Debug.Print , prm.Name, prm.Type
Next

Set db = Nothing
End Function