Create Dynamic Report using VBA

[Innova1]

Can any of this be done without using the acCmdDesignView command? This is a problem for accde files.

[Rabbit]

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.

[Innova1]

Yes exactly! I rephrase, how do I accomplish this (dynamic report) without the use of design view to publish as an accde?

[Rabbit]

You can't. Because a dynamic report in essence changes the design of a report. Otherwise, it's not dynamic.

[Innova1]

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.

[Rabbit]

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.

[pdebaets]

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.

[Mr Key]

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...Col 40; 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.

[lonerjohn]

Sorry, but the CreateAutoRepor t 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?

[twinnyfo]

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.

[lonerjohn]

The error highlights the line "With CreateReportCon trol(.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.

[lonerjohn]

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.

[twinnyfo]

Allllllllllllll lllllllll 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.

Option Compare Database
Option Explicit

Private appAccess   As New Access.Application

Public Sub CreateAutoReport(strSQL As String)
    Dim rpt         As Access.Report
    Dim rptReport   As Access.Report
    Dim strCaption  As String
    Dim lblReport   As Access.Label
    Dim txtReport   As Access.TextBox

    CurrentDb.QueryDefs("qryDummy").SQL = strSQL
    appAccess.OpenCurrentDatabase (CurrentDb.Name)

    ' Open dummy query to invoke NewObjectAutoReport command on it
    ' Put the report created to design view to make properties editable
    With DoCmd
        .OpenQuery "qryDummy", acViewNormal
        .RunCommand acCmdNewObjectAutoReport
        .Close acQuery, "qryDummy"
        .RunCommand acCmdDesignView
    End With

    ' Get reference to just created report
    For Each rpt In Reports
        If rpt.RecordSource = "qryDummy" Then Set rptReport = rpt
    Next

    With rptReport

        ' Create title control
        With CreateReportControl(.Name, acLabel, _
                acPageHeader, , "Title", 0, 0)
            .FontBold = True
            .FontSize = 12
            .SizeToFit
        End With

        ' Create timestamp on footer
        CreateReportControl .Name, acLabel, _
            acPageFooter, , Now(), 0, 0

        ' Create page numbering on footer
        With CreateReportControl(.Name, acTextBox, _
            acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
            .Width - 1000, 0)
            .SizeToFit
        End With

        ' Detach the report from dummy query
        .RecordSource = strSQL
 
        ' Set the report caption to autogenerated unique string
        strCaption = GetUniqueReportName
        If strCaption <> "" Then .Caption = strCaption

    End With

    DoCmd.RunCommand acCmdPrintPreview

    Set rptReport = Nothing

End Sub

Public Function GetUniqueReportName() As String
    Dim rpt         As Report
    Dim intCounter  As Integer
    Dim blnIsUnique As Boolean

    For intCounter = 1 To 256
        GetUniqueReportName = "rptAutoReport_" & Format(intCounter, "0000")
        blnIsUnique = True
        For Each rpt In appAccess.Reports
            If rpt.Name = GetUniqueReportName Then blnIsUnique = False
        Next
        If blnIsUnique Then Exit Function
    Next

    GetUniqueReportName = ""

End Function

hope this hepps!

[lonerjohn]

That does work, but it opens up a whole new instance of my database

[lonerjohn]

I commented out the line:

set appAccess = createobject("Access.Application")

And now it works the way it should.