Return to IFS 110 class Notes Menu!

Project 6
Database Management
and
Reporting Applications

Introduction

• Add-ins are extensions to Visual Basic that add special capabilities to development environment that are not available automatically in Visual Basic
• Microsoft and other vendors have created several add-ins that can be used in your applications

Add-ins Professional Edition of VB6

• Visual Data Manager - used to add tables to an existing database
  • Data Form Designer - facilitates form creation for database application (on Utility Menu of Visual Data Manager)
• Report Designer - used to create a report that can be viewed on desktop and printed

Train Track Inventory System

• Data-bound ComboBox control (DBCombo control) used for Railroad Name field
• edit field by typing or clicking item in list
• list of values for Railroad Name field changes over time
  • additional table will be added to Trains database to hold these values
  • these values changed by choosing Options menu then Edit Railroad List from the main form

Options Menu

• Find - opens Find dialog box allowing search by stock number
• Edit Railroad List - allows edit of the Railroad Name field values
• Report - display a report on the desktop in its own window (Crystal Reports)

Visual Data Manager

• Visual Data Manager (VisData) - application created using VB5
• Version of VisData used as Visual Basic add-in, accessible from Add-Ins menu
• VisData can create and modify databases; design data forms; build, view, execute, and save database queries
• VisData Form Designer used to create data forms that can be added to VB projects

Databases Supported by VisData

• Access (version 7 or 2)
• dBase (version 5, IV, or III)
• FoxPro (version 3, 2.6, 2.5, or 2.0)
• Paradox (version 5, 4.x, or 3.x)
• Text Files

Data Form Designer

• Data Form Designer - utility that adds forms to current VB project
• Useful for creating forms for browsing and modifying data from a simple table to complex query
• Available only when VisData has been opened using the Visual Data Manager command on the Visual Basic Add-Ins menu

Modifying Data Form Code

• Data Form Designer wrote several event procedures that need modification
• Data Form Designer added five command buttons and their corresponding Click events to Railroad form (Add, Delete, Refresh, Update, and Close)
• Refresh button was deleted from interface, but its code was not removed, when this happens VB moves the code to General section of form’s code module

cmdAdd_Click Event

• AddNew method - creates and adds a new record in the Recordset object
• This method sets fields to default values, if no default values specified, sets fields to Null
• Record that was current before AddNew used remains current
• To make new record current set Bookmark property to bookmark identified by LastModified property value

cmdUpdate_Click Event

• UpdateRecord method - saves current contents of bound controls to database during the Validate event without triggering Validate event again
• After applying the UpdateRecord method, cmdUpdate_Click event procedure sets the bookmark of Recordset object to be equal to the value of the Recordset object’s LastModified property

Bookmarks

• Bookmark - is a property of Recordset object that contains a binary string identifying the current record
• LastModified property of a Recordset object is a bookmark indicating the most recently added or changed record

cmdDelete_Click Event

• Delete method - removes the current record and makes it inaccessible
• Although you cannot edit or use deleted record, it remains current until you move to another record
• Once you move to another record you cannot make the deleted record current again

cmdClose_Click Event

• Unloads form from memory, using the Me keyword
• Me keyword - behaves like an implicitly declared variable
• When a class such as a form can have more than one instance, Me provides a way to refer to the specific instance of the class where the code executing
• Unload Me, applies Unload method to current form

Validate Event

• Validate event occurs before a different record becomes the current record of a Data control
• before Update, Delete, or Unload method or Close operation
• can use this event to insure data entered by user is valid, if necessary change values or notify user (figure 6-34 VB 6.19)

Reposition Event

• When Data control loaded, 1st record in Recordset object becomes current record, triggering the Reposition event
• Reposition event occurs - when user clicks any buttons on the Data control or uses one of the move methods (MoveNext) to move from record to record
• frmRailroad Data1_Reposition event used to display current record in caption of Data control

Error Event

• Error event of Data control occurs only as the result of a data access error that takes place when no Visual Basic procedure code is being executed, such as when a user clicks on a Data control button
• Data control automatically opens a database and loads a Recordset object after Form_Load event, or after a custom control performs an operation such as the MoveNext, AddNew or Delete method. If an error results from these actions, the Error event occurs
• If you do not write a code procedure for a Data control’s Error event, VB displays a message associated with the error

Visual Basic Data Form Designer

• Data Form Designer - automatically generates forms that contain individual bound controls and procedures used to manage information from database tables
• Can use Data Form Designer to create either single query forms to manage data from a single table or Master/Detail type forms used to manage more complex one-to-many data relationships

Modifying Inventory Form
(three-step process)

1. Modify control interface
2. Modify properties
3. Modify Code

Adding ActiveX Controls

Finished Inventory form contains two ActiveX controls to be added

1. Data-bound ComboBox (DBCombo) control
2. Crystal Reports Control

• Before ActiveX controls can be added to a form they must be added to the Toolbox

Modifying the Interface

