Data Environment

*  What is Data Environment?

*  How to create and work with connection and command objects in Data environments?

*  How to create master-detail relationship between two command objects?

*  DataGrid control and its properties, events and methods.

*  How to use DataEnvironment, DataGrid and DataCombo in real world application - Sales data entry screen?

In this chapter, we will understand how to use Data Environment, which was introduced in Visual Basic 6.0, how to use DataCombo, which is a data-bound ActiveX control, and how to use DataGrid, which is also a data-bound ActiveX control.

Let us understand each of them first. Then we will develop a data-entry screen using all these three.

Data Environment

Data Environment object is created and managed using Data Environment designer. A Data Environment object can accomplish any of the following tasks.

¨         Can contain connection and command objects

¨         Can establish relationship between command object to represent hierarchies

¨         Can group data and get aggregates

¨         Allows fields to be dragged and dropped on to a Data Report, for report, and a Form for data entry screen

¨         Can work as source for Databound control such as DataCombo controls and Hierarchical Flexgrid control.

In order to work with Data Environment object, you have to add Data Environment Designer to your project. Data Environment Designer allows programmer to accomplish any of the tasks mentioned above. Data Environment Designer is invoked when you double click on Data Environment object in Project Explorer.

To add Data Environment object to the project:

1.      Select Project-> Add Data Environment. If Add Data Environment option is not found then select More ActiveX Designers and then select Data Environment.

Note: Visual Basic adds Data Environment Designer to your project and also creates a connection object.

Working with Connection Object

Whenever you create a Data Environment object a Connection object is added to Data Environment object. But that Connection object is not connected to any database. So first we have to establish a connection with the Connection object. Now, let us see how to connect to database through Connection object.

To connect to Biblio.mdb using connection object:

1.      Select Connection object (Connection1) and click on right button.

2.      From popup menu select Properties option

Data Environment designer displays Data Link properties dialog.

3.      Select the required OLEDB provider (Microsoft Jet 3.51 OLE DB Provider)

4.      Select Connection tab and select the name of the database ( BIBLIO.MDB)

5.      Click on Ok

To rename connection object:

1.      Invoke popup menu by click on Connection object with right button.

2.      Select Rename option.

3.      Enter new name and press enter key to confirm it.

Creating Command Object

After connection is established using Connection object, now we have to create a command object to access the data of the database. A connection object can contain any number of command objects. In fact, a command object can contain another command object, which is called as child command object.

To create command object to get data from Authors table:

1.      Invoke popup menu of Connection object and select Add Command option

Data Environment Designer creates a command object with name Command1.

2.      Invoke Popup menu of command object by clicking on right button and select Properties option.

Properties dialog is displayed

3.      Change the Name of the command object to Authors

4.      Select Table as Database Object

5.      Invoke the list of tables by clicking on down arrow of Object Name drop down and select Authors as the name of the table.

6.      For the time being, ignore remaining tabs and click on OK.

A command object is created with the default name (command1). If you expand the command object by clicking on + sign, you get the columns of AUTHORS table.

To create master-detail relationship:

One of the interesting features of Data Environment object is, it allows you to create hierarchy of objects.

To understand how to create hierarchy of command objects, let us create two command objects. One for Publishers table and another for Titles table.

Publishers command object becomes parent command and Titles command object becomes child command object. These hierarchies can be used straightaway in Hierarchical FlexGrid control and Data Report object.

To create command object for Publishers:

1.      Select connection object, Biblio, and click on right button to invoke popup menu and select  Add Command option of popup menu.

2.      After a command object is created invoke its properties by selecting Properties option from Popup menu.

3.      Change Name of the command object to Publishers

4.      Choose Table as the Database Object and select Publishers as the name of the object.

5.      Click on Ok.

To create child command object for Titles:

1.      Select Publishers command object and invoke popup menu.

2.      Select Add Child Command option.

3.      A new command object is created and placed along with fields of Publishers command object.

4.      Select Child command object and invoke its properties

5.      Change name to Titles.

6.      Choose Table as Database Object and Titles as the name of the database object.

7.      Click on Relation tab  to set relationship between Publishers and Titles command object.

8.      Make sure Parent Fields and Child Fields are set to PubID and click on Add button to establish relationship between Publishers and Titles based on PubId field.

9.      Click on Ok.

After child object is created, Data Environment Designer has three command objects – Authors, Publishers, and Titles. Where Authors and Publishers are at the same level (Parent objects), Titles is a child command object of Publishers command object. When you expand all command objects, Data Environment Designer

Data Combo and Data List controls

DataCombo and DataList controls are used to present a list of values to users from which user can select one. The list of values to be displayed to user will come from one recordset and the selected value goes into another recordset.

Note: Whenever I refer to DataCombo, it also refers to DataList, unless the difference is explicitly specified.

As DataCombo is an ActiveX control, it is to be loaded into its project explicitly. And DataCombo is based on OLEDB interface. So it can be used with either Data Environment or ActiveX data control, because they are also based on OLEDB (use ADO).

To load Data List controls:

1.      Select Project->Components

2.      Visual Basic displays a list of available ActiveX controls.

3.      Turn on check box on the left of Microsoft DataList Controls 6.0 (OLEDB)

4.      Click on  OK.

Two ActiveX controls are loaded into project – DataList and DataCombo.

Note: You also have Microsoft Data Bound List Controls 6.0 ActiveX control. It contains DBList and DBCombo, which work with Data Control and not with OLEDB. If you are developing a project with only DAOs or RDOs, then you can use these controls.

The following are the important properties that are specific to Data List controls (Data Combo and Data List).

Property	Meaning
BoundColumn	Contains the name of the source field in the Recordset object that is used to supply a data value to another Recordset to which control is bound.
ListField	Returns or sets the name of the field in the Recordset object, specified by the RowSource property, used to fill the DataCombo or DataList control's list portion.
RowSource	Sets a value that specifies the source from which the control’s list is filled. The source is either an ActiveX Data control or Data Environment.
RowMember	Returns or sets the data member to be used to display list text. Used when RowSource is a Data Environment.
BoundText	Returns or sets the value of the field specified by the BoundColumn property.
DataSource	Contains an object to which control is bound. It is generally ADODC or Data Environment.
DataMember	Specifies the data member of Data Environment to which control is bound.
DataField	Specifies the name of the field to which control is bound.
DataChanged	Determines whether data in the control has been changed by any process.
Style	Determines the behavior and appearance of the control. Applicable only to DataCombo.
MatchedWithList	Returns True if the current content of the BoundText property matches one of the records in the list portion of the control.
SeletedItem	Returns a value containing a bookmark for the selected record in a DataCombo or DataList control.
VisibleItems	Returns an array of bookmarks, one for each visible item in the DataCombo or DataList control's list.
VisibleCount	Returns a value indicating the number of visible items in the list portion of the DataCombo or DataList control.
MatchEntry	Returns or sets a value indicating how the DataCombo or DataList control performs searches based on user input.

Table 20.1: Properties of DataList control.

Note: See Sample Data Entry screen later in this chapter to understand how to use various properties of the control.

DataGrid Control

Allows you to display and manipulate a set of records and columns taken from the specified recordset. You can specify whether you want to allow user to modify displayed data, add new records, and delete existing records.

The following are a few features supported by DataGrid control.

It supports the following features:

¨         Each cell contains a value. The value of the cell can be edited interactively by user or programmatically.

¨         Provides Columns collection using which you can access columns of the grid. When you select a column ColIndex property is set to enable you to access the columns selected by the user.

¨         Data in the current row can be accessed through Bookmark property, which provides access to underlying recordset object’s record.

¨         Each column can have its own display attributes

¨         The DataGrid control functions similarly to the DBGrid (which is based on DAO & RDO) control except that it doesn't support an unbound mode.

The following are the important methods, properties and events of DataGrid control.

Methods of DataGrid control

The following are the methods that are specific to DataGrid.

Method	Meaning
CaptureImage	Returns a captured image of the grid's display in its current state.
ClearFields	Clears all fields and restores grid to two columns and two rows.
ClearselCols	Deselects all the columns that are currently selected.
HoldFields	Sets the current column/field layout as the customized layout.
ColContaining	Returns the ColIndex value of the DataGrid control column containing the specified coordinate (X) value.
GetBookmark	Takes a relative row and returns the bookmark of the relative row. If GetBookmark 1 is given then the bookmark of next row of the current row is given.
Rebind	Causes the DataGrid control  to reset the columns, headings and other properties based on the current Data control properties.
RowBookmark	Returns a value containing a bookmark for a visible row in the DataGrid control.
RowContaining	Returns a value containing a bookmark for a visible row in the DataGrid control.
RowTop	Returns a value containing the Y coordinate of the top of a specified row of a DataGrid control
Scroll	Scrolls the DataGrid control horizontally and vertically in a single operation. You have to specify the number of rows and number of columns to scroll.

