After designing and working with a Form Wizard it is natural to think about designing a Report Wizard too. Because of the designing task of a Form and Report is almost same, except Page Footer with Page Number and date.

If you have gone through the designing task of the Form Wizard then you don’t have to do it again. Please go through the earlier Post: Custom made Form Wizard to understand the designing task of the FormWizard or to download it from there.

Do the following few simple steps and the ReportWizard is ready:

  1. Make a copy of the FormWizard and rename it as ReportWizard.
  2. Open the ReportWizard in Design View.
  3. Change the List Box and Combo Box headings to read as Report Format and Select Table/Query for Report respectively.
  4. Change the word Form to Report in the left side labels.
  5. Display the Code Module of the ReportWizard by selecting View – ->Code (or Alt+F11).
  6. Press Ctrl+A to select the entire Code in the Form Module and press Delete Key to delete the Code.
  7. Copy and Paste the Code given below into the ReportWizard Form Module and save the Form:
    Option Compare Database
    Option Explicit
    Dim DarkBlue As Long, twips As Long, xtyp As Integer, strFile As String
    Dim MaxSeq As Integer
    
    Private Sub cmdBack_Click()
       Me.Page1.Visible = True
       Me.Page1.SetFocus
       Me.Page2.Visible = False
    End Sub
    
    Private Sub cmdCancel_Click()
       DoCmd.Close acForm, Me.NAME
    End Sub
    
    Private Sub cmdCancel2_Click()
       DoCmd.Close acForm, Me.NAME
    End Sub
    
    Private Sub cmdForm_Click()
    If xtyp = 1 Then
       Columns
    Else
       Tabular
    End If
    DoCmd.Close acForm, Me.NAME
    
    cmdForm_Click_Exit:
    Exit Sub
    
    cmdForm_Click_Err:
    MsgBox Err.Description, , "cmdForm_Click"
    Resume cmdForm_Click_Exit
    End Sub
    
    Private Sub cmdNext_Click()
    Dim vizlist As ListBox, lcount As Integer, chkflag As Boolean
    Dim FildList As ListBox, strName As String, strRSource As String
    Dim cdb As Database, doc As Document
    Dim Tbl As TableDef, Qry As QueryDef, QryTyp As Integer
    Dim flag As Byte, FieldCount As Integer, j As Integer
    
    On Error GoTo cmdNext_Click_Err
    
    Set vizlist = Me.WizListlcount = vizlist.listcount - 1
    
    chkflag = False
    For j = 0 To lcount
      If vizlist.Selected(j) = True Then
        xtyp = j + 1
        chkflag = True
      End If
    Next
    
    If IsNull(Me![FilesList]) = True Then
       MsgBox "Select a File from Table/Query List. ", vbOKOnly + vbExclamation, "cmdNext"
       Me.WizList.Selected(0) = True
    Else
        strFile = Me!FilesList
        Me.Page2.Visible = True
       Me.Page2.SetFocus
       Me.Page1.Visible = False
    
    Set cdb = CurrentDb
    flag = 0
    For Each Tbl In cdb.TableDefs
        If Tbl.NAME = strFile Then
           flag = 1
        End If
    Next
    For Each Qry In cdb.QueryDefs
        If Qry.NAME = strFile Then
           flag = 2
        End If
    Next
    If flag = 1 Then
        Set Tbl = cdb.TableDefs(strFile)
        Set FildList = Me.FldList
        strRSource = ""
        FieldCount = Tbl.Fields.Count - 1
        For j = 0 To FieldCount
            If Len(strRSource) = 0 Then
                strRSource = Tbl.Fields(j).NAME
            Else
                strRSource = strRSource & ";" & Tbl.Fields(j).NAME
            End If
        Next
    ElseIf flag = 2 Then
        Set Qry = cdb.QueryDefs(strFile)
        strRSource = ""
        FieldCount = Qry.Fields.Count - 1
        For j = 0 To FieldCount
            If Len(strRSource) = 0 Then
                strRSource = Qry.Fields(j).NAME
            Else
                strRSource = strRSource & ";" & Qry.Fields(j).NAME
            End If
        Next
    End If
    
    Me.FldList.RowSource = strRSource
    End If
    
    cmdNext_Click_Exit:
    Exit Sub
    
    cmdNext_Click_Err:
    MsgBox Err & ": " & Err.Description, , "cmdNext_Click"
    Resume cmdNext_Click_Exit
    End Sub
    
    Private Sub FilesList_NotInList(NewData As String, Response As Integer)
      'Not in List
    End Sub
    
    Private Sub Form_Load()
    Dim strRSource As String, FList As ComboBox
    Dim cdb As Database, MaxTables As Integer, rst As Recordset
    Dim Tbl As TableDef, Qry As QueryDef, fld As Field
    Dim j As Integer, strSQL1 As String, rstcount As Integer
    Dim MaxSeq As Integer, mMax
    
    On Error Resume Next
    
    strSQL1 = "SELECT MSysObjects.Name " & "
    FROM MSysObjects  " & "
    WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) " & "AND ((Left([Name],4))'WizQ') AND ((Left([Name],1))'~') " & "AND ((MSysObjects.Flags)=0)) " & "
    ORDER BY MSysObjects.Type, MSysObjects.Name; "
    
    mMax = 100
    DoCmd.Restore
    
    DarkBlue = 8388608
    twips = 1440
    
    Set cdb = CurrentDb
    Set Qry = cdb.QueryDefs("WizQuery")
    If Err = 3265 Then
      Set Qry = cdb.CreateQueryDef("WizQuery")
      Qry.sql = strSQL1
      cdb.QueryDefs.Append Qry
      cdb.QueryDefs.Refresh
      Err.Clear
    End If
    
    Me.FilesList.RowSource = "WizQuery"Me.FilesList.Requery
    
    Form_Open_Exit:
    Exit Sub
    
    Form_Open_Err:
    MsgBox Err & ": " & Err.Description, , "Form_Open"
    Resume Form_Open_Exit
    End Sub
    
    Private Sub cmdLeft_Click()
       LeftAll 1
    End Sub
    
    Private Sub cmdLeftAll_Click()
       LeftAll 2
    End Sub
    
    Private Sub cmdright_Click()
        RightAll 1
    End Sub
    
    Private Sub cmdRightAll_Click()
        RightAll 2
    End Sub
    
    Public Function Tabular()
    Dim cdb As Database, FldList() As String, Ctrl As Control
    Dim Rpt As Report, lngTxtLeft As Long, lngTxtTop As Long, lngTxtHeight As Long
    Dim Rpttemp As Report, lngLblleft As Long, lngLblTop As Long, lngLblheight As Long
    Dim lngtxtwidth As Long, lnglblwidth As Long, FldCheck As Boolean
    Dim strTblQry As String, intflds As Integer, lstcount As Long
    Dim RptFields As ListBox, j As Integer, mMax
    Dim PgSection As Section, DetSection As Section
    
    'Create Report with Selected Fields
    
    On Error Resume Next
    
    Set RptFields = Me.SelList
    lstcount = RptFields.listcount
    
    If lstcount = 0 Then
       MsgBox "Fields Not Selected for Report! "
       Exit Function
    Else
       lstcount = lstcount - 1
    End If
    
    ReDim FldList(0 To lstcount) As String
    
    Set cdb = CurrentDb
    Set Rpt = CreateReport
    Set PgSection = Rpt.Section(acPageHeader)
        PgSection.Height = 0.6667 * twips
    
    Set DetSection = Rpt.Section(acDetail)
        DetSection.Height = 0.1667 * twips
    
    For j = 0 To lstcount
      FldList(j) = RptFields.ItemData(j)
    Next
    
    With Rpt
        .Caption = strFile
        .RecordSource = strFile
        lngtxtwidth = 0.5 * twips
        lngTxtLeft = 0.073 * twips
        lngTxtTop = 0
        lngTxtHeight = 0.1668 * twips
        lnglblwidth = lngtxtwidth
        lngLblleft = lngTxtLeft
        lngLblTop = 0.5 * twips
        lngLblheight = lngTxtHeight
    End With
    
    For j = 0 To lstcount
       Set Ctrl = CreateReportControl(Rpt.NAME, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
       With Ctrl
           .ControlSource = FldList(j)
           .ForeColor = DarkBlue
           .BorderColor = DarkBlue
           .BorderStyle = 1
           .NAME = FldList(j)
           lngTxtLeft = lngTxtLeft + (0.5 * twips)
        End With
       Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acPageHeader, , FldList(j), lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
       With Ctrl
           .Caption = FldList(j)
           .NAME = FldList(j) & " Label"
           .Width = (0.5 * twips)
           .ForeColor = DarkBlue
           .BorderColor = DarkBlue
           .BorderColor = 0
           .BorderStyle = 1
           .FontWeight = 700 ' Bold
           lngLblleft = lngLblleft + (0.5 * twips)
        End With
    Next
    
    lnglblwidth = 4.5 * twips
    lngLblleft = 0.073 * twips
    lngLblTop = 0.0521 * twips
    lngLblheight = 0.323 * twips
    lnglblwidth = 4.5 * twips
     Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acPageHeader, , "Head1", lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
       With Ctrl
            .Caption = strFile
            .TextAlign = 2
            .Width = 4.5 * twips
            .Height = 0.38 * twips
            .ForeColor = DarkBlue
            .BorderStyle = 0
            .BorderColor = DarkBlue
            .FontName = "Times New Roman"
            .FontSize = 16
            .FontWeight = 700 ' Bold
            .FontItalic = True
            .FontUnderline = True
       End With
    
    On Error GoTo Tabular_Err
    
    Page_Footer Rpt
    
    DoCmd.OpenReport Rpt.NAME, acViewPreview
    
    Tabular_Exit:
    Exit Function
    
    Tabular_Err:
    MsgBox Err.Description, , "Tabular"
    Resume Tabular_ExitEnd Function
    
    Public Function Columns()
    Dim cdb As Database, FldList() As String, Ctrl As Control
    Dim Rpt As Report, lngTxtLeft As Long, lngTxtTop As Long, lngTxtHeight As Long
    Dim lngLblleft As Long, lngLblTop As Long, lngLblheight As Long
    Dim lngtxtwidth As Long, lnglblwidth As Long, FldCheck As Boolean
    Dim strTblQry As String, intflds As Integer, lstcount As Long
    Dim FrmFields As ListBox, j As Integer, mMax
    Dim HdSection As Section, DetSection As Section
    
    'Create Report with Selected Fields
    
    On Error Resume Next
    
    Set FrmFields = Me.SelList
    lstcount = FrmFields.listcount
    
    If lstcount = 0 Then
       MsgBox "Fields Not Selected for Report! "
       Exit Function
    Else
       lstcount = lstcount - 1
    End If
    
    ReDim FldList(0 To lstcount) As String
    
    Set cdb = CurrentDb
    Set Rpt = CreateReport
    
    Set HdSection = Rpt.Section(acPageHeader)
        HdSection.Height = 0.6667 * twips
    
    Set DetSection = Rpt.Section(acDetail)
        DetSection.Height = 0.166 * twips
    
    For j = 0 To lstcount
      FldList(j) = FrmFields.ItemData(j)
    Next
    
    With Rpt
        .Caption = strFile
        .RecordSource = strFile
        lngtxtwidth = 1.5 * twips
        lngTxtLeft = 1.1 * twips
        lngTxtTop = 0.0417 * twips
        lngTxtHeight = 0.2181 * twips
        lnglblwidth = lngtxtwidth
        lngLblleft = 0.073 * twips
        lngLblTop = 0.0417 * twips
        lngLblheight = 0.2181 * twips
    End With
    
    For j = 0 To lstcount
       Set Ctrl = CreateReportControl(Rpt.NAME, acTextBox, acDetail, , FldList(j), lngTxtLeft, lngTxtTop, lngtxtwidth, lngTxtHeight)
       With Ctrl
           .ControlSource = FldList(j)
           .FontName = "Verdana"
           .FontSize = 8
           .FontWeight = 700
           .ForeColor = DarkBlue
           .BorderColor = DarkBlue
           .NAME = FldList(j)
           .BackColor = RGB(255, 255, 255)
           .BorderStyle = 1
           .SpecialEffect = 0
           If (j / 9) = 1 Or (j / 9) = 2 Or (j / 9) = 3 Then
            lngTxtTop = (0.0417 * twips)
            lngTxtLeft = lngTxtLeft + (2.7084 * twips)
           Else
            lngTxtTop = lngTxtTop + .Height + (0.1 * twips)
           End If
        End With
    
       Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acDetail, FldList(j), FldList(j), lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
        With Ctrl
           .Caption = FldList(j)
           .Height = (0.2181 * twips)
           .NAME = FldList(j) & " Label"
           .Width = twips
           .ForeColor = 0
           .BorderStyle = 0
           .FontWeight = 400
           If (j / 9) = 1 Or (j / 9) = 2 Or (j / 9) = 3 Then
            lngLblTop = (0.0417 * twips)
            lngLblleft = lngLblleft + (2.7083 * twips)
           Else
            lngLblTop = lngLblTop + .Height + (0.1 * twips)
           End If
        End With
    Next
    
    lnglblwidth = 4.5 * twips
    lngLblleft = 0.073 * twips
    lngLblTop = 0.0521 * twips
    lngLblheight = 0.323 * twips
    lnglblwidth = 4.5 * twips
     Set Ctrl = CreateReportControl(Rpt.NAME, acLabel, acPageHeader, , "Head1", lngLblleft, lngLblTop, lnglblwidth, lngLblheight)
       With Ctrl
            .Caption = strFile
            .TextAlign = 2
            .Width = 4.5 * twips
            .Height = 0.38 * twips
            .ForeColor = DarkBlue
            .BorderStyle = 0
            .BorderColor = DarkBlue
            .FontName = "Times New Roman"
            .FontSize = 20
            .FontWeight = 700 ' Bold
            .FontItalic = True
            .FontUnderline = True
       End With
    
    DoCmd.OpenReport Rpt.NAME, acViewPreview
    
    Columns_Exit:
    Exit Function
    
    Columns_Err:
    MsgBox Err.Description, , "Columns"
    Resume Columns_Exit
    End Function
    
    Public Function Page_Footer(ByRef obj)
    Dim lngWidth As Long, ctrwidth As Long, ctrlCount As Long
    Dim j As Long, cdb As Database
    Dim lngleft As Long, lngtop As Long, LineCtrl As Control, Ctrl As Control
    Dim rptSection As Section, leftmost As Long, lngheight As Long
    Dim rightmost As Long, RightIndx As Integer
    'Note : The Controls appearing in Detail Section from left to Right
    '       is not indexed 0 to nn in the order of placing,
    '       instead 1st control placed in the Section has index value 0
    '       irrespective of its current position.
    On Error GoTo Page_Footer_Err
    
    Set cdb = CurrentDb
    Set rptSection = obj.Section(acDetail)
    
    ctrlCount = rptSection.Controls.Count - 1
    
    lngleft = rptSection.Controls(0).Left
    rightmost = rptSection.Controls(0).Left
    
    'indexed 0 control may not be the leftmost control on the Form/Report
    'so find the leftmost control's left value
    For j = 0 To ctrlCount
     leftmost = rptSection.Controls(j).Left
    
     If leftmost < lngleft Then
       lngleft = leftmost
     End If
     If leftmost > rightmost Then
       rightmost = leftmost
       RightIndx = j
     End If
    Next
    
    lngtop = 0.0208 * 1440
    lngWidth = 0: ctrwidth = 0
    
       lngWidth = rightmost + rptSection.Controls(RightIndx).Width
       lngWidth = lngWidth - lngleft
    
      Set LineCtrl = CreateReportControl(obj.NAME, acLine, acPageFooter, "", "", lngleft, lngtop, lngWidth, 0)
      Set Ctrl = LineCtrl
      LineCtrl.BorderColor = 12632256
      LineCtrl.BorderWidth = 2
      LineCtrl.NAME = "ULINE"
    
    lngtop = 0.0418 * 1440
    lngleft = LineCtrl.Left
    lngWidth = 2 * 1440
    lngheight = 0.229 * 1440
    
    'draw PageNo control at the Report footer
    Set LineCtrl = CreateReportControl(obj.NAME, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
    With LineCtrl
       .ControlSource = "='Page : ' & [page] & ' / ' & [pages] "
       .NAME = "PageNo"
       .FontName = "Verdana"
       .FontSize = 10
       .FontWeight = 700
       .TextAlign = 1
    End With
    'draw Date Control at the right edge of the Line Control
    'calculate left position of Date control
    
    lngleft = (LineCtrl.Left + Ctrl.Width) - lngWidth
    Set LineCtrl = CreateReportControl(obj.NAME, acTextBox, acPageFooter, "", "", lngleft, lngtop, lngWidth, lngheight)
    With LineCtrl
       .ControlSource = "='Date : ' & Format(Date(),'dd/mm/yyyy') "
       .NAME = "Dated"
       .FontName = "Verdana"
       .FontSize = 10
       .FontWeight = 700
       .TextAlign = 3
    End With
    
    Page_Footer_Exit:
    Exit Function
    
    Page_Footer_Err:
    MsgBox Err.Description, "Page_Footer"
    Resume Page_Footer_Exit
    End Function
    
    Private Function RightAll(ByVal SelectionType As Integer)
    Dim FldList As ListBox, SelctList As ListBox, strRSource As String
    Dim listcount As Long, j As Long, strRS2 As String
    
    On Error GoTo RightAll_Err
    If SelectionType = 0 Then
       Exit Function
    End If
    Set FldList = Me.FldListSet
     SelctList = Me.SelList
    
    listcount = FldList.listcount - 1
    strRSource = SelctList.RowSource: strRS2 = ""
    
    Select Case SelectionType
        Case 1
            For j = 0 To listcount
                If FldList.Selected(j) = True Then
                    If Len(strRSource) = 0 Then
                        strRSource = FldList.ItemData(j)
                    Else
                        strRSource = strRSource & ";" & FldList.ItemData(j)
                    End If
                Else
                   If Len(strRS2) = 0 Then
                        strRS2 = FldList.ItemData(j)
                    Else
                        strRS2 = strRS2 & ";" & FldList.ItemData(j)
                    End If
                End If
            Next
            SelctList.RowSource = strRSource
            FldList.RowSource = strRS2
            SelctList.Requery
            FldList.Requery
        Case 2
    
            For j = 0 To listcount
                If Len(strRSource) = 0 Then
                    strRSource = FldList.ItemData(j)
                Else
                   strRSource = strRSource & ";" & FldList.ItemData(j)
                End If
            Next
            SelctList.RowSource = strRSource
            FldList.RowSource = ""
            SelctList.Requery
            FldList.Requery
    End Select
    
    RightAll_Exit:
    Exit Function
    
    RightAll_Err:
    MsgBox Err.Description, , "RightAll"
    Resume RightAll_Exit
    End Function
    
    Private Function LeftAll(ByVal SelectionType As Integer)
    Dim FldList As ListBox, SelctList As ListBox, strRSource As String
    Dim listcount As Long, j As Long, strRS2 As String
    
    On Error GoTo LeftAll_Err
    
    If SelectionType = 0 Then
       Exit Function
    End If
    
    Set FldList = Me.FldListSet
     SelctList = Me.SelList
    
    listcount = SelctList.listcount - 1
    strRSource = FldList.RowSource: strRS2 = ""
    
    Select Case SelectionType
        Case 1
            For j = 0 To listcount
                If SelctList.Selected(j) = True Then
                    If Len(strRSource) = 0 Then
                        strRSource = SelctList.ItemData(j)
                    Else
                        strRSource = strRSource & ";" & SelctList.ItemData(j)
                    End If
                Else
                    If Len(strRS2) = 0 Then
                        strRS2 = SelctList.ItemData(j)
                    Else
                        strRS2 = strRS2 & ";" & SelctList.ItemData(j)
                    End If
                End If
            Next
            SelctList.RowSource = strRS2
            FldList.RowSource = strRSource
            SelctList.Requery
            FldList.Requery
        Case 2
            For j = 0 To listcount
                If Len(strRSource) = 0 Then
                    strRSource = SelctList.ItemData(j)
                Else
                    strRSource = strRSource & ";" & SelctList.ItemData(j)
                End If
            Next
            SelctList.RowSource = ""
            FldList.RowSource = strRSource
            SelctList.Requery
            FldList.RequeryEnd Select
    
    LeftAll_Exit:
    Exit Function
    
    LeftAll_Err:
    MsgBox Err.Description, , "LeftAll"
    Resume LeftAll_Exit
    
    End Function
  8. Open the ReportWizard in Normal View.
  9. Select a Table or Query from the Combo Box.
  10. Select Tabular Wizard option from above.
  11. Click OK to load the selected Table/Query Field List and open the List of Fields.
  12. Select the Fields for the Report from the List Box.
  13. Click Finish to create the Report.

Normally, after creating the Report we need to modify the Detail Section Controls to make their sizes according to the data type and field sizes. After these changes the Report Footer created by the Wizard may not match with the modification that we have made. But, we already have a solution for this in an earlier Post with the Title: Draw Page Border. One of the following two Programs presented there can be used for drawing a new Page Footer (after deleting the existing Page Footer) or to resize it after changes made to the Detail Section Controls.

  1. DrawPageFooter()
  2. ReSizePageFooter()

There are other Reports related Functions also presented there to make Report Designing tasks easier. You may take a look at them as well.

You can create beautiful 3D Headings on the Report or Form with Labels or Text Boxes (Text Box values can be drawn from data Fields). Take a look at the sample Report Headings created with a 3D-Text Creation Wizard:

The following four Posts are dedicated for 3D Text Styles and you can download the 3D-Text Wizard from any of them:

  1. Create 3D Headings on Forms
  2. Border 2D Heading Text
  3. Border 3D Heading
  4. Shadow 3D Heading Style

After creating the 3D-Text you can customize it by changing the Fonts, Fore-Color and Styles like Bold, Italics or Underline.

StumbleUpon Toolbar

Download File: ReportWizard.zip (Size:105K)