The central routine is VSReport...

Previous to calling this routine, an array of fields (to include in the report) has been created through a series of calls to the library - one call for each field. Also the default font attributes, for different sections, can be altered through other calls before calling this routine. You will note that there are two parameters to this function to specify fields, to group and sort by. In a more enhanced version, arrays of such fields could also be built up through separate calls. For this simpler version, however, this is the easiest way to handle it.

  Sub VSReport (ByVal sTitle_IN As String, sDB_IN As Database, 
                ByVal sTable_IN As String, ByVal sOrderBy_IN As String, 
                ByVal sGroupBy_IN As String)
    Dim frmPrev As New frmVSPreview
    Dim ssReport As SnapShot
    Dim sSQL As String
    Dim iDoGroup As Integer
    Dim sPrevGroup As String
    Dim iCount As Integer
    Dim sOrderClause As String
    Dim sTableForm As String
    Dim sTablehead As String
    Dim sTabletemp As String
    Dim iDetail As Integer

  On Error GoTo VSReportError
    Busy
   
    ' Initialise the section formatting attributes
    If Not miSectionAttrInit Then
      InitSectionAttrs
    End If

    ' Ensure that we have some fields to report
    If miNumFields = 0 Then GoTo NoFields
   
    frmPrev.Caption = sTitle_IN
    frmPrev.vsPrinter.FileName = sTitle_IN
    ' Build order by clause for ordering AND grouping
    sOrderClause = sOrderBy_IN
    If sGroupBy_IN <> "" Then
      iDoGroup = True
      If sOrderBy_IN = "" Then
        sOrderClause = sGroupBy_IN
      Else
        sOrderClause = sGroupBy_IN & ", " & sOrderBy_IN
      End If
    End If
    ' Build field list and other bits of SQL
    sSQL = "SELECT "
    For iCount = 1 To miNumFields
      sSQL = sSQL & mFields(iCount).Name & ","
      ' Build a table header string to spec the table layout to
      ' the VSPrinter.
      If mFields(iCount).Name <>: sGroupBy_IN Then
        sTableForm = sTableForm & "<" & mFields(iCount).Width & "|"
        sTablehead = sTablehead & mFields(iCount).Title & "|"
      Else
        mFields(iCount).GroupField = True
      End If
    Next iCount
    sTableForm = Left$(sTableForm, Len(sTableForm) - 1) & ";"
    sTablehead = Left$(sTablehead, Len(sTablehead) - 1) & ";"
   
    ' Put the sections of the SQL statement together
    sSQL = Left$(sSQL, Len(sSQL) - 1)
    sSQL = sSQL & " FROM " & sTable_IN
    If sOrderClause <> "" Then
      sSQL = sSQL & " ORDER BY " & sOrderClause
    End If
    Set ssReport = sDB_IN.CreateSnapshot(sSQL)

    ' Start the document - report
    frmPrev.vsPrinter.Action = 3 ' StartDoc
    ' Set the header and footer text
    frmPrev.vsPrinter.Header = sTitle_IN
    frmPrev.vsPrinter.Footer = "|Page %d|"
    sTabletemp = sTableForm & sTablehead
    iDetail = False
    Do
      If iDoGroup Then
        If StrComp(ssReport(sGroupBy_IN) & "", sPrevGroup, 1) <> 0 Then
          ' New Group Detected
          sPrevGroup = ssReport(sGroupBy_IN)
          ' Print details of last group if there was one
          If iDetail Then
            ' Include group totals if we are doing that.
            If miDoGroups <> False Then
              sTabletemp = sTabletemp & GetGroupTotals()
              ClearGroupTotals
            End If
            PrintDetailTable sTabletemp, frmPrev.vsPrinter
          End If
            
          ' Print group heading
          SetFontCurrent "GroupHeader", frmPrev.vsPrinter
          frmPrev.vsPrinter.Paragraph = ssReport(sGroupBy_IN) & ""
          SetFontCurrent "Detail", frmPrev.vsPrinter
            
          ' Start new detail section
          sTabletemp = sTableForm & sTablehead
        End If
      End If
      ' We have got to a detail section. Set the flag.
      iDetail = True
      For iCount = 0 To ssReport.Fields.Count - 1
        ' Build a table row of all the fields, with their formats.
        If Not mFields(iCount + 1).GroupField Then
          sTabletemp = sTabletemp & Format$(ssReport.Fields(iCount).Value, 
                                            mFields(iCount + 1).Format) & "|"
          If mFields(iCount + 1).DoGroup Then
            AddGroupTotal (iCount + 1), ssReport.Fields(iCount).Value
          End If
          If mFields(iCount + 1).DoGrand Then
            AddGrandTotal iCount + 1, ssReport.Fields(iCount).Value
          End If
        End If
      Next iCount
      sTabletemp = Left$(sTabletemp, Len(sTabletemp) - 1) & ";"
      ssReport.MoveNext
    Loop Until ssReport.EOF
    ' Check if we need to do group totals.
    If miDoGroups <> False Then
      sTabletemp = sTabletemp & GetGroupTotals()
      ClearGroupTotals
    End If
    PrintDetailTable sTabletemp, frmPrev.vsPrinter

    ' Print a grand total section if there is to be one
    If miDoGrands <> False Then
      SetFontCurrent "GroupHeader", frmPrev.vsPrinter
      frmPrev.vsPrinter.Paragraph = "Grand Totals"
      SetFontCurrent "Detail", frmPrev.vsPrinter
      PrintDetailTable sTableForm & GetGrandTotals(), frmPrev.vsPrinter
    End If
   
    ' Finalise the report
    frmPrev.vsPrinter.Action = 6 ' EndDoc
    frmPrev.vsPrinter.Visible = True
    KillFieldList
    InitSectionAttrs

    ssReport.Close
    Set ssReport = Nothing
  VSReportEnd:
    UnBusy
    Exit Sub
  NoFields:
    MsgBox "There have been no fields defined.", 64, "Report Error"
    Resume VSReportEnd
  VSReportError:
    MsgBox "Error Encountered producing report." & ErrMsg(), 48, "Report Error"
    If Not frmPrev Is Nothing Then
      Unload frmPrev
      Set frmPrev = Nothing
    End If
    Resume VSReportEnd
  End Sub

Notice that the second parameter is a database object from which to get the data. This allows you to simply pass in normal JET databases, ODBC databases, or ISAM databases seamlessly. There is also a wrapper for the VSReport routine which accepts all the same parameters except that it accepts an mdb path and file name instead of a database object. That procedure simply opens up a database object and then passes on all the parameters (including that database object) to the VSReport procedure. It's simple but it saves you from having to have a database object to pass in, and it makes the library more flexible - which I like to do.

---

[HOME] [TABLE OF CONTENTS] [SEARCH]