Table 20.2: Methods of DataGrid control.

To put an image of the DataGrid in an image box:

Image1.picture =  DataGrid1.captureImage

To get the row and columns of the current mouse pointer:

X and Y are coordinates of Mouse pointer

RowValue = DataGrid1.RowContaining(Y)

colValue = DataGrid1.ColContaining(X)

Properties of DataGrid control

The following are important properties of DataGrid.

Property	Meaning
AllowArrows	Sets or returns a value that determines whether the control uses the arrow keys for grid navigation.
AproxCount	Returns the approximate number of rows in the grid.
CurrentCellModified	Sets or returns modification status of the current cell. If it is true, it means current cell, identified by BookMark and Col properties, is modified.
CurrentCellVisible	Returns true if current cell is visible.
EditActive	Determines the current editing status of the current cell in the grid. Setting it to true will initiate editing of current cell. Setting it to false ends editing. Changing its value will automatically fire relevant events.
TabAction	Sets or returns a value that defines the behavior of the tab key. Valid values are: 0 - tab moves out of grid, 1- tab moves between cells in the same row, but once row changes then control moves out of grid, 2 – tab moves amount cells.
WrapCellPointer	Determines whether moving right on the last column will move control to first column of the next row (True) or not (False).
AllowAddnew, AllowDelete, AllowUpdate	Determines whether the corresponding action will be allowed.
Columns	Returns a collection of column objects.
DataChanged	Returns true, if data in the grid is changed.
Firstrow	Contains the bookmark of the first visible row in the grid.
Leftcol	Contains the number of the column that is to be displayed as the left-most column of the grid.
SelBookMarks	Returns a collection of bookmarks for all the selected rows.
VisibleCols	Returns the number of visible columns in the grid.
VisibleRows	Returns the number of visible rows.
RowHeight	Sets the height of all rows in datagrid.

Table  20.3: Properties of DataGrid control.

Events of DataGrid control

The following are events of DataGrid control.

Event	When is it fired?
AfterColEdit	After the editing of a cell is over. This event occurs after BeforeColUpdate and AfterColUpdate events.
BeforeColEdit	Just before the user enters a character to enter into edit mode. Set Cancel property to True, to prevent user from editing the cell.
Coledit	When user has entered into edit mode. Occurs immediately after BeforeColEdit event.
Error	When a data access error has taken place.
OnAddNew	When an action invokes an AddNew operation. The AddNew operation is invoked by either entering a character in a new record or by programmatically changing the data in new record.
SelChange	When user selects a different range of rows or columns.
AfterColUpdate	After data is moved from a cell in the DataGrid control to the control's copy buffer.
BeforeColUpdate	After editing is completed in a cell, but before data is moved from the cell to the DataGrid control's copy buffer.
AfterDelete, AfterUpdate, AfterInsert	After the corresponding operation has taken place.
BeforeDelete, BeforeUpdate, BeforeInsert	Before the corresponding operation takes place.

Table 20.4: Events of DataGrid control.

The following are a few examples using events, methods and properties of datagrid control.

To take confirmation before a record is deleted from grid:

Private Sub DataGrid1_BeforeDelete (Cancel As Integer)

   Dim mResult As Integer

   mResult = MsgBox("Are you sure that you want to delete " & DataGrid1.Selbookmarks.count &  " record?", _

    vbYesNo + vbQuestion, "Delete Confirmation")

   ‘ cancel deletion, if user click on NO button

   If mResult = vbNo Then Cancel = True

End Sub

Listing 20.1: Code to take confirmation before record is deleted.

To prevent from entering a date that is less than today's date:

Private Sub DataGrid1_BeforeColUpdate (ColIndex As Long, OldValue As  

                Variant, Cancel As Integer)

   If ColIndex = 1 Then

      If DataGrid1.Columns(1).Value < Now Then

         Cancel = True

         MsgBox "You must enter a date that is later than today."

      End If

   End If

End Sub

Listing 20.2: Code to check whether entered date is less than current date.

Creating a Data Entry screen

Let us create a sample data entry screen to take the details of a sales transaction. We will use a Data Environment to access the data. DataCombo is used to provide list of products  to user so that user can select one from the list of products. The data entry screen also provides the complete details of all the products upon user’s request. For this we use a DataGrid.

The following are the various major steps involved in creating this application.

¨         Creating Data Environment

¨         Designing Form

¨         Writing Code

Creating Data Environment

