Data Environment

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.

Data Environment Designer

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.

Properties of Command object.

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.

Data Environment designer after a command object is created.


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 Environment Designer after three command objects are added.

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.
Data Environment designer after two command objects are created.
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. where 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.