Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Wink Unanswered: Starting to produce many Reports

    I am at the stage where I will be creating many Reports for my Database.

    I want to start correctly so I do not have to re-design the Reports in the future?

    At the moment, I have a "Blank" Report which has been designed and formatted as I want all future Reports (eg. logo in a subform at the top, followed by formatted Heading, fonts are specified, height of text fields are defined, footer is formatted etc etc).

    How would I get all Reports to look the same format in the future?

    Are there any other matters I should consider before starting to create all my Reports?

    Thanks in advance for any help you may wish to provide.

    Paul
    Last edited by reddevil1; 08-26-11 at 16:05.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once you have a Report that can be considered as a template for all reports in your application, you might consider using the CreateReport method of the Application object to create them. This method accepts the name of an existing report that will be used as the model of the new report (both arguments are optional):
    Code:
    Application.CreateReport [DatabaseName], [ReportModelName]
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Thanks for your reply.

    My SQL is extremely poor.

    I have only created 1 Report so far - and it looks wonderful (if I may say so myself).

    But I had to adjust all the Field Headings and Record Data manually

    It took me AGES to do that.

    Is there an easy way (without me having to write SQL myself, where I can create Reports using the template that I have created?

    Currently, when I create a Report, it always formats it using the Soft Gray template....i woudl like it to automatically use my template??

    I read somewhere that i could change the default but I cannot seem to do it successfully??

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by reddevil1 View Post
    Is there an easy way (without me having to write SQL myself, where I can create Reports using the template that I have created?
    I provided a solution for that and it's not SQL, it's VBA.

    1. Create a new form and place a command button on it. Name the button Cmd_CreateReport.

    2. In design mode, click on the button you created, Open the properties window, select the Event tab, localize the On Click line and click on the little sqare button on the right (the one with 3 dots).

    3. The VBA Editor should pop open with these lines of code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Cmd_CreateReport_Click()
    
    End Sub
    4. Copy and paste the lines in red, as follows:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Cmd_CreateReport_Click()
    
        Dim strReportModelName As String
        
        strReportModelName = "Report1"  ' Replace Report1 by the name of the report you want to use as a template.
        Application.CreateReport , strReportModelName    
    
    End Sub
    5. Edit the code and replace "Report1", as explained in the comment.

    6. Open the form in Form view. Each time you click on the command button a new report will be created on the model used as template.
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thanks. I have put the following code in the Command button.

    Code:
    Private Sub Cmd_CreateReport_Click()
    
    Dim strReportModelName As String
        
        strReportModelName = "Template Report 1"  ' Replace Report1 by the name of the report you want to use as a template.
        Application.CreateReport , strReportModelName
        
    End Sub
    It does indeed create a Report with a format nearly the same as my Template.

    However, it has not inserted a sub-form with the company logo. It has not put the date and page numbers in the footer. And it has not inserted the Heading at the top of the Page.

    I have just done Copy & Paste of the Template and then manually drag & drop and Align all the Fields. But this is a pain in the neck!

    Is ther any other way I can automatically get the same design and also have Access to arrange the Controls/Fields in a decent order?

    Thanks,

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Then you could try:
    Code:
    Private Sub Cmd_CreateReport_Click()
    
        Dim strNewReportName As String
        Dim strReportModelName As String
        
        strNewReportName = InputBox("Please enter the name of the new report:")
        If len(strNewReportName ) > 0 Then
            strReportModelName = "Template Report 1"  ' Replace Report1 by the name of the report you want to use as a template.
            DoCmd.CopyObject , strNewReportName, acReport, strReportModelName
        End If
        
    End Sub
    The only problem here is that you must know the name of the new report in advance and supply it (but is it a problem?).
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thanks for the code which creates a new Report exactly the same as the Template.

    I had previously just done "copy & paste" of the template and it looks like it produces the same result? My problem is that I now have to drag and drop and size etc etc all the Fields.

    What i am looking for is a 'Wizard' which will automatically aorganise my Fields and align them correctly, but using my Template design.

    Is this possible?
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it is. You can write a set of functions that will create a form or a report and its controls according to a set of "rules" or specification you provide, that's precisely what the Access Wizards do. This requires a good knowledge of both VBA and of the Access Object Model, though.

    As an example, here's a function from an application I use to create controls (TextBox and Labels) on a form:
    Code:
    Public Function Create_TextBoxes_List(ByVal FileName As String, ByVal FormName As String) As Long
    
        Dim appAccess As Access.Application
        Dim obj As AccessObject
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim frm As Form
        Dim tbx As TextBox
        Dim lbl As Label
        Dim lngCtlTop As Long
        Dim lngTabCount As Long
        Dim cls As Cls_wzCheckSums
        Dim lngKey As Long
        Dim strCheckSum As String
        Dim lngStatus As Long
        
        On Error GoTo Err_Create_TextBoxes_List
    
        If Len(Dir(FileName)) > 0 Then lngStatus = True
        If lngStatus = True Then
            Set appAccess = New Access.Application
            appAccess.OpenCurrentDatabase FileName
            appAccess.DoCmd.OpenForm FormName, acDesign
            Set frm = appAccess.Forms(FormName)
            Set rst = CodeDb.OpenRecordset("Tbl_wzListFormBuilder_2", dbOpenSnapshot)
            With rst
                Do Until .EOF
                    If !Keep = True Then
                        Set tbx = appAccess.CreateControl(FormName, acTextBox, acDetail, , , 2000, lngCtlTop, 567, 255)
                        If Len(Nz(!Custom_Name, "")) = 0 Then
                            tbx.Name = "Text_" & !Original_Name
                            tbx.ControlSource = !Original_Name
                        Else
                            tbx.Name = "Text_" & !Custom_Name
                            tbx.ControlSource = !Custom_Name
                        End If
                        tbx.OnDblClick = "=ParentCallBack()"
                        tbx.TabIndex = lngTabCount
                        If !Id = True Then tbx.HelpContextId = -1
                        Set lbl = appAccess.CreateControl(FormName, acLabel, acDetail, tbx.Name, , 0, lngCtlTop, 1850, 255)
                        lbl.Name = Replace(tbx.Name, "Text_", "Label_")
                        If Len(Nz(!Caption, "")) > 0 Then
                            lbl.Caption = !Caption
                        Else
                            lbl.Caption = Replace(lbl.Name, "Label_", "")
                        End If
                        lngCtlTop = lngCtlTop + 300
                    End If
                    .MoveNext
                Loop
                .Close
            End With
            Set rst = Nothing
            Set cls = New Cls_wzCheckSums
            Set dbs = CodeDb
            With cls
                lngKey = .Key
                strCheckSum = .CheckSum(.wzGUID(CStr(dbs.Properties("ApplicationSignature").Value)), lngKey)
            End With
            Set lbl = appAccess.CreateControl(FormName, acLabel, acDetail, , , 0, 0, 10, 10)
            With lbl
                .Name = "Label_Dummy"
                .BackStyle = 0
                .SpecialEffect = 0
                .BorderStyle = 0
                .BorderWidth = 0
                .Tag = strCheckSum
                .HelpContextId = lngKey
            End With
            Set cls = Nothing
        End If
    
    Exit_Create_TextBoxes_List:
        If Not appAccess Is Nothing Then
            appAccess.Quit
            Set appAccess = Nothing
        End If
        Create_TextBoxes_List = lngStatus
        Exit Function
    
    Err_Create_TextBoxes_List:
        lngStatus = Err.Number
        Error_Handler "Create_TextBoxes_List"
        Resume
        Err.Clear
        Resume Exit_Create_TextBoxes_List
        
    End Function
    Have a nice day!

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    Cheers Sinndho,

    I think that code is quite a bit above my understanding limit. i suppose I was hoping the Wizard would be able to do everything it does now, but then ask me which Template it should use at the end? Could I replace one of the standard templates with my own template?
    What would you attempt to do if you knew you would not fail?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •