Docstoc

Data Bound Controls And Data Report Designer Vivek Gupta IT Systems Group Indian Institute of Management Lucknow

Document Sample
Data Bound Controls And Data Report Designer Vivek Gupta IT Systems Group Indian Institute of Management Lucknow Powered By Docstoc
					Data Bound Controls
        And
Data Report Designer


          Vivek Gupta
      IT & Systems Group
 Indian Institute of Management
            Lucknow




                                  1




                                  2
           Page 1 of 22
                       3




         Using
DataList, DataCombo &
  DataGrid Controls


                       4
        Page 2 of 22
                   DataList Control
• Allows us to display multiple rows of data in the same control,
  giving us a 'pick list' of values.
   – Automatically filled with a field from a specified data
      control.
   – Selections from the list box can then be used to update
      another field from the same data control or, optionally, used
      to update a field from another data control.
   – Is not the same as the standard list control, which is not
      data bound.
   – To load this control, select Components from the Projects
      menu, then select from the Components dialog box:
• DAO Version:
   – Microsoft Data Bound List Controls
• ADO Version:
   – Microsoft DataList Controls

                                                                          5


                   DataList Control
•   Data Bound List Control Properties:
    –   BoundColumn: Name of field in Recordset specified by RowSource
        to be passed to DataField, once selection is made.
    –   BoundText: Text value of BoundColumn field. This is the value
        passed to DataField property.
    –   DataField: Name of field in table specified by DataSource (or
        DataMember) updated by selection.
    –   DataSource: Name of data control (DAO or ADO) or data
        environment (ADO) that is updated by the selection.
    –   DataMember: Specifies the Command object establishing the table
        updated by the selection (ADO data environment only).
    –   ListField: Name of field in table specified by RowSource (or
        RowMember) used to fill list box.
    –   RowMember: Specifies the Command object establishing the table
        used by ListField (ADO data environment only).
    –   RowSource: Name of data control (DAO or ADO) or data
        environment (ADO) used as source of items in list box.
    –   Text: Text value of selected item in list
•   Data Bound List Control Event :
    –   Click: Invoked when item in list control is clicked

                                                                          6
                                Page 3 of 22
                    DataList Control
•   One use for the data bound list control is to fill the list (ListField)
    from the database (RowSource), then allow selections.
    –   This allows us to list all values of a particular field in a database
        recordset.
    –   The selections can be used by any control on a form, whether it is
        data bound or not.

•   A powerful feature of the data bound list control is linking to (and
    updating) other fields in a database.
    –   This involves setting three more properties (DataSource, DataField,
        and BoundColumn).
        »    RowSource is set to the data control establishing the recordset
             providing the information listed (ListField) in the list box.
             •   This establishes the source table.
        »    BoundColumn is the field name from the source table record used
             as a link.
        »    DataSource is the recordset linked by the BoundColumn (it can be
             the same recordset as RowSource or another recordset).
        »    Then, DataField is the field in DataSource that is linked by
             BoundColumn.


                                                                                7



                          DataCombo
• Nearly identical to the data bound list box.
• Primary difference between the two controls is the
  way data is displayed:
    – the combo control has a list box portion and a text box
      portion that displays the selected item, and,
    – with the combo control, the user is (optionally) given
      the opportunity to type in a choice not in the list box.
    – to load this control, select Components from the
      Projects menu, then select from the Components
      dialog box:
• DAO Version: Microsoft Data Bound List Controls
• ADO Version: Microsoft DataList Controls

                                                                                8
                                    Page 4 of 22
                           DataCombo
• Data display is different with the combo control.
    – Display is established by the Style property:
• Style Symbolic Constant                Description
    0   VbComboDropDown                   Drop-down list box, user
                                             can change selection
    1   VbComboSimple                     Displayed list box, user
                                             can change selection
    2    VbComboDropDownList              Drop-down list box, user
                                              cannot change selection

• When using Style = 1, make sure you sufficiently size the control (so
  the list box portion appears) when it is placed on the form.
