By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,741 Members | 2,004 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Create Dynamic Report using VBA

MMcCarthy
Expert Mod 10K+
P: 14,534
This article contains three different approaches to creating dynamic reports.
  • Dynamic report from user defined SQL SELECT statement (Author: mmccarthy)
  • Dynamic report designed to be used with Crosstab Queries (Author: Nico5038)
  • Dynamic report creation via AutoReport command (Author: FishVal)
Dynamic report from user defined SQL SELECT statement
You will sometimes find yourself in a position where you need to allow the users to create dynamic reports based on a user defined query. The following function will create such a report when passed any appropriate SQL SELECT statement.

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 = "Title for the 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.          .Width = 8500
  25.          .RecordSource = strSQL
  26.          .Caption = title
  27.      End With
  28.  
  29.      ' Open SQL query as a recordset
  30.      Set db = CurrentDb
  31.      Set rs = db.OpenRecordset(strSQL)    
  32.  
  33.      ' Create Label Title
  34.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  35.      acPageHeader, , "Title", 0, 0)
  36.      lblNew.FontBold = True
  37.      lblNew.FontSize = 12
  38.      lblNew.SizeToFit
  39.  
  40.      ' Create corresponding label and text box controls for each field.
  41.      For Each fld In rs.Fields
  42.  
  43.          ' Create new text box control and size to fit data.
  44.          Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  45.          acDetail, , fld.Name, lngLeft + 1500, lngTop)
  46.          txtNew.SizeToFit
  47.  
  48.          ' Create new label control and size to fit data.
  49.          Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
  50.          txtNew.Name, fld.Name, lngLeft, lngTop, 1400, txtNew.Height)
  51.          lblNew.SizeToFit        
  52.  
  53.          ' Increment top value for next control
  54.          lngTop = lngTop + txtNew.Height + 25
  55.      Next
  56.  
  57.      ' Create datestamp in Footer
  58.      Set lblNew = CreateReportControl(rpt.Name, acLabel, _
  59.      acPageFooter, , Now(), 0, 0)
  60.  
  61.      ' Create page numbering on footer
  62.      Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
  63.      acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
  64.      txtNew.SizeToFit
  65.  
  66.      ' Open new report.
  67.      DoCmd.OpenReport rpt.Name, acViewPreview
  68.  
  69.      'reset all objects
  70.      rs.Close
  71.      Set rs = Nothing
  72.      Set rpt = Nothing
  73.      Set db = Nothing
  74.  
  75. End Function
  76.  

This report will not be saved until the user saves it or tries to close it. At which point they will be prompted to save it. You can play around with the layout of the report using the lngTop and lngLeft variables.

To call this function you simply need to pass a String parameter of a SQL statement to it as per the following.

CreateDynamicReport "SELECT * FROM TableName"

To create that SQL SELECT statement you can set up a form to allow the user to select options to build the query. There is no facility in this code to validate the SQL query as it is assumed this is done elsewhere. But thats another article.


Dynamic report designed to be used with Crosstab Queries

This code is especially "tuned" for crosstab queries. As I like to have control over the layout, thus I have the lay-out designed first with "coded" controls. Then the dynamic filling becomes very easy. The raw text I use to help with this is:

Making the columnheader and detaildata flexible is possible, but needs some VBA code in the OpenReport event.

To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

The report query has two rowheader columns and a Total column, therefore the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you.

Make sure that the number of Columns is not bigger than the number placed. The program code has no protection against that.

The code needed for the open report event is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2. Dim intI As Integer
  3. Dim rs As Recordset
  4.  
  5.      Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
  6.  
  7.      'Place headers
  8.      For intI = 3 To rs.Fields.Count - 1
  9.          Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
  10.      Next intI
  11.  
  12.      'Place correct controlsource
  13.      For intI = 3 To rs.Fields.Count - 1
  14.          Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
  15.      Next intI
  16.  
  17.      'Place Total field
  18.      Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"   
  19.  
  20. End Sub
  21.  
The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but it could differ for you.


Dynamic report creation via AutoReport command
This code is used for dynamic report creation using the AutoReport command. You will first need to create a query and call it "qryDummy". This query is used by the code but the resulting report will not be based on the query as this would invalidate the report when the query was next changed.