• Replace RailroadName text box with data-bound ComboBox control, adding Data control for the databound ComboBox control, and creating a menu
• DBCombo control is data-bound combo box with a drop-down list box automatically populated from a field in attached Data control
• DBCombo control can be used to update a field in a related table of another Data control

Modifying Properties

• RowSource property of DBCombo control sets a value that specifies the Data control from which the DBCombo control’s list is filled
• in addition to using table name for value of RecordSource property, you can use an SQL statement to populate the Recordset object with records sorted in a certain order
• ListField property - sets the name of the field in the Recordset object used to fill the DBCombo’s list

SQL (Structured Query Language)

• Data Form Designer named Data control Data1. When you ran the designer, specified table was Inventory and records should be sorted by StockNumber. In the Properties window, the value of Data1’s RecordSource property is not a table name but an SQL statement

select * from [Inventory] Order by [StockNumber]

Setting Startup Form

• Form1 by default is Startup form of project
• If Startup form is removed value changed automatically to Sub_Main, even if project contains other forms
• Because original Startup form was removed and new form frmInventory was added, must specify it as the Startup form for the project

Modifying Code

• Data Form Designer created several event procedures
• Modify Data1_Reposition and cmdUpdate_Click event procedures
• Write event procedures for Form_Activate and mnuRailroads_Click events
• Later in project write code for mnuFind_Click and mnuReport_Click event procedures

SelLength property

• When DBCombo control displays a field value for current record the value displays highlighted in DBCombo’s text box
• allows user to replace highlighted text by typing (don’t want this, need to deselect the text)
• SelLength property - sets the number of characters selected (DBCombo1.SelLength = 0)
• Code placed in Reposition event

Refresh method

• When new record is added to inventory, it is added at end of database table
• necessary to sort records each time new one added

• accomplished by applying Refresh method to Data1 Data control each time Update button clicked
• (Data1.Refresh code statement added to cmdUpdate_Click event procedure)

Refresh Method

• Changes in Railroads table from Railroads form should be reflected in RailroadsName list box on the Inventory form (For this to occur, Data2 control on frmInventory must be refreshed with Data2.Refresh)
• The Activate event is the appropriate place to refresh the Data2 control
• Activate event of form occurs each time a form receives focus

Writing a Recordset Search Routine

stocknum = InputBox ("Enter Stock Number", "Find")

• assigns user’s input to a variable named stocknum
• routine begins by creating string variable named SearchString that contains the criteria to be used for search in SQL-like syntax

SearchString = "StockNumber = " & Val (stocknum)

• Because StockNumber field in database has number format, a search for nonnumeric value of stocknum variable will cause run-time error. To avoid this, the Val function is used to convert any text characters into the number zero

Writing Recordset Search Routine

• FindFirst method - locates first record in Recordset object that satisfies specified criteria and makes that record current

Data1.Recordset.FindFirst SearchString

• NoMatch property - automatically given True/False value indicating whether a particular record found using one of the Find methods

If Data1.Recordset.NoMatch = True Then

Report Designer Add-In

• Report Designer Add-In in VB is special version of a report generating software product called Crystal Reports
• Report Expert - wizard included in Crystal Reports
• supply information through a series of tab sheets and the wizard creates report
• can edit and modify report created by wizard

Important Note Visual Basic 6 users:

• If Crystal Reports is not accessible through the Report Designer on the Add-Ins menu, it is not installed. Execute the file Crystl32 in the Common\Tools\VB\CRYSREPT folder on the installation Microsoft Visual Basic 6.0 Professional Edition CD-ROM. This will ask you to install Crystal Reports into the Common folder under the Microsoft Visual Studio folder on your hard drive. The Report Designer option should now appear in the Add-Ins menu. Clicking on this option will lead you into the Crystal Reports software module.

Report Designer Add-In (2 elements)

1. Crystal Reports - powerful Windows report writer used to design a nearly endless variety of custom reports
2. Crystal Custom Control - is an ActiveX control that makes it easy to build the connection between VB application and the created reports

Using Crystal Custom Control

• Build connection between application and Crystal Reports by setting control’s properties
• Name of report
• Destination for report (window, file, printer)
• Number of copies
• File information (if report is going to a file)
• Print window size and location (report is going to a window)
• Selection formula information (want to limit records)

Crystal Custom Control Properties

• WindowTitle - caption in title bar of report window
• ReportFileName - specifies path and filename of report to be executed
    (ext .rpt)
• DiscardSavedData - if set to True, Crystal Reports ignores any data already in the report file

Crystal Custom Control Code

• Can set property values of control either in Properties window or in code statements

CrystalReport1.WindowTitle = "TrainTrack Inventory by Railroad"

CrystalReport1.ReportFileName = "a:\Inventory.prt"

CrystalReport1.DiscardSavedData = True

• Setting Action property at run-time triggers printing of report

 CrystalReport1.Action = 1 

Project 6
Database Management and
Reporting Applications

END

Visual Data Manager, Crystal Reports Requirements

Return to IFS 110 class Notes Menu!