Hi All,
Please advise how to set a dynamic report to be tabular automatically
Also, help me to add groupings to a dynamic report via VBA code.
I Used the below code, but the problem with this code is, it creates report with one line in each page and if I tried to correct the width of the report through VBA I am getting the header for every single line. I am not sure where I am incorrect. - Function CreateDynamicReport(strSQL As String)
-
Dim db As DAO.Database ' database object
-
Dim rs As DAO.Recordset ' recordset object
-
Dim fld As DAO.Field ' recordset field
-
Dim txtNew As Access.TextBox ' textbox control
-
Dim lblNew As Access.Label ' label control
-
Dim rpt As Report ' hold report object
-
Dim lngTop As Long ' holds top value of control position
-
Dim lngLeft As Long ' holds left value of controls position
-
Dim title As String 'holds title of report
-
-
'set the title
-
title = "REPORT"
-
-
' initialise position variables
-
lngLeft = 0
-
lngTop = 0
-
-
'Create the report
-
Set rpt = CreateReport
-
-
' set properties of the Report
-
With rpt
-
-
.Printer.Orientation = acPRORLandscape
-
.LayoutForPrint = True
-
.Width = "14000"
-
.RecordSource = strSQL
-
.Caption = title
-
.Section(acDetail).Height = 420
-
End With
-
-
' Open SQL query as a recordset
-
Set db = CurrentDb
-
Set rs = db.OpenRecordset(strSQL)
-
-
' Create Label Title
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
-
acPageHeader, , "ReTAIn REPORT", 0, 0)
-
lblNew.FontBold = True
-
lblNew.FontSize = 12
-
lblNew.SizeToFit
-
-
' Create corresponding label and text box controls for each field.
-
For Each fld In rs.Fields
-
-
' Create new text box control and size to fit data.
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acDetail, , fld.Name, lngLeft, lngTop + 250)
-
txtNew.SizeToFit
-
-
' Create new label control and size to fit data.
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
-
txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
-
lblNew.SizeToFit
-
-
' Increment top value for next control
-
'lngTop = lngTop + txtNew.Height + 25
-
lngLeft = lngLeft + lblNew.Width + 25
-
Next
-
-
' Create datestamp in Footer
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
-
acPageFooter, , Now(), 0, 0)
-
-
' Create page numbering on footer
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
-
txtNew.SizeToFit
-
-
' Open new report.
-
DoCmd.OpenReport rpt.Name, acViewPreview
-
-
'reset all objects
-
rs.Close
-
Set rs = Nothing
-
Set rpt = Nothing
-
Set db = Nothing
-
-
End Function
1 4473
Hi,
The main issue is the fact that you create a label related to the text box control. When dropping this and changing some position parms i got it working as you intended. -
' Create corresponding label and text box controls for each field.
-
For Each fld In rs.Fields
-
-
' Create new text box control and size to fit data.
-
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
-
acDetail, , fld.Name, lngLeft, 0)
-
txtNew.SizeToFit
-
-
' Create new label control and size to fit data.
-
Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
-
, fld.Name, lngLeft, 400, 1400, txtNew.Height)
-
lblNew.SizeToFit
-
-
' Increment top value for next control
-
'lngTop = lngTop + txtNew.Height + 25
-
lngLeft = lngLeft + lblNew.Width + 25
-
Next
-
Guess the finetuning won't be a problem.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Climber |
last post by:
Hello,
I want to now how to do a dynamic report with Crystal
Report (C# .Net), the number of colum and row are
different each time because they depend of my data base.
Thanks
Climber
|
by: Brad |
last post by:
Thanks for taking the time to read my question.
I have a table of data that has Date, Data and Category. I need to
show, in
a report, each Categories Data by Date. The Date has to be it's own...
|
by: Peter Herath |
last post by:
I want to create a dynamic report using a crosstab query...... pls someone look into my attached example database and help me out to do the report generation....
example is like dis...:
there...
|
by: zoro25 |
last post by:
Hi,
I want to create a dynamic report and for that I'm using a very simple Combo Box (only one item) and I want to use this filter on my report. Here's the code I came up with:
Private Sub...
|
by: creative1 |
last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however,...
|
by: Sep410 |
last post by:
Hi all,
I have to create a dynamic report in vb.net.
I never had done something like this before.Users want to see tables name and when they choose the table they will select which fields should...
|
by: J360 |
last post by:
I'm using VB in Access 2003 to generate a dynamic report. I first open the report in design view to set all the grouping levels etc. I then use
with rpt
.printer.orientation =...
|
by: Kim Norgren |
last post by:
I hope I have followed the posting guidelines; apologies from a neophyte if not, and please correct me. I'm self-taught in VB and have reached my limit, so I'm looking for help. I am dealing with...
|
by: Maya16 |
last post by:
I have created one application in access using VBA. In this project I am creating Dynamic Report. I am able to create and open report in development environment i.e. when the project is in accdb...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
| |