• When to use the combo control instead of the list box control?
    – an excellent data entry control.
    – advantage over list box:
        » it provides experienced users the ability to type in values they know
          are correct, speeding up the data entry process.
             • The list box control does not allow any typing.
    – It is also a good control when you are short on form space.
    – Using Style = 2 replicates the functionality of the list box control
      without needing space for the list box.

                                                                                   9



                              DataGrid
• One of the most useful data bound controls.
    – It can display an entire database table,
      referenced by a data control.
        »    The table can then be edited as desired.
    – To load this control, select Components
      from the Projects menu, then select from the
      Components dialog box:

•    DAO Version: Microsoft Data Bound Grid Control
•    ADO Version: Microsoft DataGrid Control 6.0



                                                                                  10
                                   Page 5 of 22
        DataBound FlexGrid Controls
•    The data bound grid control is an excellent tool for providing
     full-editing features in a spreadsheet like fashion.
    – However, it is memory intensive and not real fast.

•    If we only need display capabilities (no editing), consider the
     data bound flexgrid control.
    – This control offers an amazing array of variations in how
         data can be displayed.
    – To load this control, select Components from the Projects
         menu, then select from the Components dialog

•   DAO/ADO Version: Microsoft FlexGrid Control
•   The flexgrid control offers a wide number of properties that
    allows us to display the data in any format we desire.
    –    We can change the color and style of nearly every piece of
         information in the grid (gridlines, fonts, colors, selections, …).

                                                                                 11

The Vendors and New Vendor forms


                                                             DataCombo

                                                               DataList
                                                               Multiline text-
                                                               box




              DataGrid

                                                                                 12
                                Page 6 of 22
    Application description
    • The DataCombo control on the Vendors form lets the user
      choose the name of a vendor from a drop-down list.
    • If the user clicks on the New button on the Vendors form,
      the New Vendor form is displayed so the user can enter a
      new vendor.
    • If the user clicks on the Delete button on the Vendors form,
      the current record is deleted. However, this button is disabled
      if the vendor has any unpaid invoices.
    • The DataList control on the Vendors form displays the
      invoice numbers for all of the selected vendor’s unpaid
      invoices.
    • The DataGrid control on the Vendors form displays detail
      information for all of the selected vendor’s unpaid invoices.
    • The multi-line text box is just a text box with its MultiLine
      property set to true. In this application, it is used to display
      some of the fields in the vendor record.

                                                                         13




DataList and DataCombo properties
Property           Description
DataSource         The name of the data source that the control is
                   bound to. This is usually a data control or a
                   DataEnvironment object
DataMember         If you select a DataEnvironment object for the
                   DataSource property, use this property to
                   select the Command object that you want to
                   use
DataField          The name of the field that the control is
                   bound to.




                                                                         14
                              Page 7 of 22
DataList and DataCombo properties (continued)
Property        Description
RowSource       The name of the data source that provides the
                data to be displayed. This is usually a data
                control or a DataEnvironment object.
RowMember       If you select a DataEnvironment object for the
                RowSource property, use this property to
                select the Command object that you want to
                use.
ListField       The name of the field in the data source that’s
                displayed by the control.




                                                                   15



  DataList and DataCombo properties (continued)
  Pro pe rty      De s c riptio n
  BoundColumn     The field defined by the RowSource property
                  that is used to pass data to the field defined
                  by the DataSource and DataField properties.
  BoundText       This property returns or sets the value of the
                  current field specified by the BoundColumn
                  property
  DataFormat      Determines the format in which the bound
                  data is displayed.
  MatchEntry      Determines how the data in a control is
                  searched as text is entered.
  Style           (DataCombo only) Determines whether the
                  list portion of a combo box drops down or is
                  fixed. It also determines whether text can be
                  entered into the text portion of the box.

                                                                   16
                           Page 8 of 22
DataCombo and DataList notes
• When you use a DataCombo and DataList control, you can bind the
  control to a field that’s defined by one data control but display data from
  a field that’s defined by another data control.
• The field that’s specified in the BoundColumn property is the field
  that’s common to both of the recordsets that are defined by the data
  controls.
• To bind a DataList or DataCombo control to a field, use the DataSource
  and DataField properties. To display data in a DataList or DataCombo
  control, use the RowSource and ListField properties.
• If you create a DataEnvironment object, you can use it for a DataSource
  or RowSource property. In that case, you use the DataMember or
  RowMember property to specify the command that you want to use
  within the DataEnvironment object.
• Is this confusing? That's one of the problems with the use of bound
  forms. And if the properties aren't set right, the application doesn't work
  right.

                                                                           17

 The shortcut menu for a DataGrid control




                                                                           18
                                Page 9 of 22
 DataGrid notes
 • You use the DataSource property of the DataGrid
   control to establish its source of data. This
   property provides the name of the ADO data
   control that the grid is bound to.
 • Once you bind a DataGrid control to an ADO
   data control, you can use the Retrieve Fields
   command in the DataGrid’s shortcut menu to add
   fields to the grid with each field as one column in
   the DataGrid.
 • If you choose the Edit command from its shortcut
   menu, the DataGrid enters into UI-active mode.
   In this mode, you can use the commands in the
   UI-active shortcut menu to change the layout of
   the columns and rows in the DataGrid control.
 • The Cut, Copy, Paste, Delete, Insert, and Append
   commands in the UI-active shortcut menu let you
   manipulate the columns in a DataGrid control.
                                                         19


The Property Pages for a DataGrid
control




                                                         20
                      Page 10 of 22
     Common DataGrid properties
       Tab              Property
       General          AllowAddNew
       General          AllowDelete
       General          AllowUpdate
       Columns          Caption
       Columns          DataField
       Layout           Locked
       Layout           AllowSizing
       Layout           Alignment
       Format           FormatType



                                                            21


               Database Reports
• Users expect to have the ability to obtain printed
  copies of information contained within a database.
   – This information is in the form of database reports.
   – Generating reports is a straightforward task.
      » We just need to determine what information we want in
        the report and how we want it to be presented.
• Three approaches to developing database reports.
  Each has its advantages and disadvantages.
   – The methods are:
      » the Visual Basic Printer Object,
      » the Crystal Reports report writer, and
      » the ADO data environment Data Report capabilities


                                                            22
                        Page 11 of 22
ADO Data Environment Data Report
• With VB6, Microsoft added a new way to produce database reports -
  the Data Report.
    – This Data Report ability lets us build a report preview screen, with
      print and export (even to the Internet) capabilities from an ADO data
      environment (it will not work with the DAO or ADO data control).
    – All we need to do is provide the data and establish the layout.
    – With future releases of VB, the Data Report is sure to become the
      accepted standard for database reports.
• Like a Form, the Data Report has its own set of properties and
  methods that control its appearance and use.
• The steps involved in using Data Report:
    – Set up the ADO recordset to be used by the report.
    – Add a Data Report object to your Visual Basic project.
    – Design the report by placing fields and other information on the Data
      Report designer form.
    – Write code to display the report in your program
• The ADO Data Report capabilities are vast and using them is a
  detailed process.



                                                                              23


              Data Report Properties
• The Data Report has a number of properties that governs appearance
  of the resulting report and how the report is connected to the data
  source. Some important properties are:
    – BorderStyle: Determines whether the displayed report window can be
      resized.
    – Caption: Title information at top of data report window.
    – DataMember: Specifies the Command object establishing the database
      report table.
    – DataSource: Name of data environment report is bound to.
    – BottomMargin: Bottom margin of printed report in twips.
    – TopMargin: Top margin of printed report in twips.
    – LeftMargin: Left margin of printed report in twips.
    – RightMargin: Right margin of printed report in twips.
    – ReportWidth: Width of database report in twips.
    – Title: Listed title for database report
