using vbA code to generate reports 1

[lanelouna]

hello everybody
if anyone can give m a push in this task that would be great.
i have been looking for a vba code, an exemple code, of creating reports based on results of queries, since i don't know how, andi can't find any.
i know that the reports i will be creating would be based on a query, but how can i say that i want to post all fields of the table (result of a query)(knowing that the fields are not always the same)(something like *)
i hope my questionis not ambiguous
if you have a sample code that would be great
thank you

For i = 0 To classStep
classLetter = toutesClasses(i)
DoCmd.OpenReport "List_" & classLetter & " ", acViewDesign
Next i

Lina Chebli

 

[MichaelRed]

hmmmmmmmmmmmmm,

The snippet of code you posted doesn't create reports. It is at least close to opening EXISTING reports with names in the format of [List_I, where I, is your loop variable.

A few observations:

[tab]the snippet does not show and declarations
[tab]the array variable toutesClasses is used without assignment (or declaration)
[tab]classstep used only as the loop index

overall grade is "Shakesperian"

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[lanelouna]

Micheal Red hello, hello all
i really wish you can help me with this
here is it i have been trying to code this all day long
i understand a bit better how it is done
buti have several question
i am gonna ask you three
here is my code
and the questions will come after
Option Compare Database


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

Dim rprt As Report

Dim ctlEtiquetteTitreList_ As Control
Dim XEtiquetteTitreList_ As Integer,
Dim YEtiquetteTitreList_ As Integer
Dim Etiquette_ As Control, ctlText_ As Control
Dim XEtiquette_ As Integer, YEtiquette_ As Integer
Dim XctlText_ As Integer, YctlText_ As Integer
Dim rprtname As String

XEtiquetteTitreList_ = 100
YEtiquetteTitreList_ = 100
XEtiquette_ = 100
YEtiquette_ = 700
XctlText_ = 100
YctlText_ = 100

Set rprt = CreateReport

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


Set ctlEtiquetteTitreList_ = CreateReportControl(rprt.Name, acLabel, acPageHeader, "", _
"", XEtiquetteTitreList_, YEtiquetteTitreList_)
With ctlEtiquetteTitreList_
.Width = 1550
.Height = 300
.Name = "List_Y"
.Caption = " List_Y"
End With

Set Etiquette_ = CreateReportControl(rprt.Name, acLabel, acPageHeader, "", _
"", XEtiquette_, YEtiquette_)
With Etiquette_
.Width = 1550
.Height = 300
.Name = "ORI_Étiquette"
.Caption = " ORI"
.ForeColor = 8388608
.FontBold = True
.TextAlign = 1
End With

Set ctlText_ = CreateReportControl(rprt.Name, acTextBox, acDetail, "", _
"", XctlText_, YctlText_)
With ctlText_
.Width = 1550
.Height = 300
.Name = "ORI"
.ControlSource = "ORI"
.TextAlign = 1
End With

DoCmd.Restore
DoCmd.Save
DoCmd.Close
end function

one: i am unable to control the name of the report, it saves it as "etat1" or "etat2".... (access in french)
how can i save it differently?

two: i want the title to be placed in the Header (header of the report, or acheader, not in the pageheader) however when i create the report this way, it only has the: pageheader, detail section , and pagefooter, and no reportheader, or report foooter, how can i say that iwant to have a report header, so that i am able to say something like this:

Set ctlEtiquetteTitreList_ = CreateReportControl(rprt.Name, acLabel, acHeader, "", _
"", XEtiquetteTitreList_, YEtiquetteTitreList_)


three: in the detail section, items are repeated 4 times, can you tell me why

i am sorry, so many questions, but i am trying to understand how it works, thank you
i would really appreciate your help Micheal, and anyone who volonteers
thanks

Lina chebli

 

[lanelouna]

about the third question, i know now why, i amsorry for asking it even, but the others, i still don' know how to do it, i hope you can help me
thank you
Lina

 

[MichaelRed]

hmmmmmmmmmmmm,

Quite a bit of progress, I would think that at the rate you are proceeding, my humble efforts will lag behind your accomplisments. But a small step:

one: i am unable to control the name of the report, it saves it as "etat1" or "etat2".... (access in french)
how can i save it differently?

Just ASSIGN it. You use "rprtname = rprt.Name", which ACCEPTS the default value presented by Ms. A. (Report | etat) & I. Make it something like:

rprtname = The Name I Want
____________________________________________________________
two: i want the title to be placed in the Header (header of the report, or acheader, not in the pageheader) however when i create the report this way, it only has the: pageheader, detail section , and pagefooter, and no reportheader, or report foooter, how can i say that iwant to have a report header, so that i am able to say something like this:

AFAIK, you cannot include (or exclude) a report header / footer via code - at least not in the sense of changing the existance of the section. You CAN create a "Template" report which has the sections had create your new report based on the template. I haven't tried this, but it is all I know re the dynamic setup. To be the MOST general, I would create the template with all the sections which might ever be desired and simply hide or show them as desired / necessary in the detailed manipulation of the content.

____________________________________________________
three: in the detail section, items are repeated 4 times, can you tell me why

In Short, no. With a minimally modified version of your code, I generate a (rather poorly formatted) report, with the correct number of fields which appear to properly connected to the table. Perhaps you list (recordsource) includes for items and you are just seeing the seperate instances of the item?

I ASS U ME there is MUCH more to be done to complete your exercise, but a few items MIGHT help:

In the detail section, at least, place the control TOP at zero. When all (detail Section Controls) are in created, set the (detail) section Height to zero. This will "shrink" the height and make viewing / reviewing the report much easier.






MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[lanelouna]

hello Micheal
thank you for your help
well i will try to create this ttemplate report, i will see if it works, but concerning he nomenclature thing, well it doesn't work, because we can' t change the name of a report, when t is in design view
if you test it you will see
thanks again

Lina

 

[MichaelRed]

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

Searching for employment in all the wrong places

 

[lanelouna]

MichealRed, you are da best!
thank you a million for the code
it saved me lots of time especially with your way of reading the fields of the recordset
if i could give ou 5 stars i would
thanks

Lina