Expand|Select|Wrap|Line Numbers
  1. Public Sub CreateAutoReport(strSQL As String)
  2. Dim rptReport As Access.Report
  3. Dim strCaption As String
  4.  
  5.      CurrentDb.QueryDefs("qryDummy").SQL = strSQL
  6.  
  7.      ' Open dummy query to invoke NewObjectAutoReport command on it
  8.      ' Put the report created to design view to make properties editable
  9.      With DoCmd
  10.          .OpenQuery "qryDummy", acViewNormal
  11.          .RunCommand acCmdNewObjectAutoReport
  12.          .Close acQuery, "qryDummy"
  13.          .RunCommand acCmdDesignView
  14.      End With
  15.  
  16.      ' Get reference to just created report
  17.      For Each rpt In Reports
  18.          If rpt.Caption = "qryDummy" Then Set rptReport = rpt
  19.      Next
  20.  
  21.      With rptReport
  22.  
  23.          ' Create title control
  24.          With CreateReportControl(.Name, acLabel, _
  25.              acPageHeader, , "Title", 0, 0)
  26.              .FontBold = True
  27.              .FontSize = 12
  28.              .SizeToFit
  29.          End With
  30.  
  31.          ' Create timestamp on footer
  32.          CreateReportControl .Name, acLabel, _
  33.              acPageFooter, , Now(), 0, 0
  34.  
  35.          ' Create page numbering on footer
  36.          With CreateReportControl(.Name, acTextBox, _
  37.              acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
  38.              .Width - 1000, 0)
  39.              .SizeToFit
  40.          End With
  41.  
  42.          ' Detach the report from dummy query
  43.          .RecordSource = strSQL
  44.  
  45.          ' Set the report caption to autogenerated unique string
  46.          strCaption = GetUniqueReportName
  47.          If strCaption <> "" Then .Caption = strCaption
  48.  
  49.      End With
  50.  
  51.      DoCmd.RunCommand acCmdPrintPreview
  52.  
  53.      Set rptReport = Nothing
  54.  
  55. End Sub
  56.  
  57.  
  58. Public Function GetUniqueReportName() As String
  59. Dim intCounter As Integer
  60. Dim blnIsUnique As Boolean
  61.  
  62.      For intCounter = 1 To 256 
  63.          GetUniqueReportName = "rptAutoReport_" & Format(intCounter, "0000")
  64.          blnIsUnique = True
  65.          For Each rpt In CurrentProject.AllReports
  66.              If rpt.Name = GetUniqueReportName Then blnIsUnique = False
  67.          Next
  68.          If blnIsUnique Then Exit Function
  69.      Next
  70.  
  71.      GetUniqueReportName = ""
  72.  
  73. End Function
  74.  
Aug 20 '07 #1
Share this Article
Share on Google+
36 Comments


P: 2
I was wondering how one would dynamically change the sizing of the columns in the field. Creating an auto report is nice but its messy. Here is the code I have now


Expand|Select|Wrap|Line Numbers
  1. Public Function StaticReportGen(SQLStr As String, Title As String, layout As String) As Boolean
  2.     Dim strReportName       As String
  3.     Dim rpt                 As Report
  4.     Dim FieldName           As Field
  5.     Dim RS                  As Recordset
  6.     Dim intI                As Integer
  7.     Dim ctrl                As Control
  8.     Dim ColWidth            As Integer
  9.     Dim FirstCol            As Boolean
  10.     Dim TextWidth           As Integer
  11.     Dim TextCol             As Boolean
  12.     Dim TextBoxes           As Collection
  13.     Dim Labels              As Collection
  14.     Dim rsLengthCheck       As ADODB.Recordset
  15.     Dim objConn             As ADODB.Connection
  16.  
  17.     On Error GoTo rptErrHandler
  18.  
  19.     ColWidth = 0
  20.     TextWidth = 0
  21.     TextCol = True
  22.     FirstCol = True
  23.  
  24.     Set rpt = CreateReport()
  25.     strReportName = rpt.Name
  26.     rpt.Caption = Title
  27.  
  28.     DoCmd.RunCommand acCmdDesignView
  29.     DoCmd.Save acReport, strReportName
  30.     DoCmd.Close acReport, strReportName, acSaveNo
  31.     DoCmd.Rename Title, acReport, strReportName
  32.     DoCmd.OpenReport Title, acViewDesign
  33.     Set rpt = Reports(Title)
  34.  
  35.     'set printer stuff
  36.     rpt.Printer.BottomMargin = 360
  37.     rpt.Printer.LeftMargin = 360
  38.     rpt.Printer.RightMargin = 360
  39.     rpt.Printer.TopMargin = 360
  40.  
  41.     If layout = "Landscape" Then
  42.         rpt.Printer.Orientation = acPRORLandscape
  43.     Else
  44.         rpt.Printer.Orientation = acPRORPortrait
  45.     End If
  46.  
  47.     Set RS = CurrentDb.OpenRecordset(SQLStr)
  48.     rpt.RecordSource = SQLStr
  49.  
  50.     'create label on pageheader
  51.     For Each FieldName In RS.Fields
  52.         CreateReportControl Title, acLabel, acPageHeader, , FieldName.Name, 0, 0
  53.         CreateReportControl Title, acTextBox, acDetail, , FieldName.Name, 0, 0
  54.         '
  55.     Next FieldName
  56.  
  57.     'arrange fields
  58.     For Each ctrl In rpt.Controls
  59.  
  60.         Select Case ctrl.ControlType
  61.             Case acTextBox
  62.                 If TextCol Then
  63.                     ctrl.Name = ctrl.ControlSource
  64.                     ctrl.Move TextWidth, 0, ctrl.WIDTH, ctrl.Height
  65.                     TextWidth = TextWidth + ctrl.WIDTH
  66.                 Else
  67.                     ctrl.Name = ctrl.ControlSource
  68.                     ctrl.Move TextWidth, 0, ctrl.WIDTH, ctrl.Height
  69.                     TextWidth = TextWidth + ctrl.WIDTH
  70.                 End If
  71.                 TextCol = False
  72.             Case acLabel
  73.                 If FirstCol Then
  74.                     ctrl.Name = "lbl" & ctrl.Caption
  75.                     ctrl.Move ColWidth, 0, ctrl.WIDTH, ctrl.Height
  76.                 Else
  77.                     ctrl.Name = "lbl" & ctrl.Caption
  78.                     ctrl.Move TextWidth, 0, ctrl.WIDTH, ctrl.Height
  79.                 End If
  80.                 ctrl.FontSize = 10
  81.                 ctrl.FontWeight = 700
  82.                 FirstCol = False
  83.             Case Else
  84.  
  85.         End Select
  86.  
  87.     Next ctrl
  88.     'create line
  89.     CreateReportControl Title, acLine, acPageHeader, , , 0, 300, rpt.WIDTH
  90.  
  91.     'create title
  92.     CreateReportControl Title, acLabel, acHeader, , Title, 0, 0
  93.     CreateReportControl Title, acTextBox, acHeader, , Chr(61) & Chr(34) & "Printed on:   " & Chr(34) & "& Date() ", 0, 300
  94.  
  95.     For Each ctrl In rpt.Controls
  96.  
  97.         Select Case ctrl.ControlType
  98.             Case acTextBox
  99.                 If ctrl.Section = 1 Then
  100.                     ctrl.FontWeight = 700
  101.                     ctrl.FontSize = 14
  102.                     ctrl.Height = 350
  103.                     ctrl.WIDTH = 3500
  104.                     ctrl.Top = 400
  105.                 End If
  106.  
  107.             Case acLabel
  108.                 If ctrl.Section = 1 Then
  109.                     ctrl.FontSize = 16
  110.                     ctrl.FontWeight = 700
  111.                     ctrl.Height = 350
  112.                     ctrl.WIDTH = 3500
  113.                 End If
  114.         End Select
  115.  
  116.     Next ctrl
  117.  
  118.     'size fields correctly
  119.     For Each ctrl In rpt.Controls
  120.  
  121.         Select Case ctrl.ControlType
  122.  
  123.             Case acTextBox
  124.                 For Each FieldName In RS.Fields
  125.                     If ctrl.Name = FieldName Then
  126.  
  127.                     End If
  128.                 Next FieldName
  129.  
  130.             Case acLabel
  131.  
  132.         End Select
  133.  
  134.     Next ctrl
  135.  
  136.     DoCmd.Save acReport, Title
  137.     DoCmd.OpenReport Title, acViewPreview
  138.     StaticReportGen = True
  139.     Exit Function
  140.  
  141. rptErrHandler:
  142.     Select Case Err.Number
  143.     End Select
  144.     StaticReportGen = False
  145.     Debug.Print Err.Number
  146.     Debug.Print Err.Description
  147.     Exit Function
  148. End Function
  149.  

I want to use the textwidth property just having a block.
Jan 21 '08 #2

P: 23
Can you set a dynamic report to be tabular automatically?