• The three methods that control the database report:
    – ExportReport: Exports the report to a specified file (graphics cannot be
      exported).
    – PrintReport: Sends the report to a printer.
    – Show: Displays the report in its own window.

                                                                              24
                                Page 12 of 22
             Data Report Designer
• To add a Data Report object to our VB
  application, click the Project menu item and
  select Add Data Report. The Data Report
  Designer appears.




                                                                     25


             Data Report Designer
• The Data Report is made up of five bands:
   – Report Header: Information placed at the top of the
     database report. Usually contains titling and dates.
   – Page Header: Information placed at the top of each page in
     the database report.
   – Detail: Most critical section of report. Displays the
     information from the recordsets.
   – Page Footer: Information placed at the bottom of each page
     in the database report.
   – Report Footer: Information placed at the end of the
     database report.
• Each band also has a Section Name.
   – Each section has its own individual properties that can be
     accessed at either design or run time.
   – To display the properties for a particular section, click the
     section's gray title bar.
   – To display the properties for the Data Report object, click
     the square in the upper left corner of the designer.
                                                                     26
                           Page 13 of 22
             Data Report Designer
• To build a database report, we place information on the
  designer form.
   – This information is contained in a set of Data Report Tools.
   – When a data report is added to a VB project, a separate set
     of tools is added to the toolbox.
   – To access these tools, click the Data Report button in the
     toolbox (the usual tools are now accessed by clicking
     General).
• The data report controls are:
   – rptLabel: Specifies a label used to display information that
     is not data bound.
   – rptTextBox: Displays the contents of a database field.
   – rptImage: Contains a graphic image.
   – rptLine: Enables you to place lines on the report.
   – rptShape: Enables you to place shapes on the report.
   – rptFunction: Allows you to place a field in the header or
     footer with some computed summary information

                                                                    27


     A new data report that’s based on a
     DataEnvironment object




                                                                    28
                           Page 14 of 22
  How to create a data report
  • Before you create a data report, create a
    DataEnvironment object, a Connection object,
    and a Command object that defines the data for
    the report.
  • Then, to create a data report, open the Data
    Report window.
  • To associate the data report with the Command
    object you created, set the DataSource property
    of the report to the name of the DataEnvironment
    object that contains the Command object, and set
    the DataMember property to the name of the
    Command object.
  • To format the sections of the report based on the
    structure of the Command object, select the
    Retrieve Structure command from the shortcut
    menu of the Data Report window.
                                                        29


T h e d e sig n o f a re p o rt




                                                        30
                        Page 15 of 22
   How to work in the Data Report window
   • To add a field to a report, drag it from the list of fields
     for the Command object in the Data Environment
     Designer window to the appropriate section of the report.
   • When you add a field to a report, the DataMember and
     DataField properties of the text box that’s created are set
     automatically so they refer to the appropriate field.
   • You can also create a control that displays a field by
     adding a text box to the report and then setting its
     DataMember and DataField properties manually.
   • To add other controls to a report, select the control from
     the DataReport section of the Toolbox and then drag in
     the report to place and size the control.
   • To change the height of a section, drag the bottom border
     of the section. To change the width of all the sections in
     the report, drag the right border of the report.


                                                                   31


The shortcut menu for inserting a predefined field




                                                                   32
                            Page 16 of 22
Codes for predefined fields
Co de   De s c riptio n
%p      The current page number
%P      The total number of pages
%d      The current date (short format)
%D      The current date (long format)
%t      The current time (short format)
%T      The current time (long format)
%i      The report title (from the Title property of
        the DataReport object)




                                                                33




  How to add a predefined field to a report
  • Display the shortcut menu for the section where you
    want to add the field and then select the field you want
    to add from the Insert Control submenu.
  • You can also include a predefined field by adding a label
    control and then including the code for the field in the
    control’s Caption property.




                                                                34
                          Page 17 of 22