The following are the steps required to create new application , add Data Environment and create required objects in  Data Environment .

1.      Start a new project using File -> New Project and select  Standard Exe as the project type.

2.      Add a Data Environment object by selecting Project -> Add Data Environment.

3.      Change the Name of Data Environment to DenvProducts.

4.      Click right mouse button on Connection object to invoke popup menu and select properties option to invoke Data Link Properties.

5.      Select  Microsoft Jet 3.51 OLE DB Provider          as OLE DB provider.

6.      Select Connection tab and choose Products.mdb as the database.

7.      Click on OK to close Data Link Properties.

8.      Invoke popup menu of Connection object and select Add Command to add a command object.

9.      A new Command object will be created with the name Command1.

10.  Select Command object and invoke its properties.

11.  Change the following properties of the command1 object.

Name                           Products

Database Object          Table

Object Name               Products

12.  Invoke popup menu of Connection object again and select Add Command option.

13.  A new command object will be created again.

14.  Change the following properties of the newly created command object.

 Name                           Sales

Database Object          Table

Object Name               Sales

After both the command objects are created, the Data Environment designer should look like this.

Designing form

After we have created required objects in Data Environment, let us now concentrate on placing the required controls on the form and changing their properties.  In this application we have to have two forms. One is automatically added to the project as it is a Standard Exe project. So we have to create only one form. We will concentrate on second form a bit later. First let us place required controls on the first form.

Before we proceed let us load two ActiveX controls that will be used in this project.

To Load DataList control into project:

1.      Select Project -> Components.

2.      Visual Basic displays Components dialog with list of available ActiveX controls.

3.      Check Microsoft DataList Controls 6.0 (OLEDB)

4.      Click on Ok

Note: DataList consists of DataList control and DataCombo control.

To Load DataGrid control into project:

1.      Select Project -> Components

Visual Basic displays Components dialog with list of available ActiveX controls.

2.      Check Micorsoft DataGrid Controls 6.0 (OLEDB)

3.      Click on Ok

Designing first form (Data Entry)

Designing data entry screen involves the following steps.

1.      Open the Form and the Data Environment designer and place them side by side.

2.      Drag Fields (QTYSOLD, RPU and DISCOUNT) from Data Environment designer to Form.

3.      When you drag and drop a field from Data Environment Designer to Form, a textbox for field and a label control for the name of the field will be created on the form at the position where you dropped the field.

If you look at the properties of textbox that is created when you drag and drop a field of Data Environment on the form, you find the following properties already set by Data Environment. These properties make the textbox bound to Data Environment.

The properties for qtysold are:

DataSource     DenvProducts

Datamember    Sales

DataField        QtySold

Using DataCombo control

1.      Place DataCombo control on the from

2.      And change the following properties.

Property	Value
Name	DcmbProdno
BoundColumn	Prodno
ListField	Proddesc
DataField	Prodno
DataMember	Sales
DataSource	Denvproducts
RowSource	Denvproducts
RowMember	Products
Style	2-dbcDropdownlist

DataCombo box is used to get the list of products from Products command of Data Environment. It displays product description (listField is proddesc) but when user selects an item it takes the corresponding product number (boundcolumn is prodno).

The data combo box is bound to prodno field  (datafield is prodno) of Sales command object (datamember is Sales) of the Data Environment (datasource is Denvproducts). As the result whatever product number is selected by the user that is copied into prodno field of Sales table.

That is all about DataCombo control.

1.      Add a line at the bottom of the form using Line tool.

2.      Add four command buttons and arrange them

3.      Change the properties of the form and command buttons as follows.

Object	Property	Value
Form1	Name	FrmSales
	Caption	Sales Entry Form
	BorderStyle	3-Fixed Dialog
Command1	Name	CmdSave
	Caption	&Save
	Default	True
Command2	Name	CmdCancel
	Caption	&Cancel
	Cancel	True
Command3	Name	CmdList
	Caption	&Products List
Command4	Name	CmdQuit
	Caption	&Quit

That’s all we have so far as the data entry form is concerned. We will write code later. It is time to design our second form in which we display the details of products using datagrid.

Designing second form (List form)

1.      First, make sure you have second form added to your project. If not, add a form using Project->Add Form.

2.      Place DataGrid control and a command  button on the from.

3.      Change the properties as follows.

Object	Property	Value
Form2	Name	FrmProdList
	Caption	Products List
	BorderStyle	3-Fixed Dialog
