Dynamic Report

[Brian]

Hello all, I am new to creating a dynamic reports and this is I hope a 1
time need. The table I have below is created dynamically the only
things that are constants would be DBH and FLAG.. the other columns
could be anything and any number of them. My question is I want this to
be reflected on a dynamic report as well.. I have the code below where I
have started but a few questions I have is
1. my recordsource does not estatblish
2. Anyone got an example of how to create the column headers which i am
sure would have to be in a loop and textbox.
3. How to preview the report and then close it without prompt for save



DBH 316 318 743 833 951 Flag
6 2.5 5 0
8 5 7.5 2.5 2.5 0
10 5 5 0
12 7.5 7.5 0
14 5 2.5 2.5 2.5 0
16 2.5 2.5 0
18 12.5 0
20 5 0
22 0
24 2.5 0




Sub NewReport()

Dim rpt As Report
Dim ctlLabel As Control, ctlText As Control
Dim intDataX As Integer, intDataY As Integer
Dim intLabelX As Integer, intLabelY As Integer
intLabelX = 100
intLabelY = 100
intDataX = 1000
intDataY = 100

Set rpt = CreateReport
rpt.RecordSource = "tblStand"
'Open in Design
DoCmd.OpenReport "rpt", acViewDesign
Set ctlText = CreateReportControl("rpt", acTextBox, acDetail, "", "", _
intDataX, intDataY)
ctlText.Name = "txtSpc"
Set ctlLabel = CreateReportControl("rpt", acLabel, , ctlText.Name, _
"TEXTSPC1", intLabelX, intLabelY)
' Restore new report.
DoCmd.Restore
DoCmd.RepaintObject
DoCmd.OpenReport "rpt", acViewPreview

 

[Jeff Boyce]

Brian

It sounds a little like you are trying to recreate the functionality of
Access' report features. If you are going to need any number of fields,
column headers, variable recordsources, etc., why not just use
Access/reports?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

Hello all, I am new to creating a dynamic reports and this is I hope a 1
time need. The table I have below is created dynamically the only
things that are constants would be DBH and FLAG.. the other columns
could be anything and any number of them. My question is I want this to
be reflected on a dynamic report as well.. I have the code below where I
have started but a few questions I have is
1. my recordsource does not estatblish
2. Anyone got an example of how to create the column headers which i am
sure would have to be in a loop and textbox.
3. How to preview the report and then close it without prompt for save

DBH 316 318 743 833 951 Flag
6 2.5 5 0
8 5 7.5 2.5 2.5 0
10 5 5 0
12 7.5 7.5 0
14 5 2.5 2.5 2.5 0
16 2.5 2.5 0
18 12.5 0
20 5 0
22 0
24 2.5 0

Sub NewReport()
Dim rpt As Report
Dim ctlLabel As Control, ctlText As Control
Dim intDataX As Integer, intDataY As Integer
Dim intLabelX As Integer, intLabelY As Integer
intLabelX = 100
intLabelY = 100
intDataX = 1000
intDataY = 100

Set rpt = CreateReport
rpt.RecordSource = "tblStand"
'Open in Design
DoCmd.OpenReport "rpt", acViewDesign
Set ctlText = CreateReportControl("rpt", acTextBox, acDetail, "", "", _
intDataX, intDataY)
ctlText.Name = "txtSpc"
Set ctlLabel = CreateReportControl("rpt", acLabel, , ctlText.Name, _
"TEXTSPC1", intLabelX, intLabelY)
' Restore new report.
DoCmd.Restore
DoCmd.RepaintObject
DoCmd.OpenReport "rpt", acViewPreview

This is a software NO-NO. You should never create or
manipulate the design of heavy duty objects such as forms or
reports in a running application.

Actually it is easier, more efficient and way more reliable
to precreate a report with a sufficient number of controls
and then manipulate them in the report's Open event.

To make the code simpler, the controls should be named with
a constant prefix and a sequential number suffix (e.g.
txtCol1, txtCol2, ... , lblCol1, lblCol2, ... and ...)
The controls should all be invisible and be sized as needed,
but positioned in a pile at the top of their section.

An outline of the Open event procedure would then be
something like:

Dim rs As Recordset
Dim K As Integer
Dim lngTxtX As Long
Dim lngLblX As Long

lngLblX = 100
lngTxtX = 100

Set rs = OpenRecordset(Me.RecordSource, dbOpenSnapshot)

For K = 1 To rs.Fields.Count
Me("txtCol" & K).Visible = True
Me("txtCol" & K).Left = K * lngTxtX
Me("txtCol" & K).ControlSource = rs.Fields(K).Name

Me("lblCol" & K).Visible = True
Me("lblCol" & K).Left = K * lngLblX
Me("lblCol" & K).Caption = rs.Fields(K).Name
Next K

rs.Close : Set rs = Nothing
End Sub

 

[Brian]

I like what you are showing me I just get to the end of it going through
code and I says Item not found in collection on the rs.Fields(K).Name
even though it should have stopped at 7 and I have 8 text fields and labels

 

[Marshall Barton]

My mistake, it should be
. . . rs.Fields(K - 1).Name
in both places where I had rs.Fields(K).Name