The vendor phone list report in print preview




                                                                  35




 How to format, preview, and print a report
 • To preview a report, set the Startup Object property of
   the project to the name of the data report. Then, run the
   project.
 • You can use the controls in the Print Preview window to
   zoom in and out of the report and to scroll through the
   pages of the report. To print the report, click on the Print
   toolbar button.
 • To change the margins of a report or to control where
   page breaks occur, use the Report and Section properties.




                                                                  36
                          Page 18 of 22
The properties for formatting a report for
printing
Obje c t   Pro pe rty           De s c riptio n
Report     TopMargin            The margins of the report in
           BottomMargin         twips. By default, the margins
           LeftMargin           are set to 1440 (about one inch).
           RightMargin
Section    ForcePageBreak       Forces a page break before or
                                after the section. The available
                                settings are rptPageBreakNone,
                                rptPageBreakBefore,
                                rptPageBreakAfter, and
                                rptPageBreakBeforeAndAfter.
           KeepTogether         Causes the section to begin
                                printing on the following page if
                                the whole section won’t fit on
                                the current page.

                                                                    37



   A report that group s invo ices by vendor




                                                                    38
                          Page 19 of 22
How to create groups based on fields
• You can use the Grouping tab in the Properties dialog
  box for a Command object to group the records retrieved
  by the command.
• To create a group, check the Group Command Object
  box, enter the name you want to use for the group, and
  select the fields you want to include in the group.
• After you create a group, it will appear in the Data
  Environment Designer window with two subordinate
  folders. The first folder contains the summary fields for
  the group, and the second folder contains the detail
  fields.
• If you base a report on a Command object that includes
  grouping, the appropriate Group Header and Group
  Footer sections are added to the report when you retrieve
  the structure of the Command object.


                                                              39



A report based on a command hierarchy




                                                              40
                       Page 20 of 22
    How to create groups based on
    hierarchical relationships
    • You can use the Data Environment Designer to
      create a command hierarchy that consists of two
      or more levels of parent-child relationships.
    • When you retrieve the structure of a command
      hierarchy into a data report, Group Header and
      Group Footer sections are added to the report for
      each parent command in the hierarchy.
    • Then, you can add the fields in the parent
      commands to the appropriate group sections, and
      you can add the fields in the child command to
      the Detail section.



                                                                 41



The syntax of the Show method
 Datareport.Show

The syntax of the PrintReport method
 Datareport.PrintReport [ShowDialog][,Range]
     [,PageFrom][,PageTo]


Arg ume nt    De s c riptio n
ShowDialog    Determines if the Print dialog box is displayed
              before the report is printed.
Range         Determines what pages of the report are printed.
              The available settings are rptRangeAllPages
              (the default) and rptRangeFromTo.
PageFrom      The numbers of the first and last pages to be
PageTo        printed. If you omit PageFrom, the report is
              printed from page 1. If you omit PageTo, the
              report is printed through the last page.

                                                                 42
                        Page 21 of 22
A statement that displays a report in Print Preview
  drVendorPhoneList.Show

A statement that displays the Print dialog box for a
report
  drYTDPurchases.PrintReport True

A statement that prints the first five pages of a
report
  drInvoicesByVendorDetail.PrintReport , rptRangeFromTo, , 5

Notes
• You can issue the Show or PrintReport method from a form or
  standard module.
• You can also export the text in a report to a file using the
  ExportReport method.


                                                                    43




     How to create a data report without a
     DataEnvironment object
     • Open a Connection object and create a Recordset object on
       that connection using the Execute method. Then, set the
       DataSource property of the data report to the Recordset
       object.
     • Because the DataReport object doesn’t have a Load event,
       you have to set its data source from a form or standard
       module.
     • When you design a data report that’s not associated with a
       DataEnviroment object, you include a field from the data
       source by adding a text box to the report and then setting
       its DataField property to the name of the field.




                                                                    44
                            Page 22 of 22

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:342
posted:7/8/2011
language:English
pages:22
Description: Print Datareport Visual Basic 6.0 document sample