Programmatically designing a rpt

[Dave]

I have a report that has a cross tab query as its record source & that cross
tab will have heading. So I have the design of the report carried out
programmatically. My code is below to give you an idea iof what I have
done. However, at run time I need to have a field change color depending on
the result of the query.

My question is - Is there any way to, at design time call a function that
will eventually run at run time depending on the result from the query?

Code is

Public Function PrepareAdminReport() As Boolean

Dim ARecordset As DAO.Recordset
Dim AReport As Report
Dim i As Integer
Dim intNoOfCrossTabs As Integer
Dim AControl As Control

Set ARecordset = CurrentDb.OpenRecordset("qryCrossTab")
Set AReport = CreateReport("", "")

intNoOfFields = ARecordset.Fields.count - 1 'No of cross tabs fields

For i = 0 To intNoXTabs
Set AControl = CreateReportControl(AReport.Name, acTextBox,
acDetail)
With AControl
.Height = 270
.Top = 0
AReport("text" & i).ControlSource =
ARecordset.Fields(i).Name
End With
Next

AReport.Section("detail").Height = 0
AReport.RecordSource = "qryCrossTab"

DoCmd.Close acReport, AReport.Name, acSaveYes

End Function

 

[Duane Hookom]

I think there are better alternatives/solutions for creating reports from
dynamic crosstabs. Check the samples at this link
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

 

[Dave]

Duane

Thanks for that - I have had a look at your solution which appears to be
able to be adapted to what I need to do. In ztblExplanation of
CrossTabXp.mde you refer in the last paragraph to "The Microsoft sample
Solutions.mdb" - do you mean Northwind - or something else - and is so what?

By the way I think your explanation is very clear and would be a good
article - but would suggest a reader would have to have thought a bit about
the problem and understand and use Xtabs to really understand it.

Thanks again
Dave

 

[Duane Hookom]

There is a sample database file "Solutions.mdb" that might be loaded with
your version of Access.