Report Source

[Michael Conroy]

I have a big picture question regarding the recordsource of a report. In the
past I setup a huge query and most reports run off this query. By huge, I
mean the normalization is reversed and I have the text I need to display on
the report and the number I need to filter it. I build a criteria string from
the users selections in my unbound report form. The final command is this:

DoCmd.OpenReport [Report], acViewNormal, , [Criteria]

I guess my question is, is this the right way to do things? It seems to me
the report output will get slower the more information is entered into the
tables. And that query looks wierd, with almost every table in there.

Is there a way to do a query like a recordset and keep it open until the
user changes reports and I need a different query. Then I can apply the
criteria and then open the report, then change the criteria and open the
report again all without reruning the query? Anyway, it works the way I have
it now, but I was curious if it was the best way to do it. Thanks again.

 

[Hunter57]

Hi Michael,

I don't know if this is what you are looking for, but you can use a
temporary table to hold your query data. Then you can use the table as the
Report Recordsource or create a query based on the table. Here is a Sub that
I use:

Public Sub FillTempTable(strQryName As String, strRptName As String,
strTable As String)
' Created by Patrick Wood 11-10-2007
' Fill a table with a Report's current RecordSource Query Fields
On Error GoTo Err_FillTempTable

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim strSQL As String
Dim qryPauseTime, qryStart, qryFinish

'Clear old data from table
strTable = "tblFieldList"
DoCmd.SetWarnings False
strSQL = "DELETE * FROM " & strTable
DoCmd.RunSQL strSQL

'Allow for time for the table to be cleared
qryPauseTime = 0.9 ' Set duration.
qryStart = Timer ' Set start time.
Do While Timer < qryStart + qryPauseTime
DoEvents ' Give the query time to work.
Loop
qryFinish = Timer ' Set end time.

DoCmd.SetWarnings True

'Fill table with table and field names
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable, dbOpenTable)

Set qdf = dbs.QueryDefs(strQryName)
For Each fld In qdf.Fields
rst.AddNew
rst!RecordSourceName = qdf.Name
rst!FieldList = fld.Name
rst!DataType = fld.Type
rst!Required = fld.Required
rst.Update
Next fld

' Clean up
qdf.Close
rst.Close
dbs.Close
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing

DoCmd.OpenReport strRptName, acViewNormal, , Criteria

Exit_FillTempTable:
Exit Sub

Err_FillTempTable:
MsgBox Err.Description
Resume Exit_FillTempTable

End Sub

You can use this with any Report based on a query.

Patrick Wood
www.advancingsoftware.com
www.churchmanagesoftware.com

I have a big picture question regarding the recordsource of a report. In the
past I setup a huge query and most reports run off this query. By huge, I
mean the normalization is reversed and I have the text I need to display on
the report and the number I need to filter it. I build a criteria string from
the users selections in my unbound report form. The final command is this:

DoCmd.OpenReport [Report], acViewNormal, , [Criteria]

I guess my question is, is this the right way to do things? It seems to me
the report output will get slower the more information is entered into the
tables. And that query looks wierd, with almost every table in there.

Is there a way to do a query like a recordset and keep it open until the
user changes reports and I need a different query. Then I can apply the
criteria and then open the report, then change the criteria and open the
report again all without reruning the query? Anyway, it works the way I have
it now, but I was curious if it was the best way to do it. Thanks again.