Custom Report Wizard ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

After designing and working with a Form Wizard it is natural to think about designing a Report Wizard too. The designing task of a Form and Report is almost the 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: The Custom-made Form Wizard to understand the designing steps of the Form Wizard or to download it from there.

Designing Report Wizard.

Do the following few simple steps and the Report-Wizard is ready:

Make a copy of the Form Wizard and rename it as ReportWizard.
Open the ReportWizard in Design View.
Change the List Box and Combo Box headings to read as Report Format and Select Table/Query for Report respectively.
Change the word 'Form' to Report in the left side labels.
Display the Code Module of the ReportWizard by selecting View - ->Code (or Alt+F11).
Press Ctrl+A to select the entire code in the Form Module and press Delete Key to delete the Code.

Copy and Paste the code given below into the ReportWizard Form Module and save the Form:

Report Wizard VBA Code

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

Create Left-side ListBox Items.

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

Create Right-side ListBox Items.

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

Create Report In Tabular Format.

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

Create Report In Columns Format.

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

Create Report's Page-Footer Contents.

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

Try out the Report Wizard.

Open the ReportWizard in Normal View.
Select a Table or Query from the Combo Box.
Select the Tabular Wizard option from above.
Click OK to load the selected Table/Query Field List and open the List of Fields.
Select the Fields for the Report from the List Box.
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 are made to the Detail Section Controls.

DrawPageFooter()
ReSizePageFooter()

There are other Reports related to 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: