reports and VBA code

[lanelouna]

hello again
i really need your help in this

i know this might sound very silly question, but i don't know much about reports, and vba codingof reports
my question is this:
first: when we create a report using vba, not all sections appears, in fact it is missing report header (page header is there), so how can i specify thati want as well report header and footer
Second: how can i say thati want to create a control in the report header, or in the page header, or in the details section?
for example
i have the title "ctlEtiquetteTitreList_" and i want to put it in report header, so what od i say
third question: i am writing this code, and well the textbox list_Y is repeated 4 times, in the acview mode, how come?
i would really prreciate your responses
thank you in advance
Lina Chebli

Function FDynaReport()
Dim db As Database
Set db = CurrentDb

Dim rprt As Report
Dim ctlEtiquetteTitreList_ As Control
Dim rprtname As String

Set rprt = CreateReport
rprt.RecordSource = "ListY"
rprt.Caption = "List_Y"
rprtname = rprt.Name


Set ctlEtiquetteTitreList_ = CreateReportControl(rprt.Name, acLabel, , "", "", XEtiquette_, YEtiquette_)
With ctlEtiquetteTitreList_
.Width = 1550
.Height = 300
.Name = "List_Y"
.Caption = " List_Y"
End With
DoCmd.Restore
DoCmd.Save
DoCmd.Close
end function

 

[scking]

You create stored reports, not with vba. I know of nobody that creates reports in vba. Since you are in an Access forum I will go out on a limb and say you use the Report Designer. Now I'm sure somebody creates them in vba but not normally.

I can understand the need to possible instantiate a stored report and manage it from within vba but not creating from scratch.

Created on-the-fly
----------------------------------------
Dim rpt as Report
DoCmd.OpenReport ("My Stored Report&quot
Set rpt = Reports("My Stored Report&quot

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[beetee]

I've never created a report with VBA either, although I've created about everything else.

You may want to make a temp report, and examine it's properties using VBA, e.g.

' NOTE: this code will need error handling, but it should get you started.
sub ShowObjectProperties(TheObject as object)
dim CurProp as property
for each curprop in theobject.properties
debug.print curprop.name & " = " & curprop.value
next curprop
end sub

Then, start exploring an existing report object. The header, footer, groups, etc. will be *somewhere* in the object's hierarchy or properties.

 

[lanelouna]

thank you all for your responses
i had Micheal Red suggesting a solution
and his solution was much of a help

here it goes
MichaelRed (Programmer) Feb 21, 2003
hmmmmmmmmmmmmm,

poor planning on MY part. WHY this should be is the MYSTERY


Any way, I sortof made some (rather gross) adjustments, and have the procedure working -at least simplistically- to generate a VERY limited and STANDARD columar report based on a Table type Recordset. I DID also rename it, so you can copy / paste it into your app, and both run it and review what I did.

It is reasonably simplistic, but MAY help in seeing some attributes.


Function basDynRpt(RptSrc As String)

'Michael Red 2/21/2003
'Adapted from Tek-Tips Thread703-481600 by lanelouna

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(RptSrc)

Dim rprt As Report

Dim ctrlLbl As Control
Dim XPos As Single
Dim XIncr As Single
Dim YPos As Single
Dim Idx As Integer
Dim HdrCtrl As Control
Dim DtlCtrl As Control
Dim MyName As String
Dim OldName As String

XIncr = 1444

Set rprt = CreateReport

rprt.RecordSource = RptSrc
MyName = Right(RptSrc, Len(RptSrc) - 3)
rprt.Caption = MyName & " - Report"
OldName = rprt.Name
' rprt.ScaleMode = 5 'Inches

Idx = 0
While Idx < rst.Fields.Count

'Create the Label
Set HdrCtrl = CreateReportControl(rprt.Name, _
acLabel, acPageHeader, &quot;&quot;, _
&quot;&quot;, XPos, YPos)
With HdrCtrl
.Height = 300
.Name = &quot;lbl&quot; & rst.Fields(Idx).Name
.Caption = rst.Fields(Idx).Name
.ForeColor = 8388608
.FontBold = True
.TextAlign = 1
.Width = Len(rprt.Caption) * 120
End With

'Detail Field
Set DtlCtrl = CreateReportControl(rprt.Name, _
acTextBox, acDetail, &quot;&quot;, _
&quot;&quot;, XPos, YPos)
With DtlCtrl
.Width = HdrCtrl.Width
.Height = 300
.Name = &quot;txt&quot; & rst.Fields(Idx).Name
.ControlSource = rst.Fields(Idx).Name
.CanGrow = True
.CanShrink = True
.TextAlign = 1
End With
XPos = XPos + (1.01 * HdrCtrl.Width)

Idx = Idx + 1
Wend

rprt.Section(&quot;Detail&quot.Height = 0

DoCmd.Restore
DoCmd.Save
DoCmd.Close

DoCmd.Rename MyName, acReport, OldName

End Function



MichaelRed
m.red@att.net