Also, could somebody show what the code would look like to add groupings to a dynamic report?

I try

with rpt

.GroupLevel(0).controlsource = me.combo1.value

but keep getting "Error, no grouping or sorting command given"

Thanks.
Aug 26 '08 #3

P: 1
Dim vargrplevel As Variant 'holds grouping level of report

vargrplevel = CreateGroupLevel(rpt.Name, "[INSERT FIELD NAME]", True, True)
rpt.Section(acGroupLevel1Header).Height = 400
rpt.Section(acGroupLevel1Footer).Height = 400



This will also create headers/footers for the grouping level.
Mar 22 '10 #4

P: 53
Hi msquared

AutoReport Code has some Errors and problems
Can we correct them together?
Best regards
AliNagoo
Jun 20 '10 #5

P: 2
The code does not exactly does what it's suppose to. Each record is displayed on a separate page. In the "for each field" section, the "Increment top value for next control" code does not do the job.

Any idea what is wrong with this?

Thanks

acheo
Sep 18 '10 #6

P: 53
Hello
I think It's more better to show every record in a page for me!
because the report's fields are numerous and the length of them is variable from zero to 255 character so i wont try to show records in another style like tabular.
Good Luck
Sep 19 '10 #7

P: 2
I don't understand what you're saying. Clearly, the author wanted to display several records in a single page. I'm telling you that this does not work at run time. It merely displays the first record and then displays the second one on the followoing page. That defeats the purpose of having a report don't you think? So again, my question is: what should we change to have the records sequentially displayed?

thanks
Sep 19 '10 #8

P: n/a
jai Kumar
where can I get "Report", "CreateReportControl" "CreateReport" etc functions used in this
Oct 13 '10 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
These functions are standard in the default Access libraries in VBA. You shouldn't need to add any extra libraries to use them.
Oct 13 '10 #10

P: 58
For the case of Dynamic report designed to be used with Crosstab Queries (Author: Nico5038).
1. Is it possible to vary the number of column headings with this?
2. Where is the recordsourse of this Code?
Dec 31 '10 #11

nico5038
Expert 2.5K+
P: 3,072
Hi Moah Scout,

For answering your questions:

1. The number of headings is "fixed", as you need to place the controls holding the results in the report design. The maximum number of columns thus needs to be known. When there are less columns as the max, there's no problem, as these won't be filled.
When there are more, the extra columns are ignored. With a little effort you could add a warning that there are more columns by using the .field.count property of the recordset.

2. The recordsource of the report holding the code is used in line 5:
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

Thus this code is "universal", for every report using this code.

Regards,

Nico
Jan 4 '11 #12

100+
P: 131
Ok!
What if the ColumnHeadings of Crosstab-Query changes and RowHeadings remain constant?
I mean, CrossTabQuery will be created from user selections on the combobox and thus the RowHeadings has to remain constant and the number ColumnHeadings Changes from 10 to 27.
Is it still possible to use this code?
Jan 9 '11 #13

nico5038
Expert 2.5K+
P: 3,072
Then you'll need to "fix" column headers and not use:
>"The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc."
As these will hold the desired values.

Also remove the code lines :
Expand|Select|Wrap|Line Numbers
  1.  
  2.      'Place headers
  3.      For intI = 3 To rs.Fields.Count - 1
  4.          Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
  5.      Next intI
  6.  
When the fields selected aren't in a "fixed" sequence, then you can fill the column value by building a loop and check when the fieldname is equal to the column heading to fill the column.
Another option is to "Fix" the query and make sure all fields are always in the same order and put "Dummy" (read Null) values in the not used columns.

Getting the idea ?

Nico
Jan 9 '11 #14

P: 1
1.Can you set a dynamic report to be tabular automatically?

2.I have also tried to group some data using this code, but it doesn't seem to do anything:
Expand|Select|Wrap|Line Numbers
  1. varGroupLevel = CreateGroupLevel(report.Name, "data_curs", _
  2.         True, True)
  3. report.Section(acGroupLevel1Header).Height = 400
  4. report.Section(acGroupLevel1Footer).Height = 400
  5.  
Jun 12 '11 #15

P: 3
Can any of this be done without using the acCmdDesignView command? This is a problem for accde files.
Aug 23 '11 #16

Rabbit
Expert Mod 10K+
P: 12,365
accde files are specifically for the purposes of preventing changes to the design of the objects. There is no reason to have an accde otherwise. Wanting to change the design is counter to the purpose of having an accde.
Aug 23 '11 #17

