473,320 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dynamic Report with VBA in MS ACCESS

66 64KB
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.

Expand|Select|Wrap|Line Numbers
  1. Function CreateDynamicReport(strSQL As String)
  2. Dim db As DAO.Database ' database object
  3. Dim rs As DAO.Recordset ' recordset object
  4. Dim fld As DAO.Field ' recordset field
  5. Dim txtNew As Access.TextBox ' textbox control
  6. Dim lblNew As Access.Label ' label control
  7. Dim rpt As Report ' hold report object
  8. Dim lngTop As Long ' holds top value of control position
  9. Dim lngLeft As Long ' holds left value of controls position
  10. Dim title As String 'holds title of report
  11.  
  12.      'set the title
  13.      title = "REPORT"
  14.  
  15.      ' initialise position variables
  16.      lngLeft = 0
  17.      lngTop = 0
  18.  
  19.      'Create the report
  20.      Set rpt = CreateReport
  21.  
  22.      ' set properties of the Report
  23.      With rpt
  24.  
  25.          .Printer.Orientation = acPRORLandscape
  26.          .LayoutForPrint = True
  27.          .Width = "14000"
  28.          .RecordSource = strSQL
  29.          .Caption = title
  30.          .Section(acDetail).Height = 420
  31.      End With
  32.  
  33.      ' Open SQL query as a recordset
  34.      Set db = CurrentDb
  35.      Set rs = db.OpenRecordset(strSQL)
  36.  
  37.      ' Create Label Title
  38.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  39.      acPageHeader, , "ReTAIn REPORT", 0, 0)
  40.      lblNew.FontBold = True
  41.      lblNew.FontSize = 12
  42.      lblNew.SizeToFit
  43.  
  44.      ' Create corresponding label and text box controls for each field.
  45.      For Each fld In rs.Fields
  46.  
  47.          ' Create new text box control and size to fit data.
  48.          Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  49.          acDetail, , fld.Name, lngLeft, lngTop + 250)
  50.          txtNew.SizeToFit
  51.  
  52.          ' Create new label control and size to fit data.
  53.          Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
  54.          txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
  55.          lblNew.SizeToFit
  56.  
  57.          ' Increment top value for next control
  58.          'lngTop = lngTop + txtNew.Height + 25
  59.           lngLeft = lngLeft + lblNew.Width + 25
  60.      Next
  61.  
  62.      ' Create datestamp in Footer
  63.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  64.      acPageFooter, , Now(), 0, 0)
  65.  
  66.      ' Create page numbering on footer
  67.      Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  68.      acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
  69.      txtNew.SizeToFit
  70.  
  71.      ' Open new report.
  72.      DoCmd.OpenReport rpt.Name, acViewPreview
  73.  
  74.      'reset all objects
  75.      rs.Close
  76.      Set rs = Nothing
  77.      Set rpt = Nothing
  78.      Set db = Nothing
  79.  
  80. End Function
Mar 30 '15 #1
1 4473
nico5038
3,080 Expert 2GB
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.

Expand|Select|Wrap|Line Numbers
  1.      ' Create corresponding label and text box controls for each field.
  2.      For Each fld In rs.Fields
  3.  
  4.          ' Create new text box control and size to fit data.
  5.          Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  6.          acDetail, , fld.Name, lngLeft, 0)
  7.          txtNew.SizeToFit
  8.  
  9.          ' Create new label control and size to fit data.
  10.          Set lblNew = CreateReportControl(rpt.Name, acLabel, acPageHeader, _
  11.           , fld.Name, lngLeft, 400, 1400, txtNew.Height)
  12.          lblNew.SizeToFit
  13.  
  14.          ' Increment top value for next control
  15.          'lngTop = lngTop + txtNew.Height + 25
  16.           lngLeft = lngLeft + lblNew.Width + 25
  17.      Next
  18.  
Guess the finetuning won't be a problem.

Nic;o)
Apr 2 '15 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

3
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
1
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...
6
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...
2
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...
3
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,...
4
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...
2
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 =...
3
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...
3
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
0
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...
0
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.