Creating reports using Data Report

Steps:

1.     On your sales system folder-create reports folder

2.     Project-Components-Designers-Data Report

3.     On the project explorer, right click project->click add-Data Report and save to to reports folder.

4.     Using the label and text control on data report create report as shown below:

5.     Create CustomerRecordUI form(refer to previous tutorial-2.Displaying data from the database)

Sidenote: On your project explorer, your folder structure should be like this:

6.     On AppToolBox.bas type the following:

    Public Const cLftMargin = 800

    Public Const cTopMargin = 800

    Public Const cRytMargin = 800

    Public Const cBtmMargin = 800

7.     On dbs.bas type the following:

    Public db As New Connection

    Public Sub OpenConnection()

        'validation

        If db.State = 1 Then db.Close()

        db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _

                                     & "Data Source=" & App.Path & "\Database\SALES.mdb"

        db.Open(db.ConnectionString)

    End Sub

    Public Sub Execute(ByVal sql As String)

        'validation

        db.Execute(sql)

    End Sub

    Public Sub CloseConnection()

        'validation

        db.Close()

    End Sub

8.     On CustomerRecordUI code window, type following:

Private Sub Form_Load()

        Call DisplayCustomers()

    End Sub

    Public Sub DisplayCustomers()

        dbs.OpenConnection()

        Dim rs As New Recordset

        rs.Open("Customers", dbs.db)

        If rs.BOF = True And rs.EOF = True Then Exit Sub

        Me.lvwCustomer.ListItems.Clear()

        Dim counter As Integer

        counter = 0

        Do Until rs.EOF

            Customer = lvwCustomer.ListItems.add(, , rs.Fields("id"))

            With Customer

                .SubItems(1) = rs.Fields("name")

                .SubItems(2) = rs.Fields("address")

                .SubItems(3) = rs.Fields("contactno")

                .SubItems(4) = Format(rs.Fields("creditlimit"), "#,##0.00")

            End With

            counter = counter + 1

            rs.MoveNext()

        Loop

        rs.Close()

        dbs.CloseConnection()

        Me.stbCustomer.Panels(1).Text = "Total Customers: " & counter

    End Sub

    Private Sub mnuReports_Click()

        dbs.OpenConnection()

        Dim rs As New Recordset

        rs.Open("Customers", dbs.db)

        With CustomersRPT

            .TopMargin = cTopMargin

            .BottomMargin = cBtmMargin

            .RightMargin = cRytMargin

            .LeftMargin = cLftMargin

            .DataSource = rs

            .Sections("pageFooter").Controls("totalCustomers").Caption = Me.stbCustomer.Panels(1).Text

        End With

        Dim ReportViewer As Object

        ReportViewer = CustomersRPT

        ReportViewer.Show()

    End Sub