P: 3
Yes exactly! I rephrase, how do I accomplish this (dynamic report) without the use of design view to publish as an accde?
Aug 23 '11 #18

Rabbit
Expert Mod 10K+
P: 12,365
You can't. Because a dynamic report in essence changes the design of a report. Otherwise, it's not dynamic.
Aug 23 '11 #19

P: 3
Rabbit, your statements are correct. However, based on the title of this thread (Dynamic report from user defined SQL SELECT statement), this code dynamically and programatically updates the forms/reports based on user selections. I want to prevent USER changes to the forms and reports, but want to make programatic changes based on user input.
Aug 23 '11 #20

Rabbit
Expert Mod 10K+
P: 12,365
Yes, and to do so, it changes the design of the report. Which you can't do in an accde. To make programmatic changes to the design, you must also allow the user to make changes to the design. You can't have both worlds.

You can either allow programmatic changes to the design and user changes to the design by not using an accde or you can prevent programmatic changes to the design and prevent user changes to the design by using an accde. You can't mix the two.
Aug 24 '11 #21

P: 16
See the 2nd example above "Dynamic report designed to be used with Crosstab Queries". This example does not open the report in design view, and should therefore be compatible with .accde files. You can adapt it for general use with any type of select query.
Oct 16 '11 #22

100+
P: 131
What if I have products on the market that create Column headings of different Size, say from 15 to 40 depending on sales of that particular day.
If you first design the Col1,Col2...Col40; When it comes the number of column resulted from Crosstab is lessthan 40, it gives error.
As Nico stipulated on Post no. 14 above, but I cant fix it. It still gives error.
Oct 17 '11 #23

P: 23
Sorry, but the CreateAutoReport function does not work. I get an error message of "Object Variable or with block variable not set."

Additionally, it appears as if the author is using two different sets of variables for the report; both rpt and rptReport.

Can someone advise on how to fix this?
Jan 29 '19 #24

twinnyfo
Expert Mod 2.5K+
P: 3,283
lonerjohn,

At which line are you getting this error? did you do a direct copy and paste? Did you make necessary changes to the code to account for your actual situation?

And you probably have the line Option Explicit at the top of your module, which is a good thing. However, the author did not explicitly declare all the necessary variables (rst being one of them).

Let us know what you got.
Jan 29 '19 #25

P: 23
The error highlights the line "With CreateReportControl(.Name, acLabel, acpageheader, , "Title", 0, 0)

But the line above it, with rptReport is equal to nothing. 5 or 6 lines above that, where it says "For each rpt in reports", rpt is also equal to nothing.

Yes, I copied all the code and didn't change anything. I created a query named qryDummy.
Jan 29 '19 #26

P: 23
I also do not have option explicit turned on. I just noticed he's referencing two different variables with similar names. Unless rpt is a keyword, as a variable, it's not being set to anything anywhere.
Jan 29 '19 #27

twinnyfo
Expert Mod 2.5K+
P: 3,283
Alllllllllllllllllllllll righty, then.................