Command1	Name	CmdClose
	Caption	&Close
DataGrid1	DataSource	DenvProducts
	DataMember	Products
	AllowUpdate	false

After properties of DataGrid are changed, change the size of data grid so that all columns of the grid can be displayed to user.

To resize grid to the size of columns:

1.      Invoke Popup menu for DataGrid and select Retrieve Fields option.

2.      DataGrid takes columns information from data source (Data Environment) and resizes the columns of the grid to the required size.

3.      Now resize the grid in such a way that user can view all the columns.
DataGrid is resized by leaving some area on the right side for scrollbar.

Writing Code

Before we write code let us understand what are the important events for us, and what we do when those events occur.

Event	Required Action
Selecting Save button	Saves the current record to SALES table and creates a new blank record. It also decreases QOH in Products table by qtysold.
Selecting Cancel button	Erases the data already entered  and keeps the form ready for new record.
Selecting ProductList button	Displays frmProdList form, which displays the details of the products.
Selecting Quit button	Terminates the application.(RPU)
Selecting a product form DataCombo control	Immediately displays the rate per unit of the selected product.
Moving out of qtySold item	Checks whether the entered amount of qty is really existing in the Products table.
Clicking on a row in DataGrid	Places the selected product into dcmbProdno of the frmSales form so that when you come back you do not have to repeat the selection of the same product.
Selecting Close button of frmProdList	Closes the form and returns to qtySold field of frmSales.

Table 20.5: Events to which we need to respond in sample application.

Code for frmSales form

Here is the code to take required actions for various events in frmSales.

Private Sub cmdCancel_Click()

 With DenvProducts.rsSales

   .CancelUpdate

   .AddNew

   ClearFields

 End With

End Sub

Private Sub cmdList_Click()

  frmprodlist.Show vbModal

  ' goto txtqtysold text box

  txtqtysold.SetFocus

End Sub

Private Sub cmdSave_Click()

 With DenvProducts.rsSales

   ' save current record

   .Update

   UpdateProducts

   .AddNew

   ClearFields

 End With

End Sub

Private Sub Cmdquit_Click()

  Unload Me

 

End Sub

Private Sub dcmbProdno_Change()

  GetRate

End Sub

Private Sub dcmbProdno_Click(Area As Integer)

  GetRate

End Sub

Private Sub Form_Activate()

 With DenvProducts.rsSales

   If .EditMode = adEditNone Then

    .AddNew

   End If

 End With

End Sub

Public Sub ClearFields()

 

  txtqtysold.Text = ""

  txtrpu.Text = ""

  txtdiscount.Text = ""

  dcmbProdno.SetFocus

 

End Sub

Private Sub txtqtysold_Validate(Cancel As Boolean)

 ' check whether quantity is sufficient

 If txtqtysold.Text = "" Then

      Exit Sub

 End If

 If Not check_qty Then

    Cancel = True

 End If

End Sub

Public Function check_qty() As Boolean

' get quantity on hand for the selected product

With DenvProducts.rsProducts

   .Bookmark = dcmbProdno.SelectedItem

   qoh = .Fields("qoh").Value

   If qoh < CInt(txtqtysold.Text) Then

      MsgBox "Insufficient Quantity on Hand"

      check_qty = False

   Else

      check_qty = True

   End If

  

 End With

End Function

Public Sub GetRate()

 If Not dcmbProdno.MatchedWithList Then

    Exit Sub

 End If

' get rate and put that into txtRpu

 ' move to the required record

 With DenvProducts.rsProducts

    .Bookmark = dcmbProdno.SelectedItem

     txtrpu.Text = .Fields("rpu").Value

 End With

End Sub

Public Sub UpdateProducts()

' Decrease QOH of PRODUCTS table by QTYSOLD

 With DenvProducts.rsProducts

     .Bookmark = dcmbProdno.SelectedItem

     .Fields("qoh") = .Fields("qoh") - CInt(txtqtysold.Text)

     .Update

 End With

End Sub

Listing  20.3: Code for frmSalesEntry form

Code for frmProdList form

Here is the code for frmProdList form. The code takes care of placing selected product number into dcmbprodno of frmSales. Close button closes the form and control goes back to frmSales.

Private Sub cmdClose_Click()

 Unload Me

End Sub

Private Sub DataGrid1_Click()

 ' pass the selected product number to data entry screen

 frmSales.dcmbProdno.BoundText = DataGrid1.Columns(0).Value

End Sub

Listing 20.4: Code in frmProdList form.