Create a report on the fly 1

[MrMajik]

Can I create an entire report using VisualBasic? I want to be able to add labels and text fields as needed to the report.

An example of creating a label and text field would be a great help.

Thank you.

 

[MichaelRed]

thread703-481600
MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MrMajik]

MichaelRed;

This is what I am looking for! Simple and effective. I can easily modify this to meet the needs of my project once I get the little nasties out of the way

Here are a couple issues I don't understand with this:

1. I am using the ADODB method. Did I properly convert your code from DAO to ADO?

2. Close to the bottom of the code on this line:
DoCmd.Save acReport, OldName
I get a runtime error that says,
Run-time error '2501':
The Save action was canceled.

Thus, this line fails, too:
DoCmd.Rename MyName, acReport, OldName

However, when I close Access it asks me if I want to save the report. Hmm...

Thank you.
MrMajik


I modified the example you posted to meet the needs of the report.

Public Function basDynRpt()
Dim cnn As New ADODB.Connection
Dim rstAssets As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.CurrentProject.Path & "\Dit.mdb;"
rstAssets.Open "SELECT * FROM tblAssets", cnn, adOpenKeyset, adLockReadOnly ' adLockOptimistic
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 = tblAgency
MyName = "rptTestReport"
rprt.Caption = MyName & " - Report"
OldName = rprt.Name
' rprt.ScaleMode = 5 'Inches

Idx = 0
While Idx < rstAssets.Fields.Count - 10
'Create the Label
Set HdrCtrl = CreateReportControl(rprt.Name, _
acLabel, acPageHeader, &quot;&quot;, _
&quot;&quot;, XPos, YPos)
With HdrCtrl
.Height = 300
.Name = &quot;lbl&quot; & rstAssets.Fields(Idx).Name
.Caption = rstAssets.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; & rstAssets.Fields(Idx).Name
.ControlSource = rstAssets.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 acReport, OldName
DoCmd.Close
DoCmd.Rename MyName, acReport, OldName
rstAgency.Close
cnn.Close
End Function

 

[MrMajik]

I learned that in Acess 2002 you can't save a report while the code is running. Strange...There is a DoCmd.OutputTo method available in VB. However, it will not allow you to output to a report format. You can output to a text file or a HTML page etc.

Once I stopped thinking of code and asked myself what it is I am trying to do I realized I don't need to save these reports because the code will generate them on demand.

All is well

MrMajik

 

[MichaelRed]

looks like you have answered your own questions to your satisfaction. I do not have Ms. A. 2K2. so cannot answer that issue anyway. If it 'works' for you, it was obviously converted 'satisfactorily', so that also appears to be a moot issue. Thanks for the star / appreciation.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MrMajik]

MichaelRed;

Your original example got me going in the right direction. The rest of this report-generating stuff is a cake-walk in the park

Thanks again for taking the time to help with this.

MrMajik