Please refer to the following. It should work. All the kinks and weird stuff have been hammered out. Some things in the original were pointing to wrong things and would never have worked.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private appAccess   As New Access.Application
  5.  
  6. Public Sub CreateAutoReport(strSQL As String)
  7.     Dim rpt         As Access.Report
  8.     Dim rptReport   As Access.Report
  9.     Dim strCaption  As String
  10.     Dim lblReport   As Access.Label
  11.     Dim txtReport   As Access.TextBox
  12.  
  13.     CurrentDb.QueryDefs("qryDummy").SQL = strSQL
  14.     appAccess.OpenCurrentDatabase (CurrentDb.Name)
  15.  
  16.     ' Open dummy query to invoke NewObjectAutoReport command on it
  17.     ' Put the report created to design view to make properties editable
  18.     With DoCmd
  19.         .OpenQuery "qryDummy", acViewNormal
  20.         .RunCommand acCmdNewObjectAutoReport
  21.         .Close acQuery, "qryDummy"
  22.         .RunCommand acCmdDesignView
  23.     End With
  24.  
  25.     ' Get reference to just created report
  26.     For Each rpt In Reports
  27.         If rpt.RecordSource = "qryDummy" Then Set rptReport = rpt
  28.     Next
  29.  
  30.     With rptReport
  31.  
  32.         ' Create title control
  33.         With CreateReportControl(.Name, acLabel, _
  34.                 acPageHeader, , "Title", 0, 0)
  35.             .FontBold = True
  36.             .FontSize = 12
  37.             .SizeToFit
  38.         End With
  39.  
  40.         ' Create timestamp on footer
  41.         CreateReportControl .Name, acLabel, _
  42.             acPageFooter, , Now(), 0, 0
  43.  
  44.         ' Create page numbering on footer
  45.         With CreateReportControl(.Name, acTextBox, _
  46.             acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
  47.             .Width - 1000, 0)
  48.             .SizeToFit
  49.         End With
  50.  
  51.         ' Detach the report from dummy query
  52.         .RecordSource = strSQL
  53.  
  54.         ' Set the report caption to autogenerated unique string
  55.         strCaption = GetUniqueReportName
  56.         If strCaption <> "" Then .Caption = strCaption
  57.  
  58.     End With
  59.  
  60.     DoCmd.RunCommand acCmdPrintPreview
  61.  
  62.     Set rptReport = Nothing
  63.  
  64. End Sub
  65.  
  66. Public Function GetUniqueReportName() As String
  67.     Dim rpt         As Report
  68.     Dim intCounter  As Integer
  69.     Dim blnIsUnique As Boolean
  70.  
  71.     For intCounter = 1 To 256
  72.         GetUniqueReportName = "rptAutoReport_" & Format(intCounter, "0000")
  73.         blnIsUnique = True
  74.         For Each rpt In appAccess.Reports
  75.             If rpt.Name = GetUniqueReportName Then blnIsUnique = False
  76.         Next
  77.         If blnIsUnique Then Exit Function
  78.     Next
  79.  
  80.     GetUniqueReportName = ""
  81.  
  82. End Function
hope this hepps!
Jan 29 '19 #28

P: 23
That does work, but it opens up a whole new instance of my database
Jan 29 '19 #29

P: 23
I commented out the line:

Expand|Select|Wrap|Line Numbers
  1. set appAccess = createobject("Access.Application")
And now it works the way it should.
Jan 29 '19 #30

twinnyfo
Expert Mod 2.5K+
P: 3,283
Very strange. It works fine for me without a new instance....

I have no current explanation.
Jan 29 '19 #31

P: 23
No worries, I'm just happy it works. Now I just have to figure out the calculated control and my week will be set lol
Jan 29 '19 #32

twinnyfo
Expert Mod 2.5K+
P: 3,283
Perfect! I will remove from the code above so others don't experience the same.

Thanks!
Jan 29 '19 #33

twinnyfo
Expert Mod 2.5K+
P: 3,283
Lines 45-47 above give one such example. However, I presume your issue is a wee bit more complicated....

Feel free to begin a new thread with such a question. That will be more helpful for others viewing this site.
Jan 29 '19 #34

P: 23
Yeah, it's a lot more complicated, I'll explain it here, and if it's meant for another thread, I can post it again.

Let me preface this by saying I completely understand and acknowledge that the way this database was built is not efficient. However, my office wants to keep this setup.

I have a reports form (frmReports2) to generate reports based on information selected by the user. One of the command buttons contains the code you provided above. frmReports2 also contains a multi-value field list box to choose among specific fields to add to the report. All this works fine. My problem, however, is that 9 of the fields contain financial values. If the user selects one or more of these fields (FY1415, FY1516, FY1617, FY1718, FY1819, FY1920, FY2021, FY2122, FY2223) for the report, I need to have a calculated control on the report for each record that will sum up the values of the fields the user chose to populate on the report. So if the user chose to show values of FY1415, FY1516, FY1617 and FY1718, I would need to add those to the report (which is already done) but also add an additional field to calculate the sum of those (in this instance, 4) fields that the user chose.
Jan 29 '19 #35

twinnyfo
Expert Mod 2.5K+
P: 3,283
I am going to specifically ask you to begin a new thread, as I welcome the challenge.

However, I think the answer is as simple as creating a field that simply sums the desired field and placing it in the Report Footer--all of which, I believe, can be done using CreatReportControl. Then, it's just an issue of adjusting its location.

I encourage you to take a stab at it and post your results: success here; problems in a new thread.
Jan 29 '19 #36

P: 23
My problem is I'm not really advanced with Access. I've taught myself what I know so far. I'm inheriting this project in my new office from someone else. I wouldn't even really know where to begin. I'll post it in a new thread, just in case you do accept the challenge.
Jan 29 '19 #37