DataGrid Control

Displays and enables data manipulation of a series of rows and columns representing records and fields from a Recordset object.

Syntax

DataGrid

Remarks

The data-aware DataGrid control appears similar to the Grid control; however, you can set the DataGrid control's DataSource property to a Data control so that the control is automatically filled and its column headers set automatically from a Data control's Recordset object. The DataGrid control is really a fixed collection of columns, each with an indeterminate number of rows.

Each cell of a DataGrid control can hold text values, but not linked or embedded objects. You can specify the current cell in code, or the user can change it at run time using the mouse or the arrow keys. Cells can be edited interactively, by typing into the cell, or programmatically. Cells can be selected individually or by row.

If a cell's text is too long to be displayed in the cell, the text wraps to the next line within the same cell. To display the wrapped text, you must increase the cell's Column object's Width property and/or the DataGrid control's RowHeight property. At design time, you can change the column width interactively by resizing the column or by changing the column's width in the Column object's property page.

Use the DataGrid control's Columns collection's Count property and the Recordset object's RecordCount property to determine the number of columns and rows in the control. A DataGrid control can have as many rows as the system resources can support and up to 32767 columns.

When you select a cell, the ColIndex property is set, thus selecting one of the Column objects in the DataGrid object's Columns collection. The Text and Value properties of the Column object reference the contents of the current cell. The data in the current row can be accessed using the Bookmark property, which provides access to the underlying Recordset object's record. Each column of the DataGrid control has its own font, border, word wrap, and other attributes that can be set without regard to other columns. At design time, you can set the column width and row height and establish columns that are not visible to the user. You can also prevent users from changing the formatting at run time.

Note If you set any of the DataGrid column properties at design time, you will need to set all of them in order to maintain the current settings.

Note If you use the Move method to position the DataGrid control, you may need to use the Refresh method to force it to repaint.

The DataGrid control functions similarly to the DBGrid control except that it doesn't support an unbound mode.

Note This control is Unicode-enabled. When used on a Unicode-enabled system such as Microsoft Windows NT, the control passes Unicode data with no conversion. On other systems, however, data is converted from ANSI to Unicode and back. For more information, see "ANSI, DBCS, and Unicode: Definitions" in the Programmer's Guide.


Topic Not Found

No matching topic was found. Some possible reasons and remedies are as follows:

  • The file (.chm) that contains the destination topic is not in the expected location.

    Remedy: Rerun the MSDN Library Setup.

  • The file (.chm) that contains the current topic or destination topic is out-of-date and the link is no longer valid.

    Remedy: Install the latest version of the MSDN Library.

  • The MSDN Library CD is not in the CD-ROM drive.

    Remedy: Insert the MSDN Library CD into the CD-ROM drive.


AfterColUpdate Event

Occurs after data is moved from a cell in the DataGrid control to the control's copy buffer.

Syntax

Private Sub object_AfterColUpdate ([index As Integer,] colindex As Integer)

The AfterColUpdate event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column in the control.

Remarks

When a user completes editing within a DataGrid control cell, as when tabbing to another column in the same row, pressing ENTER, or when the control loses focus, the BeforeColUpdate event is executed, and unless canceled, data from the cell is moved to the control's copy buffer. Once moved, the AfterColUpdate event is executed.

The AfterColUpdate event occurs after the BeforeColUpdate event, and only if the cancel argument in the BeforeColUpdate event is not set to True.

Once the AfterColUpdate event procedure begins, the cell data has already been moved to the control's copy buffer and can't be canceled, but other updates can occur before the data is committed to the Recordset.


AfterColUpdate Event Example

This example does a lookup when one column is updated and places the result in another column.

Private Sub DataGrid1_AfterColUpdate (ColIndex As Integer)
   If ColIndex = 1 Then
      Data1.Recordset.FindFirst "PubId = " _
       & DataGrid1.Columns(1).Value
      If Not Data1.Recordset.NoMatch Then
         DataGrid1.Columns(2).Value = _
          Data1.Recordset.Fields("Publisher")
      Else
         DataGrid1.Columns(2).Value = "No Match"
      End If
   End If
End Sub


AfterDelete Event

Occurs after the user deletes a selected record in the DataGrid control.

Syntax

Private Sub object_AfterDelete ([index As Integer,] colindex As Integer)

The AfterDelete event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column.

Remarks

When the user selects a record selector in the DataGrid control and presses DEL or CTRL+X, the selected row is deleted. Before the record is deleted, the BeforeDelete event is triggered. Once the row is deleted, the AfterDelete event is triggered. The row selected for deletion is available in the collection provided by the SelBookmarks property.


AfterDelete Event Example

This example displays a message confirming that a record has successfully been deleted.

Private Sub DataGrid1_AfterDelete ()
   MsgBox "Record has successfully been deleted!"
End Sub


AfterInsert Event

Occurs after the user inserts a new record into the DataGrid control.

Syntax

Private Sub object_AfterInsert (index As Integer)

The AfterInsert event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.

Remarks

When the user selects the new record (at the bottom of the control) and enters a character in one of the cells, the BeforeInsert event is triggered, followed by the BeforeUpdate, AfterUpdate and AfterInsert events.

When the AfterInsert event is triggered, the record has already been added to the database. The Bookmark property can be used to access the new record.

The AfterInsert event can't be canceled.

The AfterInsert event procedure can be used to update other tables or to perform post-update cleanup of other controls.


AfterInsert Event Example

This example creates an entry in a related table if the user enters a value in a column in the grid.

Private SubDataGrid1_AfterInsert ()
   If DataGrid1.Columns(1).Value <> "" Then
      Data2.Recordset.AddNew
      Data2.Recordset.Fields("PubId") = DataGrid1.Columns(1).Value
      Data2.Recordset.Update
   End If
End Sub


AfterUpdate Event

Occurs after changed data has been written to the database from a DataGrid control.

Syntax

Sub object_AfterUpdate (index As Integer)

The AfterUpdate event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.

Remarks

When the user moves to another row, or the Recordset object's Update method is executed, data is moved from the control's copy buffer to the Data control's copy buffer and written to the database. Once the write is complete, the AfterUpdate event is triggered.

The updated record is available by using the Bookmark property of the DataGrid control.

The AfterUpdate event occurs after the BeforeUpdate event, but before the LostFocus event for the control (or GotFocus for the next control in the tab order). This event occurs in bound and unbound mode and can't be canceled.

Unlike the Change event, changing data in a control or record using code doesn't trigger this event.


AfterUpdate Event Example

This example updates a label when any change has been made in the grid.

Private Sub DataGrid1_AfterUpdate ()
   Label1.Caption = "Last modified: " & Format$(Now, "Long Date")
End Sub


BeforeColUpdate Event

Occurs after editing is completed in a cell, but before data is moved from the cell to the DataGrid control's copy buffer.

Syntax

Private Sub object_BeforeColUpdate ([ index As Integer,] colindex As Integer, oldvalue As Variant, cancel As Integer)

The BeforeColUpdate event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column.
oldvalue | A value that contains the value contained in the cell prior to the change.
cancel | A Boolean expression expression that specifies whether the change occurs, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | Cancels the change, restores cell to oldvalue, and restores focus to the control.
False | (Default) Continues with change and permits change of focus.

Remarks

The data specified by the oldvalue argument moves from the cell to the control's copy buffer when a user completes editing within a cell, as when tabbing to another column in the same row, pressing ENTER, or changing focus away from the cell. Before the data has been moved from the cell into the control's copy buffer, the BeforeColUpdate event is triggered. This event gives your application an opportunity to check the individual grid cells before they are committed to the control's copy buffer.

If your event procedures set the cancel argument to True, the previous value is restored in the cell and focus remains on the control and the AfterColUpdate event is not triggered.

To restore oldvalue in the cell and permit the user to move focus off of the cell, set cancel to False and set the cell to oldvalue as follows:

Cancel = False
DataGrid1.Columns(ColIndex).Value = OldValue

The AfterColUpdate event occurs after the BeforeColUpdate event.

By setting the cancel argument to True, the user can not move the focus from the control until the application determines that the data can be safely moved back to the control's copy buffer.


BeforeColUpdate Event Example

This example checks to make sure that the value the user has typed in is within a certain range; otherwise it disables the update.

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


BeforeDelete Event

Occurs before a selected record is deleted in a DataGrid control.

Syntax

Private Sub object_BeforeDelete ([index As Integer,] cancel As Integer)

The BeforeDelete event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
cancel | A Boolean expression that determines whether a record is deleted, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | Leaves focus on control and doesn't delete the record.
False | (Default) Continues with delete operation and enables change of focus.

Remarks

When the user selects a record selector in the control and presses DEL or CTL+X, the BeforeDelete event is triggered before the selected row is deleted.

Once the row is deleted, the AfterDelete event is triggered. The row selected for deletion is available in the collection provided by the SelBookmarks property.

If your event procedure sets the canceI argument to True, the row isn't deleted.

If more than one row is selected, the error message Multiple rows cannot be deleted is displayed.


BeforeDelete Event Example

This example displays a message that asks the user to confirm a deletion in a grid.

Private Sub DataGrid1_BeforeDelete (Cancel As Integer)
   Dim mResult As Integer
   mResult = MsgBox("Are you sure that you want to delete " & DataGrid1.SeletedRows &  " record?", _
    vbYesNo And vbQuestion, "Delete Confirmation")
   If mResult = vbNo Then Cancel = True
End Sub


BeforeInsert Event

Occurs before new records are inserted into a DataGrid control.

Syntax

Private Sub object_BeforeInsert ([ index As Integer,] cancel As Integer)

The BeforeInsert event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
cancel | A Boolean expression that determines if a record is added, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | Leaves focus on control and doesn't add a new record
False | (Default) Continues with copy and enables change of focus

Remarks

When the user selects the new record (at the bottom of the DataGrid control) and enters a character in one of the cells, the BeforeInsert event is triggered, followed by the BeforeUpdate, AfterUpdate and AfterInsert events.

If your event procedure sets the canceI argument to True, the row isn't inserted and the cell is cleared.

When the BeforeInsert event is triggered, the record has not been added to the database. The new record exists in the DataGrid control's copy buffer until this event procedure ends.

After the AfterInsert event is finished, the new record row in the DataGrid control is reinitialized and the edited record becomes the last row in the DataGrid control.


BeforeInsert Event Example

This example displays a message that asks the user to confirm the addition of a new record.

Private Sub DataGrid1_BeforeInsert (Cancel As Integer)
   Dim mResult As Integer
   mResult = MsgBox("Confirm: Add a new record?", _
    vbYesNo And vbQuestion, "Confirmation")
   If mResult = vbNo Then Cancel = True
End Sub


BeforeUpdate Event

Occurs before data is moved from a DataGrid control to the control's copy buffer.

Syntax

Private Sub object_BeforeUpdate ([index As Integer,] cancel As Integer)

The BeforeUpdate event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
cancel | A Boolean expression that determines if data is copied, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | Leaves focus on control and doesn't copy data.
False | (Default) Continues with copy operation and enables change of focus.

Remarks

When the user moves to another row or the Recordset object's Update method is executed, data is moved from the DataGrid control's copy buffer to the Data control's copy buffer and written to the database.

Just before the data is moved from the DataGrid control's copy buffer back into the Data control's copy buffer, the BeforeUpdate event is triggered. Unless the copy operation is canceled, the AfterUpdate event is triggered after the data has been moved back into the Data control's copy buffer and written to the database. The updated record is available by using the Bookmark property of the DataGrid control.

If you set the BeforeUpdate event cancel argument to True, focus remains on the control, neither the AfterUpdate or LostFocus event is triggered, and the record isn't saved to the database.

The BeforeUpdate event occurs before the AfterUpdate and LostFocus events for this control, or before the GotFocus event for the next control in the tab order.

This event occurs even if the control isn't bound.

Unlike the Change event, changing data in a control or record using code doesn't trigger this event.

You can use this event to validate data in a bound control record before permitting the user to commit the change to the Data control's copy buffer. By setting the cancel argument to True, the user can't move focus from the control until the application determines whether the data can be safely moved back to the Data control's copy buffer.


BeforeUpdate Event Example

This example displays a message that tells the user to enter a value in the first column before the grid can be updated.

Private Sub DataGrid1_BeforeUpdate (Cancel As Integer)
   If DataGrid1.Columns(1).Value = "" Then
      MsgBox "You must enter value in the first column!"
      Cancel = True
   End If
End Sub


ColResize Event

Occurs when a user resizes a column of a DataGrid control.

Syntax

Private Sub object_ColResize ([ index As Integer,] colindex As Integer, cancel As Integer)

The ColResize event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column.
cancel | A Boolean expression that determines whether a column is resized, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | Cancels the change, restores column to its original width.
False | (Default) Continues with width change.

Remarks

When the user resizes a column, the ColResize event is triggered. Your event procedure can accept the change, alter the degree of change, or cancel the change completely.

If you set the cancel argument to True, the column width is restored. To alter the degree of change, set the Width property of the Column object to the desired value.

Executing the Refresh method within the procedure causes the control to be repainted even if the cancel argument is True.


ColResize Event Example

This example resizes all the columns to the size of the first column if the user sizes the first column.

Private Sub DataGrid1_ColResize (ColIndex As Integer, Cancel As Integer)
   Dim nCol As Column
   If ColIndex = 1 Then
      For Each nCol In DataGrid1.Columns
         nCol.Width = DataGrid.Columns(1).Width
      Next
   End If
End Sub


HeadClick Event

Occurs when the user clicks on the header for a particular column of a DataGrid control.

Syntax

Private Sub object_HeadClick ([ index As Integer,] colindex As Integer)

The HeadClick event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column.

Remarks

One possible use for this event is to resort the Recordset object based on the selected column.


HeadClick Event Example

This example sorts the record source of the Data control based on which column the user clicked.

Private Sub DataGrid1_HeadClick (ColIndex As Integer)
   Data1.RecordSource = "Select * From Publishers Order By " & _
    DataGrid1.Columns(ColIndex).DataField
   Data1.Refresh
End Sub


RowResize Event

Occurs when a user resizes a row in a DataGrid control.

Syntax

Private Sub object_RowResize ([ index As Integer,] cancel As Integer)

The RowResize event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
cancel | A Boolean expression that determines if a change is canceled, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | Cancels the change and restores row to its original height.
False | (Default) Continues with change of row height.

Remarks

The user can resize the DataGrid control rows using the mouse. When the user changes the height, the RowResize event is triggered. Your event procedure can accept the change, alter the degree of change, or cancel the change completely.

The DataGrid control's RowHeight property determines the height for all rows in the control.

If you set the cancel argument to True, the row height is restored. To alter the degree of change, set the RowHeight property to the desired value.

Executing the Refresh method within the procedure causes the control to be repainted even if the cancel argument is True.


RowResize Event Example

This example ensures that there are at least five visible rows in the grid.

Private Sub DataGrid1_RowResize (Cancel As Integer)
   If DataGrid1.VisibleRows < 5 Then Cancel = True
End Sub


CellText Method

Returns a formatted text value from a DataGrid control cell. Doesn't support named arguments.

Syntax

object.CellText bookmark

The CellText method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
bookmark | Required. A string expression that represents a row in the DataGrid control.

Remarks

The CellText method returns a formatted string representation of the data in the current column for the row specified by the bookmark value. Using the CellText method is similar to accessing the Text property, except you can select a specific row from which to retrieve the value.

The value returned by the CellText method is derived from the Text property by applying the formatting as specified by the NumberFormat property of the Column object.

When using the CellText method, use the Columns collection to specify the specific column of the DataGrid control and set the bookmark argument to a specific row.

Using the CellText method to extract information from a cell doesn't affect the current selection.


CellText Method Example

This examples gets information from the top and bottom rows and displays it in a label.

Sub DataGrid1_Scroll (Cancel As Integer)
   Dim TopRow, BottomRow
   TopRow = DataGrid1.Columns(1).CellText(DataGrid1.FirstRow)
   BottomRow = DataGrid1.Columns(1).CellText(DataGrid1.RowBookmark _
    (DataGrid1.VisibleRows - 1))
   Label1.Caption = "Records " & TopRow & " to " & _
    BottomRow & " are currently displayed."
End Sub


CellValue Method

Returns a raw data value in a column for a specified row in a DataGrid control. Doesn't support named arguments.

Syntax

object.CellValue bookmark

The CellValue method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
bookmark | Required. A string expression that contains the unformatted data stored in a selected DataGrid control cell.

Remarks

When using the CellValue method, use the Columns collection to specify the specific column of the DataGrid control and set the bookmark argument to a specific row.

Using the CellValue method returns the same value as the Value property setting of the current Column object, except that you can specify a specific row in the DataGrid control to reference.

Using the CellValue method to extract information from a cell doesn't affect the current selection.


CellValue Method Example

This example retrieves all the values in a given column from the selected range of rows and loads them into an array for later use.

Sub Command1_Click ()
   Dim I
   ReDim CalcArray (0 to DataGrid1.SelBookmarks.Count - 1)
   For I = 0 to DataGrid1.SelBookmarks.Count -1
   ' Puts the value of the current row in the selected row
   ' array into corresponding CalcArray cell.
      CalcArray(I) = _
       DataGrid1.Columns(1).CellValue(DataGrid1.SelBookmarks(I))
   Next I
End Sub


ColContaining Method

Returns the ColIndex value of the DataGrid control column containing the specified coordinate (X) value. Doesn't support named arguments.

Syntax

object.ColContaining coordinate

The ColContaining method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
coordinate | Required. A single numeric expression that defines a horizontal coordinate (X value) based on the coordinate system of the container.

Remarks

The ColContaining method returns a number that corresponds to one of the column indexes of the control specified by object. This number ranges from 0 to 1 less than the setting of the Count property of the Columns collection (0 to Columns.Count - 1). This method is useful when working with mouse and drag events when you are trying to determine where the user clicked or dropped another control in terms of a column of the DataGrid control.

If coordinate is outside of the coordinate system of the container, a trappable error occurs.

Note The ColContaining method returns the ColIndex of the column indicated, not the visible column. If coordinate falls in the first visible column, but two columns have been scrolled off the left side of the control, the ColContaining method returns 2.


GetBookmark Method

Returns a value containing a bookmark for a row relative to the current row in a DataGrid control. Doesn't support named arguments.

Syntax

object.GetBookmark value

The GetBookmark method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | Required. A long numeric expression that addresses rows of the DataGrid control relative to the current row, as described in Settings.

Settings

The settings for value are:

Setting | Description
0 | Returns bookmark of the current row — the same as DataGrid1.Bookmark.
1 | Returns bookmark of the row following current row.
-1 | Returns bookmark of the row preceding current row.
n | Returns bookmark of the row relative to current row based on (DataGrid1.Row + n)

Remarks

The GetBookmark method may return values that are very different from the RowBookmark method because the current row may not be visible.


GetBookmark Method Example

This example checks the updated value of a particular column to make sure that the new value lies between the values of the previous and the next rows.

Sub DataGrid1_BeforeColUpdate (ColIndex As Integer, _
 OldValue as Variant, PrevVal, NextVal, CurVal, Cancel As Integer)
   If ColIndex = 1 Then
      PrevVal = DataGrid1.Columns(1).CellValue(_
       DataGrid1.GetBookmark(-1))
      NextVal = DataGrid1.Columns(1).CellValue(_
       DataGrid1.GetBookmark(1))
      CurVal = DataGrid1.Columns(1).Value
      If CurVal > PrevVal Or CurVal < NextVal  Then
         Cancel = True
         MsgBox "Value must be between" & PrevVal _
          & " and " & NextVal
      End If
   End If
End Sub


Add Method (Columns, SelBookmarks, Splits Collections)

Adds a new column to the Columns collection, a new bookmark to the SelBookmarks collection, or a new split to the Splits collection of the DataGrid control. Doesn't support named arguments.

Syntax

object.Add colindex

object.Add bookmark

The Add method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
colindex | Required. An integer that specifies where the new Column or Split object is inserted in the Columns collection or Splits collection, as described in Settings.
bookmark | The bookmark to be added to the collection.

Settings

The settings for colindex are:

Setting | Description
0 | Inserts new column as leftmost column.
Count | If the colindex argument is the same as the Count property setting, the new column is inserted as the rightmost column.
n | Inserts the new column to the left of the nth column in the Columns collection. The nth column and all subsequent columns are incremented accordingly.

Remarks

The Add method inserts a new Column object into the Columns collection based on the colindex argument. New columns are added with their Visible property set to False and all other properties set to their default values. Initially, new columns are unbound because the DataField property is set to a zero-length string (""). The Count property of the Columns collection is incremented to reflect the new column.

Important If you have previously deleted a column using the Remove method, after adding new columns, you may need to refresh the display with the Rebind and Refresh methods. This instructs the DataGrid control to rebuild its internal column layout matrix to correctly reflect the true status of the control.

Use the Add method to add bookmarks to the SelBookmarks collection. Once a bookmark is appended to the SelBookmarks collection, it appears selected in the DataGrid control.


Rebind Method

Re-creates the DataGrid control properties and columns. Doesn't support named arguments.

Syntax

object.Rebind

Remarks

The Rebind method causes the DataGrid control to perform the same operations that occur when you set the DataSource property. The DataGrid control resets the columns, headings and other properties based on the current Data control properties.

If you have not modified the grid columns at design time, then executing the ReBind method will reset the columns, headings, and other properties based on the current data source.

However, if you have altered the columns in any way at design time (even if you leave the DataField properties blank), then the grid will assume that you wish to maintain the modified grid layout and will not automatically reset the columns.

For an unbound grid (one with its DataMode property set to 1), this method is similar to the Refresh method except that the grid attempts to restore the current and topmost rows.

Note To force the grid to reset the column bindings even if the columns were modified at design time, invoke the ClearFields method immediately before ReBind. Conversely, to cancel the grid's automatic layout response and force the grid to use the current column/field layout, invoke the HoldFields method immediately before ReBind.


Rebind Method Example

This example checks a global variable to see if the user changed the table layout and reconfigures using the original table information.

Sub CheckForRebind_Click ()
   If UserChangedLayout Then
      DataGrid1.Rebind
   End If
End Sub


Remove Method (DBGrid)

Remove Method (DataGrid)

Removes the specified row from the SelBookmarks collection, or the specified Column object from the Columns collection of a DataGrid control.

Syntax

object.Remove index

The Remove method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | Required. An integer in the range of 0 to the Count property setting of the collection – 1.

Remarks

For the SelBookmarks collection, the Remove method removes the row specified by the index argument, then decrements the SelBookmarks.Count property by 1. If the row removed from the SelBookmarks collection is visible, it will be deselected in the DataGrid control.

For the Columns collection, the Remove method removes the column specified by the index argument, then decrements the Columns.Count property by 1.

If you specify a row that isn't in the SelBookmarks collection or a Column object that isn't in the Columns collection, a trappable error occurs.


RowBookmark Method

Returns a value containing a bookmark for a visible row in the DataGrid control. Doesn't support named arguments.

Syntax

object.RowBookmark value

The RowBookmark method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | Required. An integer in the range of 0 to the setting of the DataGrid control's VisibleRows property minus 1.

Remarks

RowBookmark(0) returns the same bookmark as the FirstRow property of the DataGrid control. The current row, as determined by the DataGrid control's Bookmark property, may not be returned by this method if the current row isn't visible.

Note The bookmarks returned by RowBookmark should not be saved because their values change as soon as rows visible in the DataGrid control change.


RowBookmark Method Example

This example selects all the rows that are currently visible on the grid.

Sub SelectAllVisible_Click ()
   Dim I
   For I = 0 To DataGrid1.VisibleRows - 1
      DataGrid1.SelBookmarks.Add DataGrid1.RowBookmark(I)
   Next I
End Sub


RowContaining, ColContaining Method Example

This example saves the value of the cell where the user began a drag method.

Dim SaveValue
Sub DataGrid1_MouseDown (Button As Integer, Shift As Integer, _
 X As Single, Y As Single)
   Dim RowValue, ColValue
   ' Get the value of the row and column that the mouse is over
   RowValue = DataGrid1.RowContaining(Y)
   ColValue = DataGrid1.ColContaining(X)
   ' If the values are both valid, save the text of the cell and
   ' begin dragging.
   If RowValue > 0 And RowValue < DataGrid1.VisibleRows And _
    ColValue > 0 And ColValue < DataGrid1.VisibleCols Then
      SaveValue = DataGrid1.Columns(ColValue). _
      CellValue(DataGrid1.RowBookmark(RowValue))
      DataGrid1.Drag 1
   End If
End Sub


RowContaining Method

Returns a value corresponding to the row number of the specified vertical (Y) coordinate of the DataGrid control. Doesn't support named arguments.

Syntax

object.RowContaining coordinate

The RowContaining method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
coordinate | Required. A single numeric expression that specifies a vertical coordinate (Y value) based on the coordinate system of the container.

Remarks

The RowContaining method returns a value that corresponds to one of the column indexes of the control specified by object. This value ranges from 0 to the setting of the VisibleRows property -1. This method is useful when working with mouse and drag events when you are trying to determine where the user clicked or dropped another control in terms of a row of the DataGrid control.

If coordinate is outside of the coordinate system of the container, a trappable error occurs.


RowTop Method

Returns a value containing the Y coordinate of the top of a specified row of a DataGrid control. Doesn't support named arguments.

Syntax

object.RowTop value

The RowTop method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | Required. An integer that specifies a row in the range of 0 to the setting of the VisibleRows property -1.

Remarks

The RowTop method returns a value that corresponds to the Y coordinate of the top of the row specified by value. The return value is based on the ScaleMode property of the container.

You can use the RowTop method with the RowHeight, Left, and Width properties of the Column object to determine the exact location and dimension of a chosen cell in the DataGrid control.


RowTop Method Example

This example begins a drag operation in the grid. Using the grid cell location and size properties, a Label control the size of the cell is used as the drag object.

Sub DataGrid1_MouseDown (Button As Integer, _
Shift As Integer, X As Single, Y As Single)
   ' Declare variables.
   Dim DY, DX, RowValue, ColValue, CellLeft, CellTop
 ColValue = DataGrid1.ColContaining(X)
 RowValue = DataGrid1.RowContaining(Y)
   ' Get the height of the cell.
 DY = DataGrid1.RowHeight
   ' Get the width of the cell.
 DX = DataGrid1.Columns(ColValue).Width
   CellLeft = DataGrid1.Columns(ColValue).Left
 CellTop = DataGrid1.RowTop(RowValue)
 Label1.Caption = DataGrid1.Columns(ColValue). _
      CellValue(DataGrid1.RowBookmark(RowValue))
 Label1.Move CellLeft, CellTop, DX, DY
 Label1.Drag ' Drag label outline.
End Sub


Scroll Method

Scrolls the DataGrid control horizontally and vertically in a single operation. Doesn't support named arguments.

Syntax

object.Scroll colvalue, rowvalue

The Scroll method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
colvalue | Required. A long numeric expression that specifies a column in the control.
rowvalue | Required. A long numeric expression that specifies a row in the control.

Remarks

Positive values scroll right and down. Negative values scroll left and up. Values that are out of range don't cause an error — the DataGrid control scrolls to the maximum degree possible. The same effect can be achieved by setting the FirstRow and LeftCol properties, but these must be set independently, causing two separate Paint events.


Scroll Method Example

This example creates two buttons that enable you to scroll diagonally, one to move down and to the right and the other to move up and to the left.

Sub ScrollDownRight_Click
   ' Scroll down and to the right.
   DataGrid1.Scroll DataGrid1.VisibleCols, DataGrid1.VisibleRows
End Sub

Sub ScrollUpLeft_Click
   ' Scroll up and to the left.
   DataGrid1.Scroll -DataGrid1.VisibleCols, -DataGrid1.VisibleRows
End Sub


Column Object

A Column object represents a column within a DataGrid control.

Remarks

You manipulate a column in a DataGrid control by using a Column object's methods and properties. With a Column object, you can modify attributes of the column header as well as the column itself.

Note A DataGrid object can contain only 32767 columns, as column indices are stored in integers.

To use a Column object, you can either use the Columns property of the DataGrid control directly or assign each column to a separate variable dimensioned as a Column object. The following demonstrates the latter:

Dim Col1, Col2 as Column
Set Col1 = DataGrid1.Columns(0)
Set Col2 = DataGrid1.Columns(1)
Col1.Caption = "Column 1"
Col2.Caption = "Column 2"

If often referring to the columns in a DataGrid control, you will increase performance by using the above method to assign values to columns rather than using the Columns property as in:

DataGrid1.Columns(0).Caption = "Column 1"


SelBookmarks Collection

A SelBookmarks collection contains a bookmark for each row selected in a DataGrid control.

Syntax

SelBookmarks

Remarks

Use the SelBookmarks property of the DataGrid control to return the SelBookmarks collection. Bookmarks are added to the SelBookmarks collection in the order they're selected. You can reposition the DataGrid control's current record pointer by setting the Bookmark property to one of the selected bookmarks in the SelBookmarks collection.

Use the Add method to add bookmarks to the SelBookmarks collection. Once a bookmark is appended to the SelBookmarks collection, it appears selected in the DataGrid control.

To remove a bookmark from the SelBookmarks collection, use the Remove method. Once a bookmark is removed from the SelBookmarks collection, it no longer appears selected in the DataGrid control.

The SelBookmarks collection supports the Add and Remove methods as well as the Count property. Using these methods and properties, you can manipulate the list of selected items in the DataGrid control. For example, you can programmatically select additional items by using the Add method, or determine the total number of selected items using the Count property.


AllowAddNew Property

Returns or sets a value indicating whether the user can add new records to the Recordset object underlying a DataGrid control.

Syntax

object.AllowAddNew [= value]

The AllowAddNew property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether a user can add new records, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | Users can add records to the Recordset object underlying the DataGrid control.
False | Users can't add records to the Recordset underlying the DataGrid control.

Remarks

If the AllowAddnew property is True, the last row displayed in the DataGrid control is left blank to permit users to enter new records. If the AllowAddNew property is False, no blank line is displayed.

The underlying Recordset may not enable insertions even if the AllowAddNew property is True. In this case, an error occurs when the user tries to add a record.


AllowAddNew, AllowDelete, AllowUpdate Properties Example

This example checks the value of a check box. If it is False, the user can't make changes to the grid.

Private Sub Form_Load ()
   If Check1.Value = 0 Then
      DataGrid1.AllowDelete = False
      DataGrid1.AllowAddNew = False
      DataGrid1.AllowUpdate = False
   End If
End Sub


AllowDelete Property

Returns or sets a value indicating whether the user can delete records from the Recordset object underlying a DataGrid control.

Syntax

object.AllowDelete [= value]

The AllowDelete property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether a user can delete records, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | Users can delete records from the Recordset object underlying the DataGrid control.
False | Users can't delete records from the Recordset underlying the DataGrid control.

Remarks

Use the AllowDelete property to prevent the user from deleting records from the Recordset through interaction with the DataGrid control.

The underlying Recordset may not enable deletions even if the AllowDelete property is True for the DataGrid control. In this case, an error occurs when the user tries to delete a record.

Note After deleting a record from a DataGrid control, you should use the Refresh method on it to force the DataGrid to update. Otherwise, although a record has been deleted from the underlying recordset, the record will still show in the DataGrid.


AllowRowSizing Property

Returns or sets a value indicating whether a user can resize the rows of the DataGrid control or Split object at run-time.

Syntax

object.AllowRowSizing [= value]

The AllowRowSizing property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether a user can resize rows, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | Rows can be sized by the user.
False | Rows can't be sized by the user.

Remarks

If the AllowRowSizing property is True, the mouse pointer turns into a double-headed (Size N S) arrow when positioned over the row divider between any record selectors, and the user can resize the rows by dragging. Any change in row size causes a RowResize event.

All rows of the DataGrid control are always the same height, which is determined by the RowHeight property.

Note Even if the AllowRowSizing property is False, the height of the rows can still be changed programmatically with the RowHeight property.


AllowRowSizing Property Example

This example checks the database to see if it has any memo fields; if not, row resizing is disabled.

Sub CheckForMemoField()
   Dim Fld As Field
   DataGrid1.AllowRowSizing = False
   For Each Fld in Data1.Recordset.Fields
      If Fld.Type = dbMemo Then
         DataGrid1.AllowRowSizing = True
         DataGrid1.RowHeight = DataGrid1.RowHeight * 2
         Exit For
      End If
   Next
End Sub


AllowSizing Property

Returns or sets a value indicating whether a user can resize columns or splits in the DataGrid control at run-time.

Syntax

object.AllowSizing [= value]

The AllowSizing property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether a column or split can be resized, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | (Default for Column) User can resize column or split.
False | (Default for Split) User can't resize column or split.

Remarks

If the AllowSizing property is True, the mouse pointer turns into a double-headed (Size W E) arrow when positioned over the divider of the specified column, and the user can resize the column by dragging. Any change in column size causes a ColResize event.

For the leftmost split with AllowSizing set to True, the mouse pointer turns into a pair of vertical lines with a downward arrow when positioned over that split's size box (at the lower left corner), and the user can create a new split by dragging. The creation of a new split causes a SplitChange event.

If AllowSizing is True for any other split, the mouse pointer turns into a pair of vertical lines with a double-headed arrow when positioned over that split's size box, and the user can resize the split by dragging. No event is fired in this case (except for the standard mouse events).


AllowSizing Property Example

This example prevents the user from resizing or editing the first three columns of the grid.

Private Sub Form_Load ()
   Dim I
   For I = 0 to 2
      DataGrid1.Columns(I).AllowSizing = False
      DataGrid1.Columns(I).Locked = True
   Next I
End Sub


AllowUpdate Property

Returns or sets a value indicating whether a user can modify any data in the DataGrid control.

Syntax

object.AllowUpdate [= value]

The AllowUpdate property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether the user can change data, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | The user can modify data in the DataGrid control
False | The user can't modify data in the DataGrid control

Remarks

When the AllowUpdate property is False, the user can still scroll through the DataGrid control and select data, but can't change any of the values; any attempt to change the data in the grid is ignored.

You can also use the Column object properties to make individual columns of the DataGrid control read-only, but the AllowUpdate property setting takes precedence over the column settings (without changing the column settings).

Note The Recordset object may not enable updates even if AllowUpdate is True for the DataGrid control; in this case a trappable error occurs when the user tries to change the record.


ColIndex Property

Returns a value indicating the position of the column in the Columns collection of the DataGrid control and the visible position (left-to-right) of the column in the DataGrid control. This property is read-only at run time and not available at design time.

Syntax

object.ColIndex

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

Note that the value of the position given is the same as if all columns were visible and doesn't change if you hide one or more columns.

This property returns the zero-based index of a column within the Columns collection. Note, however, that For… Each is not guaranteed to return items in any particular order.


ColumnHeaders Property

Returns or sets a value indicating whether the column headers are displayed in a DataGrid control.

Syntax

object.ColumnHeaders [= value]

The ColumnHeaders property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether column headers are displayed, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | DataGrid control's column headers are displayed
False | DataGrid control's column headers aren't displayed


Columns Property (DBGrid)

Columns Property (DataGrid)

Returns a collection of Column objects.

Syntax

object.Columns

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

The Columns property returns a collection of Column objects in a Variant.

You can manipulate most of a DataGrid control's attributes by changing the properties of Column objects. Choose a specific Column object with the Col property.


DefColWidth Property

Returns or sets a value indicating the default column width for all columns in the DataGrid control.

Syntax

object.DefColWidth [= value]

The DefColWidth property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | An integer based on the scale mode of the control.

Remarks

If you set the DefColWidth property to 0, the control automatically sizes all columns based on either the width of the column heading or the Size property setting of the underlying field, whichever is larger. For example, to set the default column width of all columns to the width of the first column:

DataGrid1.DefColWidth = DataGrid1.Columns(0).Width


DividerStyle Property

Returns or sets a value specifying the style of border drawn on the right edge of the specified column of a DataGrid control.

Syntax

object.DividerStyle [= value]

The DividerStyle property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | An integer that specifies the border style, as described in Settings.

Settings

The settings for value are:

Constant | Value | Description
dbgNoDividers | 0 | No divider
dbgBlackLine | 1 | Black line
dbgDarkGrayLine | 2 | (Default) Dark gray line
dbgRaised | 3 | Raised
dbgInset | 4 | Inset
dbgUserForeColor | 5 | Divider is drawn using the color set by the ForeColor property
dbgLightGrayLine | 6 | Light gray line

Remarks

The DividerStyle property doesn't affect whether the column can be resized by dragging it or not. When the border is either raised or inset, the colors used are set by Microsoft Windows.


DividerStyle Property Example

This example changes the column divider style when the user clicks a heading.

Private Sub DataGrid1_HeadClick (ColIndex As Long)
   If DataGrid1.Columns(ColIndex).DividerStyle <> 5 Then
      DataGrid1.Columns(ColIndex).DividerStyle = _
       DataGrid1.Columns(ColIndex).DividerStyle + 1
   Else
      DataGrid1.Columns(ColIndex).DividerStyle = 0
   End If
End Sub


FirstRow Property

Returns or sets a value containing the bookmark for the first visible row in the DataGrid control or Split object. Not available at design time.

Syntax

object.FirstRow [= value]

The FirstRow property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A string expression containing a bookmark corresponding to the first visible row in the DataGrid control.

Remarks

For a DataGrid control, setting the FirstRow property causes the grid to scroll so that the specified row becomes the topmost row. If a grid contains multiple splits, then the topmost row changes in each split, even if the splits have different ScrollGroup property settings.

For a Split object, setting the FirstRow property causes the specified row to become the topmost row for that split only.


HeadFont Property

Returns or sets a value indicating the font used in column headers in a DataGrid control.

Syntax

object.Type [= value]

The Type property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | An object expression that evaluates to a Font object. The default value is the column's current font set to bold.

Remarks

Changing the HeadFont property may resize the headers to accommodate the new font.


HeadLines Property

Returns or sets a value indicating the number of lines of text displayed in the column headers of a DataGrid control.

Syntax

object.HeadLines [= value]

The HeadLines property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A single from 0 to 10. The default value is 1, which causes the control to display the names of underlying fields for each column in the header. A setting of 0 removes the headings.

Remarks

The HeadLines property can be used to display more than one line of text in the column headers of the DataGrid control.


HeadLines Property Example

This example checks the value of a check box to determine whether or not to display headings in the grid.

Private Sub Check1_Click ()
   If Check1.Value = vbChecked Then
      DataGrid1.HeadLines = 2   ' If checked,two lines in
                              ' column headings.
   Else
      DataGrid1.HeadLines = 0   ' No headings.
   End If
End Sub


NumberFormat Property

Returns or sets a value indicating the format string for the Column object of a DataGrid control.

Syntax

object.NumberFormat [= value]

The NumberFormat property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A string expression that defines how the expression in the Value property is formatted. The default value is a zero-length string ("").

Remarks

The Text property of the Column object is derived by applying this format to the Value property of the Column object. If NumberFormat is set to an invalid string, data in the cells are displayed as #ERR# and the value set in the Value property remains unchanged. See the Format function for information about valid format strings.


NumberFormat Property Example

This example formats the second column in a DataGrid control as a long date:

Private Sub Command1_Click ()
   DataGrid1.Columns(1).NumberFormat = "long date"
End Sub


RecordSelectors Property

Returns or sets a value indicating if record selectors are displayed in the DataGrid control or Split object.

Syntax

object.RecordSelectors [= value]

The RecordSelectors property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines if record selectors are displayed, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | Record selectors are displayed.
False | Record selectors aren't displayed.

Remarks

Record selectors, when displayed, appear to the left of the rows in the grid or split.

If a grid contains multiple splits, then setting its RecordSelectors property has the same effect as setting the RecordSelectors property of each split individually.

Note When the user selects a row by clicking its record selector, the bookmark of the selected row is added to the SelBookmarks collection.


RowDividerStyle Property

Returns or sets a value that specifies the style of border drawn between the rows of the selected DataGrid control.

Syntax

object.RowDividerStyle [= value]

The RowDividerStyle property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | An integer that specifies the border style, as described in Settings.

Settings

The settings for value are:

Setting | Description
0 | No divider
1 | Black line
2 | (Default) Dark gray line
3 | Raised
4 | Inset
5 | Divider is drawn using the ForeColor property setting color

Remarks

The RowDividerStyle property doesn't affect whether the border can be dragged or not. When the border is raised or inset, Microsoft Windows sets the colors.


RowDividerStyle Property Example

This example toggles through the different row line styles when you click a command button.

Private Sub ChangeStyle_Click ()
   If DataGrid1.RowDividerStyle < 5 Then
      DataGrid1.RowDividerStyle = DataGrid1.RowDividerStyle + 1
   Else
      DataGrid1.RowDividerStyle = 0
   End If
End Sub


SelBookmarks Property

Returns a collection of bookmarks for all selected records in the DataGrid control.

Syntax

object.SelBookmarks

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

When a record is selected in the DataGrid control, its bookmark is appended to the collection returned by the SelBookmarks property. For example, if you create a clone of the Recordset object created by the DataGrid control, you can process individual data records by repositioning the cloned Recordset with bookmarks taken from the SelBookmarks collection.


SelBookmarks Property Example

This example loops through the rows the user has selected and deletes them from the database.

Sub DeleteRows()
    Dim varBmk As Variant
    For Each varBmk In DataGrid1.SelBookmarks
        Data1.Recordset.Bookmark = varBmk
        Data1.Recordset.Delete
        Data1.Refresh
    Next
End Sub


VisibleCols Property

Returns a value indicating the number of visible columns in the DataGrid control. Not available at design time and read-only at run time.

Syntax

object.VisibleCols

The VisibleCols property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.

Remarks

The VisibleCols property is an integer ranging from 0 to the total number of columns available, as determined by the Count property of the Columns collection.

This property returns the number of visible columns in the current split. The value returned includes both fully and partially displayed columns.

Use the Split property to determine the index of the current split.


VisibleCols Property Example

This example defines buttons to move the grid a whole page left or right.

Private Sub PageRight_Click ()
   'Page grid to the right.
   If DataGrid1.LeftCol + DataGrid1.VisibleCols < _
       DataGrid1.Columns.Count Then
      DataGrid1.LeftCol = DataGrid1.LeftCol + _
       DataGrid1.VisibleCols
   End If
End Sub

Private Sub PageLeft_Click ()
   'Page grid to the left.
   If DataGrid1.LeftCol - DataGrid1.VisibleCols >= 0 Then
      DataGrid1.LeftCol = DataGrid1.LeftCol - _
       DataGrid1.VisibleCols
   End If
End Sub


VisibleRows Property

Returns a value indicating the number of visible rows in the DataGrid control. This property is read-only at run time.

Syntax

object.VisibleRows

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

The VisibleRows property returns an integer ranging from 0 to the number of rows in the DataGrid control. The VisibleRows property includes either fully or partially visible DataGrid control rows.


VisibleRows Property Example

This example selects all the rows that are currently visible on the grid.

Private Sub SelectVisible_Click ()
   Dim I
   For I = 0 To DataGrid1.VisibleRows - 1
      DataGrid1.SelBookmarks.Add DataGrid1.RowBookmark(I)
   Next I
End Sub


Split

Similar to the "split window" capabilities available with Microsoft Excel where a window is divided into separate vertical panes to provide users with different views of data. Each split is represented by a Split object and contains a group of adjacent columns that scroll as a unit.


Columns Collection

The Columns collection contains all stored Column objects of a DataGrid control.

Syntax

Columns(index)

Columns.Item(index)

Remarks

You can use the properties and methods of the Columns collection to add and remove Column objects, count the number of columns in the Columns collection, and address individual columns of the Columns collection.

The Columns collection can be accessed through the Columns property of the DataGrid control.


Split Object, Splits Collection Example

The following example adds a new fixed split to a DataGrid control. To try this example, add a Data control and a DataGrid control to the form of a new Standard EXE project. Set the DatabaseName and RecordSource properties of the Data control to "BIBLIO.MDB" and "AUTHORS". Paste the following code into the General Declarations section of the form:

Private Sub Form_Load()
   ' Before modifying the grid's properties, make sure
   ' the grid is initialized by refreshing the Data
   ' control.
   Data1.Refresh

   ' Create an additional splits:
   DataGrid1.Splits.Add 0 ' Create an additional split

   ' Hide all columns in the leftmost split,
   ' Splits(0), except for columns 0 and 1
   Dim Cols As Columns, C As Column
   Set Cols = DataGrid1.Splits(0).Columns
   For Each C In Cols
      C.Visible = False
   Next C
   Cols(0).Visible = True
   Cols(1).Visible = True

   ' Configure Splits(0) to display exactly two
   ' columns, and disable resizing
   With DataGrid1.Splits(0)
      .SizeMode = dbgNumberOfColumns
      .Size = 2
      .AllowSizing = False
   End With

   ' Usually, if you fix columns 0 and 1 from
   ' scrolling in a split, you will want to make them
   ' invisible in other splits:
   Set Cols = DataGrid1.Splits(1).Columns
   Cols(0).Visible = False
   Cols(1).Visible = False

   ' Turn off the record selectors in Split 1:
   DataGrid1.Splits(1).RecordSelectors = False
End Sub


Splits Collection

The Splits collection contains all stored Split objects in a DataGrid control.

Syntax

Splits(index)

Splits.Item(index)

Remarks

You can create splits at design time using the grid's UI-active context menu. At run time, you can create and remove splits using the Splits collection's Add and Remove methods. Each method takes a zero-based split index. The following code demonstrates adding and removing splits at run time:

' Create a Split object with index 0
DataGrid1.Splits.Add 0
' Remove the Split object with index 1
DataGrid1.Splits.Remove 1

You can determine the number of splits in a grid using the Splits collection's Count property.


DataGrid Control Constants

AddNewMode Constants

Constant | Value | Description
dbgNoAddNew | 0 | No AddNew
dbgAddNewCurrent | 1 | AddNew current
dbgAddNewPending | 2 | AddNew pending

Alignment Constants

Constant | Value | Description
dbgLeft | 0 | Left
dbgRight | 1 | Right
dbgCenter | 2 | Center
dbgGeneral | 3 | General

Appearance Constants

Constant | Value | Description
dbgFlat | 0 | Flat appearance
dbg3D | 1 | 3D appearance

Borderstyle Constants

Constant | Value | Description
dbgNoBorder | 0 | None
dbgFixedSingle | 1 | FixedSingle

Dividerstyle Constants

Constant | Value | Description
dbgNoDividers | 0 | No dividers
dbgBlackLine | 1 | Black line
dbgDarkGrayLine | 2 | Dark gray line
dbgRaised | 3 | Raised
dbgInset | 4 | Inset
dbgUseForeColor | 5 | Use forecolor
dbgLightGrayLine | 6 | Light gray line

Error Constants

Constant | Value | Description
dbgBadEvent | 67133793 | Operation is invalid within the event
dbgBindError | 67125399 | Cannot initialize data bindings
dbgColNotFound | 67133767 | Column not found
dbgDataAccessError | 67133792 | Data access error
dbgErrorAdd | 67133782 | Error occurred while trying to add record
dbgErrorDelete | 67133781 | Error occurred while trying to delete record
dbgErrorUpdate | 67133783 | Error occurred while trying to update record
dbgInvalidBookmark | 67133769 | Invalid bookmark
dbgInvalidColumnIndex | 67133765 | Invalid column index
dbgInvalidPropertyValue | 67109760 | Invalid property value
dbgInvalidSplitIndex | 67134021 | Invalid split index
dbgInvRowNum | 67133768 | Invalid row number
dbgMustHaveSplit | 67137538 | Must have split
dbgNoMethNow | 67137539 | Method is not available in this context
dbgNoMultiDelete | 67133785 | Cannot delete multiple rows
dbgNoPropNow | 67133794 | Property is not available in this context
dbgOrphaned | 67137536 | Supporting object on the grid has been deleted
dbgOutOfMemory | 67108871 | Out of memory
dbgScrollOutOfRange | 67133777 | Scroll arguments out of range
dbgSubscriptRange | 67108873 | Subscript out of range
dbgTypeMismatch | 67108883 | Type mismatch
dbgUnknown | 67137537 | An unexpected and unknown error has occurred

Marquee Style Constants

Constant | Value | Description
dbgDottedCellBorder | 0 | Dotted cell border
dbgSolidCellBorder | 1 | Solid cell border
dbgHighlightCell | 2 | Highlight the cell
dbgHighlightRow | 3 | Highlight the row
dbgHighlightRowRaiseCell | 4 | Highlight row, raise cell
dbgNoMarquee | 5 | No marquee
dbgFloatingEditor | 6 | Floating editor

Scroll Bar Constants

Constant | Value | Description
dbgNone | 0 | None
dbgHorizontal | 1 | Horizontal
dbgVertical | 2 | Vertical
dbgBoth | 3 | Both
dbgAutomatic | 4 | Automatic

SplitSizeMode Constants

Constant | Value | Description
dbgScalable | 0 | Scalable
dbgExact | 1 | Exact

TabAction Constants

Constant | Value | Description
dbgControlNavigation | 0 | Control navigation
dbgColumnNavigation | 1 | Column navigation
dbgGridNavigation | 2 | Grid navigation


AfterColEdit Event

Occurs after editing is completed in a grid cell.

Syntax

Private Sub object_AfterColEdit([ index As Integer,] ByVal colindex As Integer)

The AfterColEdit event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
Index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column that was edited.

Remarks

When the user completes editing within a grid cell, as when tabbing to another column in the same row, pressing the ENTER key, or clicking on another cell, the BeforeColUpdate and AfterColUpdate events are executed, and data from the cell is moved to the grid's copy buffer. The AfterColEdit event immediately follows the AfterColUpdate event.

When editing is completed in a grid cell, this event is always triggered, even if no changes were made to the cell or the BeforeColUpdate event was canceled.

The AfterColEdit event will not be fired if the BeforeColEdit event is canceled.


BeforeColEdit Event

Occurs just before the user enters edit mode by typing a character.

Syntax

Private Sub object_BeforeColEdit([ index As Integer,] ByVal colindex As Integer, ByVal keyascii As Integer, cancel As Integer)

The BeforeColEdit event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
Index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column to be edited.
keyascii | An integer representing the ANSI key code of the character typed by the user to initiate editing, or 0 if the user initiated editing by clicking the mouse. KeyAscii is passed by value, not by reference; you cannot change its value to initiate editing with a different character.
cancel | An integer that may be set to True to prevent the user from editing the cell, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | The cell will not enter edit mode
False | (Default) The ColEdit event is fired immediately, followed by the Change and KeyUp events for the KeyAscii argument, if non-zero.

Remarks

If a floating editor marquee is not in use, this event also occurs when the user clicks the current cell or double clicks another cell.

Use this event to control the editability of cells on a per-cell basis, or to translate the initial keystroke into a default value.

Note The keyascii argument can only be 0 if a floating editor marquee is not in use.


ButtonClick Event (DataGrid Control)

Occurs when the current cell's built-in button is clicked.

Syntax

Private Sub object_ButtonClick([ index As Integer,] ByVal colindex As Integer)

The ButtonClick event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
Index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column whose button was clicked.

Remarks

The built-in button is enabled for a column when its Button property is set to True.

Typically, you enable the column button when you want to drop down a Visual Basic control (such as the built-in combo box, a bound list box, or even another DataGrid control) for editing or data entry. When the button in the current cell is clicked, the ButtonClick event will be fired. You can then write code to drop down the desired control from the cell.


ColEdit Event

Occurs when a cell first enters edit mode by typing a character.

Syntax

Private Sub object_ColEdit([ index As Integer,] ByVal colindex As Integer)

The ColEdit event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
Index | An integer that identifies a control if it is in a control array.
colindex | An integer that identifies the column being edited.

Remarks

If a floating editor marquee is not in use, this event also occurs when the user clicks the current cell or double clicks another cell.

The ColEdit event immediately follows the BeforeColEdit event only when the latter is not canceled.

When the user completes editing within a grid cell, as when tabbing to another column in the same row, pressing the ENTER key, or clicking on another cell, the BeforeColUpdate and AfterColUpdate events are executed if the data has been changed. The AfterColEdit event is then fired to indicate that editing is completed.


Error Event (DataGrid Control)

Occurs only as the result of a data access error that takes place when no Visual Basic code is being executed.

Syntax

Private Sub object_Error([ index As Integer,] ByVal dataerror As Integer, response As Integer)

The Error event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
Index | An integer that identifies a control if it is in a control array.
dataerror | An integer that identifies the error that occurred.
response | An integer that may be set to 0 to suppress error message display, as described in Settings.

Settings

The settings for response are:

Setting | Description
0 | No error message will be displayed.
1 | (Default) The message associated with the error will be displayed.

Remarks

Even if your application handles run time errors in code, errors can still occur when none of your code is executing, as when the user clicks a Data control button or changes the current record by interacting with a bound control. If a data access error results from such an action, the Error event is fired.

Not adding code to this event is equivalent to setting the response argument to 0.

Note Use the ErrorText property to retrieve the error string that will be displayed.


OnAddNew Event

Occurs when a user action invokes an AddNew operation.

Syntax

Private Sub object_OnAddNew([ index As Integer])

The OnAddNew event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.

Remarks

The OnAddNew event occurs when an AddNew operation has been initiated by either of the following:

  • The user modifies a cell within the AddNew row. Typically, this occurs as soon as the user types a character, but may also occur as a result of a built-in radio button or combo box selection.

  • The Value or Text property of a column is set in code when the current cell is within the AddNew row.

This event occurs only if the grid's AllowAddNew property is True.

When the OnAddNew event is fired, the value of the AddNewMode property is 2 - AddNew Pending.


SelChange Event (DataGrid Control)

Occurs when the user selects a different range of rows or columns.

Syntax

Private Sub object_SelChange([ index As Integer,] cancel As Integer)

The SelChange event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.
cancel | A Boolean expression that determines if a change is canceled, as described in Settings.

Settings

The settings for cancel are:

Setting | Description
True | If your event procedure sets the Cancel argument to True, the previous row and column selections (if any) are restored, and the SelStartCol, SelEndCol, and SelBookmarks properties revert to their previous values
False | (Default) Continues with change.

Remarks

This event is triggered under several circumstances:

  • When the user selects a single row by clicking its record selector.

  • When the user adds a row to the list of selected rows by clicking its record selector while holding down the CTRL key.

  • When the user selects a single column by clicking its header.

  • When the user changes the range of selected columns by dragging to an adjacent column within the header row.

  • When the user extends the range of selected columns by holding down the SHIFT key and clicking on an unselected column header.

  • When the user clears the current row or column selection by clicking an individual cell, this event will fire before the RowColChange event.

The current range of selected columns is provided by the SelStartCol and SelEndCol properties. The bookmarks of the selected rows are available in the collection provided by the SelBookmarks property. Within this event procedure, these properties reflect the user's pending selection(s).

This event is only triggered by user interaction with the grid. It cannot be triggered by code.

Note When the user selects a column, any row selections are cleared. Similarly, when the user selects a row, any column selections are cleared.


SplitChange Event

Occurs when the current cell changes to a different cell in another split.

Syntax

Private Sub object_SplitChange([ index As Integer])

The SplitChange event syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
index | An integer that identifies a control if it is in a control array.

Remarks

This event is triggered under several circumstances:

  • When the grid is first displayed.

  • When the user clicks a cell in another split (subject to the setting of the AllowFocus property).

  • When the user presses a navigation key to cross a split boundary (subject to the setting of the TabAcrossSplits property).

  • When the Split property is changed in code to a different value.

  • When a new split is inserted before the current split via code or user interaction.

  • When the current split is removed via code or user interaction.

If the user edits data and then moves the current cell position to a new row in another split, the update events for the original row are completed before the SplitChange event is executed.

If a split change also results in a change to the current row or column, then the SplitChange event will always precede the RowColChange event.


Error occurred while trying to add record

Try correcting the error, if possible, and add the record again. If the error isn't correctable, you cannot add the record.


Caption text is too long

The text in a caption cannot exceed 32768 characters.


Column not found

The column you attempted to locate could not be found.


Invalid column index

There is no column for the index value you specified.


Data access error

An unknown process or error is preventing the DataGrid from accessing data.


Error occurred while trying to delete record

Try correcting the error, if possible, and delete the record again. If the error isn't correctable, you cannot delete the record.


Cannot delete multiple rows

You must delete rows one at a time.


An unknown and unexpected error code was received

An error has occurred which is beyond the scope of the DataGrid to handle. One common cause is that the OLEDB provider was unable to complete a request, or it returned a code that the DataGrid couldn't handle.


<Name> is not a valid data field name

Naming conventions are data-provider–specific. Make sure that the name you are attempting to assign to a data field adheres to the naming conventions specified by that data provider.


Invalid bookmark

The bookmark you specified is not valid for the current datasource.


Invalid setting for <name> property

You attempted to set a value for a property which is not valid for it. Search the documentation for specific information on the property in question and use a setting appropriate for it.


Invalid row number

There is no row for the value you specified.


Invalid setting for ScrollBars property

The value you assigned to the ScrollBars property is outside the available range.


Can't delete last split

You must always have at least one split available for the DataGrid to work properly.


No current record

You must have a record highlighted for the operation to work correctly.


Property is not available in this context

You attempted to access a property which isn't currently available or applicable in the current context. For information on specific usage of the property, search the documentation.


Supporting object on the Datagrid has been deleted

An object which is required for the current operation has been deleted from the DataGrid.


Scroll arguments out of range

The value you supplied to the Scroll method is beyond the range of available rows.


Invalid split index

The value you supplied to the split index is greater or less than the current number of splits.


Error occurred while trying to update record

Try correcting the error, if possible, and update the record again. If the error isn't correctable, you cannot update the record.


CaptureImage Method

Returns a captured image of the grid's display in its current state.

Syntax

object.CaptureImage

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

Use the CaptureImage method to retrieve a snapshot of the grid.

The following code uses the CaptureImage method to assign a snapshot of a DataGrid control to a PictureBox control.

Picture1.Picture = DataGrid1.CaptureImage

Note The CaptureImage method retrieves the image as a metafile typed image. Therefore, the image will resize to the size of its container.


ClearFields Method

Restores the default grid layout.

Syntax

object.ClearFields

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

The ClearFields method restores the default grid layout (with two blank columns) so that subsequent ReBind operations will automatically derive new column bindings from the (possibly changed) data source. You can cancel the grid's automatic layout behavior by invoking the HoldFields method.


ClearSelCols Method

Deselects all columns in a split. If no columns are selected, then this method does nothing.

Syntax

object.ClearSelCols

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

If a grid contains multiple splits, then invoking its ClearSelCols method has the same effect as invoking the ClearSelCols method for the current split. The index of the current split is available through the DataGrid control's Split property.

Use the SelStartCol and SelEndCol properties to determine the current column selection range for a split.


HoldFields Method

Sets the current column/field layout as the customized layout.

Syntax

object.HoldFields

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

The HoldFields method sets the current column/field layout as the customized layout so that subsequent ReBind operations will use the current layout for display. You can resume the grid's automatic layout behavior by invoking the ClearFields method.


SplitContaining Method

Returns the Index value of the split containing the specified coordinate pair.

Syntax

object.SplitContaining x, y

The SplitContaining method syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
x | Required. A single precision value that defines the horizontal coordinate, based on the coordinate system of the grid's container.
y | Required. A single precision value that defines the vertical coordinate, based on the coordinate system of the grid's container.

Remarks

This value ranges from 0 to 1 less than the setting of the Count property of the Splits collection (0 to Splits.Count - 1).

This method is useful when working with mouse and drag events when you are trying to determine where the user clicked or dropped another control in terms of a grid column.

If either argument is outside of the grid's data area, this method returns -1.


Split Object

A Split object represents a split within a DataGrid control.

Remarks

The DataGrid supports Excel-like splits that divide the grid into vertical panes to provide users with different views of a database. Each split is represented by a Split object and contains a group of adjacent columns that scroll as a unit. When a DataGrid object is created, it contains one Split object by default.

You can use splits to present your data in multiple vertical panes. The data panes (or splits) can display data, scroll (vertically) together or independently of each other, and display the same or different columns. You can also use splits to fix one or more columns from scrolling. Unlike other grid products, the fixed columns do not have to be at the left edge of the grid, but can be at the right edge or anywhere in the middle. You can even have multiple groups of fixed columns within a grid.

You cannot stretch a column wider than its split. If you resize a split to be smaller then a column you can still grab the "phantom" splitter bar.

Each Split object maintains its own Columns collection. These independent splits and columns provide you with very powerful and flexible data presentation capabilities.

As mentioned above, a grid (a DataGrid object) initially contains a single split. If additional splits are created, you can determine or set the current split (i.e., the split that has received focus) using the grid's Split property as follows:

' Read the zero-based index of the current split
Variable% = DataGrid1.Split

' Set focus to the split with an index equal to
' Variable%
DataGrid1.Split = Variable%

Each split in a grid is a different view of the same data source, and each split behaves just like an independent grid. If you create additional Split objects without customizing any of the split properties, all splits will be identical and each will behave very much like the original grid with one split.

Some of the properties of the DataGrid control are the same as the properties of a Split object and are considered common. Changes made to a DataGrid control common property also change the same property of the current Split object and vice versa. For example, consider a grid with two splits, and assume that the current split index is 1 (i.e., the grid's Split property is set to 1). If you want to determine the marquee style in use, the following statements are identical:

marquee% = DataGrid1.MarqueeStyle
marquee% = DataGrid1.Splits(1).MarqueeStyle

If the current split index is set to 1, then the following code is equivalent for setting the MarqeeStyle property to dbgSolidCellBorder:

DataGrid1.MarqueeStyle = dbgSolidCellBorder
DataGrid1.Splits(1).MarqueeStyle = dbgSolidCellBorder

Note Common properties are unique to DataGrid objects and their associated Split objects. No other object pairs possess similar relationships.


AddNewMode Property

Returns a value that describes the location of the current cell with respect to the grid's AddNew row. Read-only at run time and not available at design time.

Syntax

object.AddNewMode

The AddNewMode property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.

Values

The AddNewMode property returns one of the following:

Constant | Value | Description
dbgNoAddNew | 0 | The current cell is not in the last row, and no AddNew operation is pending.
dbgAddNewCurrent | 1 | The current cell is in the last row, but no AddNew operation is pending.
dbgAddNewPending | 2 | The current cell is in the next to last row as a result of a pending AddNew operation initiated by the user through the grid's user interface, or by code as a result of setting the Value or Text properties of a column.

Remarks

If the AllowAddNew property is True, the last row displayed in the grid is left blank to permit users to enter new records. If the AllowAddNew property is False, the blank row is not displayed, and AddNewMode always returns 0.


AllowArrows Property

Sets or returns a value that determines whether the control uses the arrow keys for grid navigation.

Syntax

object.AllowArrows [= value]

The AllowArrows property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines the arrow keys are used for grid navigation, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | (Default) The user can use the arrow keys to move both from cell to cell and row to row.
False | The left and right arrow keys will move focus from control to control and cannot be used to move between cells.

Remarks

The user cannot use the arrow keys to move out of the DataGrid control when this property is set to True. If the WrapCellPointer property is also set to True, then the arrow keys will wrap around rows and the user can navigate the entire grid using the arrow keys.


AllowFocus Property

Sets or returns a value that determines whether cells within a split can receive focus.

Syntax

object.AllowFocus [= value]

The AllowFocus property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether a cell receives focus, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | (Default) The user will be able to interactively select the split, giving it focus.
False | The user will not be able to interactively select the split. When clicked on, the split will not receive focus and the control that previously had focus will retain it.

Remarks

Use this property in combination with the AllowSizing property to completely prohibit the user from making any changes to a split (by setting both properties to False). Unselectable splits are passed over when TabAcrossSplits is set to True.


ApproxCount Property

Returns the approximate number of rows in the grid.

Syntax

object.ApproxCount

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

This property returns the approximate row count used by the grid to calibrate the vertical scroll bar.

Typically, the ApproxCount property is used to improve the accuracy of the vertical scroll bar. This is particularly useful for situations where the number of rows is known in advance, such as when a grid is used in conjunction with an array.

Note Getting the ApproxCount property will query the underlying data source.


Bookmark Property (DataGrid)

Returns or sets a bookmark for the specified row within a RowBuffer object in an unbound DataGrid control.

Syntax

object.Bookmark (row) [= value]

The Bookmark property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
row | An integer specifying the row where the bookmark is placed. The range of this value can be from 0 to RowCount – 1.
value | A variant representing the bookmark for the specified row.

Remarks

Use the value returned by the Bookmark property to save a reference to the current row that remains valid even after another row becomes current.

When you set the Bookmark property to a valid value in code, the row associated with that value becomes the current row, and the grid adjusts its display to bring the new current row into view if necessary.

The Bookmark property is defined as a Variant to accommodate user-defined bookmarks in unbound mode.

In the UnboundReadData event there may be multiple rows, so you must provide a bookmark for each row.

The UnboundWriteData event passes a bookmark to you to identify the row of data to be updated.

The UnboundAddData event passes a bookmark to you to identify the row of data to be added.

Note In unbound mode, setting the Bookmark property to itself will force the current row to be updated via the UnboundWriteData event.


Bookmark Property (DataGrid) Example

In this example, when the user deletes a row in the unbound DataGrid control, the UnboundDeleteRow event is triggered, allowing you to manually delete the row from your data set, in this case, a simple array. The following code fragment shows how a bookmark is passed as an argument in the UnboundDeleteRow event to identify the row to be deleted.

Private Sub DataGrid1_UnboundDeleteRow(Bookmark As Variant)
  For i% = Bookmark + 1 To RowCount - 1
    For j% = 0 to MAXCOLS - 1
    UserData(j%, I% - 1) = UserData(j%, I%)
    Next j%
  Next I%
End Sub

Refer to the UnboundReadData event example for an example of assigning data to the Bookmark property.


Button Property (Column Object)

Sets or returns a value that determines whether a button is displayed within the current cell.

Syntax

object.Button [= value]

The Button property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines if a button is displayed within the current cell, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | A button will be displayed in the upper right corner of the current cell at run time.
False | (Default) No button will be displayed.

Remarks

Typically, you enable the column button when you want to drop down a control (such as the built-in combo box, a bound list box, or even another DataGrid control) for editing or data entry. When the button in the current cell is clicked, the ButtonClick event will be fired. You can then write code to drop down the desired control from the cell.


Caption Property (DataGrid Control, Column Object)

For a DataGrid control, this property determines the text displayed in the caption bar at the top of the grid.

For a Column object, this property determines the text displayed in the column's heading area.

Syntax

object.Caption [= value]

The Caption property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A string expression that determines what is displayed, as described below.

Remarks

Setting the Caption property to an empty string for a DataGrid control hides its caption bar.

Setting the Caption property to an empty string for a Column object clears the text in the column's heading area but does not hide the heading. Column captions are only displayed if the DataGrid control's ColumnHeaders property is set to True and the HeadLines property is not set to 0.


CurrentCellModified Property

Sets or returns modification status of the current cell. Not available at design time.

Syntax

object.CurrentCellModified [= value]

The CurrentCellModified property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines the modification status of the current cell, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | Editing is in progress and the current cell (indicated by the Bookmark and Col properties) has been modified by the user.
False | The cell has not been modified or editing is not in progress.

Remarks

You can use this property to cancel any changes the user has made to the current text. For example, to program a function key to discard the user's changes (like the ESC key), trap the key code in the grid's KeyDown event and set CurrentCellModified to False. This will revert the current cell to its original contents.


CurrentCellVisible Property

Sets or returns the visibility of the current cell. Not available at design time.

Syntax

object.CurrentCellVisible [= value]

The CurrentCellVisible property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines the visibility of the current cell, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | The current cell (indicated by the Bookmark and Col properties) is visible within the displayed area of a grid or split.
False | The cell is not visible.

Remarks

For a DataGrid control, setting the CurrentCellVisible property to True causes the grid to scroll so that the current cell is brought into view. If a grid contains multiple splits, then the current cell becomes visible in each split.

For a Split object, setting the CurrentCellVisible property to True makes the current cell visible in that split only.

In all cases, setting this property to False is meaningless and is ignored.


DataChanged Property (DBGrid)

DataChanged Property (DataGrid)

Returns or sets a value indicating that the data in the bound control has been changed by some process other than that of retrieving data from the current record. Not available at design time.

Syntax

object.DataChanged [= value]

The DataChanged property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that indicates whether data has changed, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | The data currently in the control isn't the same as in the current record.
False | (Default) The data currently in the control, if any, is the same as the data in the current record.

Remarks

When a data control moves from record to record, it passes data from fields in the current record to controls bound to the specific field or the entire record. As data is displayed in the bound controls, the DataChanged property is set to False. If the user or any other operation changes the value in the bound control, the DataChanged property is set to True. Simply moving to another record doesn't affect the DataChanged property.

When the data control starts to move to a different record, the Validate event occurs. If DataChanged is True for any bound control, the data control automatically invokes the Edit and Update methods to post the changes to the database.

If you don't wish to save changes from a bound control to the database, you can set the DataChanged property to False in the Validate event.

Inspect the value of the DataChanged property in your code for a control's Change event to avoid a cascading event.

Data Type

Boolean


DataField Property (DBGrid Control, Column Object)

DataField Property (DataGrid Control, Column Object)

Returns or sets a value that binds a control to a field in the current record

Syntax

object.DataField [= value]

The DataField property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A string expression that evaluates to the name of one of the fields in the Recordset object specified by a data control's RecordSource and DatabaseName properties.

Remarks

Bound controls provide access to specific data in your database. Bound controls that manage a single field typically display the value of a specific field in the current record. The DataSource property of a bound control specifies a valid data control name, and the DataField property specifies a valid field name in the Recordset object created by the data control. Together, these properties specify what data appears in the bound control.

When you use a QueryDef object or SQL statement that returns the results of an expression, the field name is automatically generated by the Microsoft Jet database engine. For example, when you code an SQL aggregate function or an expression in your SQL query, unless you alias the aggregate fields using an AS clause, the field names are automatically generated. Generally, the expression field name is Expr1 followed by a three-character number starting with 000. The first expression returned would be named Expr1000.

It's recommended that you code your SQL queries to alias expression columns as shown below:

Adodc1.RecordSource = "Select AVG(Sales)  " _
   & " AS AverageSales From SalesTable"
Text1.DataField = "AverageSales"
Adodc1.Refresh

Note Make sure the DataField property setting is valid for each bound control. If you change the setting of a data control's RecordSource property and then use Refresh, the Recordset identifies the new object. This may invalidate the DataField settings of bound controls and produce a trappable error.

Data Type

String


DefaultValue Property (DBGrid Control)

DefaultValue Property (DataGrid Control)

Sets the default value for new column data.

Syntax

object.DefaultValue [= value]

The DefaultValue property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Variant expression containing the default value for the specified column.

Remarks

The DataGrid control does not use this property itself, but provides it as a placeholder for you to associate default values with columns in a DataGrid control. This property can be used as a tag for a column. Arbitrary values can be stored and retrieved later.


EditActive Property

Sets or returns the editing status of the current cell. Not available at design time.

Syntax

object.EditActive [= value]

The EditActive property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines the editing status, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | The current cell is currently being edited by the user.
False | No editing is in progress.

Remarks

If the grid is not already in edit mode, setting EditActive to True will initiate editing on the current cell. The caret will be positioned at the end of the cell and the ColEdit event will be triggered.

If the grid is already in edit mode, setting EditActive to False will exit edit mode. If the cell has been modified, this will trigger the following events: BeforeColUpdate, AfterColUpdate, and AfterColEdit.

Note To cancel editing completely, set the CurrentCellModified property to False, then set EditActive to False.


ErrorText Property

Returns the error message string from the underlying data source. Not available at design time.

Syntax

object.ErrorText

The ErrorText property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.

Remarks

When a database error occurs as a result of user interaction with the grid, such as when the user enters text into a numeric field and then attempts to update the current record by moving to another row, the grid's Error event will fire. However, the error code passed to the event handler in the DataError parameter may not identify the specific error that occurred, or may even differ across operating environments. For these reasons, the ErrorText property is provided so that your application can parse the actual error message to determine the nature of the error.

Note The ErrorText property is only valid within a DataGrid control's Error event handler. A trappable error will occur if you attempt to access it in any other context.


hWndEditor Property

Returns the unique window handle assigned to a DataGrid control's editing window by the Microsoft Windows operating environment. Not available at design time.

Syntax

object.hWndEditor

The hWndEditor property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.

Remarks

Experienced users can pass the value of this property to Windows API calls that require a valid window handle.

When editing is not in progress, this property returns 0.

Note Since the value of this property can change while a program is running, never store the hWndEditor value in a variable. Also, do not use the hWndEditor property to test whether editing is in progress. The EditActive property is provided for this purpose.


Index Property (Split Object)

Returns the index of the selected split.

Syntax

object.Index

The object placeholder represents an object expression that evaluates to an object in the Applies To list.

Remarks

This property is useful if you need to identify a given Split object.


LeftCol Property

Returns or sets an integer representing the leftmost visible column of a DataGrid control. this property is read-only at design time.

Syntax

object.LeftCol [= value]

The LeftCol property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A numeric expression indicating the leftmost visible column. The default value is 0.


MarqueeStyle Property

Sets or returns the Marquee style for the DataGrid control or Split object.

Syntax

object.MarqueeStyle [= value]

The MarqueeStyle property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A number or constant that specifies the Marqee style, as described in Settings.

Settings

The settings for value are:

Constant | Value | Description
dbgDottedCellBorder | 0 | The current cell within the current row will be highlighted by drawing a dotted border around the cell. In Microsoft Windows terminology, this is usually called a focus rectangle.
dbgSolidCellBorder | 1 | The current cell within the current row will be highlighted by drawing a solid box around the current cell. This is more visible than the dotted cell border, especially when 3-D divider properties are used for the grid.
dbgHighlightCell | 2 | The entire current cell will be highlighted by inverting the colors within the cell. This provides a very distinctive block-style highlight for the current cell.
dbgHighlightRow | 3 | The entire row containing the current cell will be highlighted by inverting the colors within the row. In this mode, it is not possible to visually determine which cell is the current cell, only the current row. When the grid or split is not editable, this setting is often preferred, since cell position is then irrelevant.
dbgHighlightRowRaiseCell | 4 | The entire row will be highlighted. The current cell within the row will be "raised" so that it appears distinctive. This setting doesn't appear clearly with all background color and divider settings. The best effect is achieved by using 3-D dividers and a light gray background.
dbgNoMarquee | 5 | The marquee will not be shown. This setting is useful for cases where the current row is irrelevant, or where you don't want to draw the user's attention to the grid until necessary.
dbgFloatingEditor | 6 | The current cell will be highlighted by a floating text editor window with a blinking caret (as in Microsoft Access). This is the default setting.

Remarks

If a grid contains multiple splits, then setting its MarqueeStyle property has the same effect as setting the MarqueeStyle property of each split individually.

Note If the floating editor marquee setting is in effect and the current cell contains radio buttons or graphics, then a dotted focus rectangle will be displayed.


ScrollGroup Property

Used to synchronize vertical scrolling between splits.

Syntax

object.ScrollGroup [= value]

The ScrollGroup property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | An integer expression that determines the scroll group that a split belongs to.

Remarks

This property is used to synchronize vertical scrolling between splits. All splits with the same ScrollGroup setting will be synchronized when vertical scrolling occurs within any one of them. Splits belonging to different groups can scroll independently, allowing different splits to display different parts of the database.

If the ScrollBars property for a split is set to 4 - Automatic, only the rightmost split of the group will have a vertical scroll bar. If there is only one split, setting this property has no effect.

Setting the FirstRow property for one split affects all other splits in the same group, keeping the group synchronized.

Newly created splits have a ScrollGroup value of 1.


SizeMode Property (Split Object)

Sets or returns a value that determines how the Size property is used to determine the actual size of a split.

Syntax

object.SizeMode [= value]

The SizeMode property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A number or constant that determines how the Size property is used, as described in Settings.

Settings

The settings for value are:

Constant | Value | Description
dbgScalable | 0 | (default) The value returned by the Size property is an integer indicating the relative size of the split with respect to other scalable splits. For example, if a grid contains 3 scalable splits with Size properties equal to 1, 2, and 3, the size of each split would be 1/6, 1/3, and 1/2 of the total grid width, respectively.
dbgExact | 1 | The value returned by the Size property is a floating point number indicating the exact size of the split in terms of the coordinate system of the grid's container. This setting allows you to fix the size of the split so that it always has the same width, even if new splits are added or existing splits are removed.
dbgNumberOfColumns | 2 | The value returned by the Size property is an integer indicating the number of columns displayed in the split, and the split will adjust its width to display the number of full columns specified by the Size property. For example, if Size is set to 2, and the user scrolls the split horizontally, the width of the split will change so that 2 full columns are displayed, regardless of how wide the columns are.

Remarks

When there is only one split (the grid's default behavior), the split spans the entire width of the grid, the SizeMode property is always 0 - dbgScalable, and the Size property is always 1. Setting either of these properties has no effect when there is only one split. If there are multiple splits, and you then remove all but one, the SizeMode and Size properties of the remaining split automatically revert to 0 and 1, respectively.

Consider a grid containing both scalable splits and splits with a fixed number of columns. If a split with a fixed number of columns is scrolled horizontally, the total width remaining for the scalable splits may change because grid columns are generally of different widths. However, the ratios of the sizes of the scalable splits remain the same as specified by their Size properties.


Size Property (Split Object)

Sets or returns the size of a split.

Syntax

object.Size [= value]

The Size property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | An integer expression that specifies the size of the split.

Remarks

The meaning of the value returned by this property is determined by the split's SizeMode property setting.

If SizeMode is set to the default value of 0 - Scalable, the value returned by the Size property is an integer indicating the relative size of the split with respect to other scalable splits.

If SizeMode is set to 1 - Exact, the value returned by the Size property is a floating point number indicating the exact size of the split in terms of the coordinate system of the grid's container.

If SizeMode is set to 2 - Number of Columns, the value returned by the Size property is an integer indicating the number of columns displayed in the split.

Note When there is only one split (the grid's default behavior), the split spans the entire width of the grid, the SizeMode property is always 0 - dbgScalable, and the Size property is always 1. Setting either of these properties has no effect when there is only one split. If there are multiple splits, and you then remove all but one, the SizeMode and Size properties of the remaining split automatically revert to 0 and 1, respectively.


Split Property

Sets or returns the index of the current split. Not available at design time.

Syntax

object.Split [= value]

The Split property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Long that specifies the index of the current split, as described in Remarks.

Remarks

The Split property specifies a zero-based index of the current split.


Splits Property

Returns a collection of Split objects. Not available at design time.

Syntax

object.Splits

The Splits property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.


TabAcrossSplits Property

Sets or returns the behavior of the tab and arrow keys at split borders.

Syntax

object.TabAcrossSplits [= value]

The TabAcrossSplits property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines the behavior of the tab and arrow keys at split borders, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | Tab and arrow keys will move the current cell across split boundaries. When at the last column of the rightmost split (or the first column of the leftmost split), they will either wrap to the next row, stop, or move to other controls depending on the values of the WrapCellPointer and TabAction properties.
False | (Default) The tab and arrow keys will not move the current cell across split boundaries. They will either wrap to the next row, stop, or move to other controls depending on the values of the WrapCellPointer and TabAction properties.

Remarks

The TabAcrossSplits property does not determine if the tab and arrow keys will move from cell to cell, or from control to control, or wrap to the next row. Use the AllowArrows, WrapCellPointer, and TabAction properties to control this behavior. If the tab and arrow keys are able to move from cell to cell, this property determines whether they will move across split boundaries to adjacent splits.


TabAction Property

Sets or returns a value that defines the behavior of the tab key.

Syntax

object.TabAction [= value]

The TabAction property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A number or constant that defines the behavior of the tab key, as described in Settings.

Settings

The settings for value are:

Constant | Value | Description
dbgControlNavigation | 0 | (Default) The tab key moves to the next or previous control on the form.
dbgColumnNavigation | 1 | The tab key moves the current cell to the next or previous column. However, if this action would cause the current row to change, then the next or previous control on the form receives focus.
dbgGridNavigation | 2 | The tab key moves the current cell to the next or previous column. The behavior of the tab key at row boundaries is determined by the WrapCellPointer property. When this setting is used, the tab key never results in movement to another control.

Remarks

The TabAction property does not determine if the tab key will cross split boundaries. Use the TabAcrossSplits property to control this behavior.

The value of the TabAction property overrides the WrapCellPointer property behavior. For example, if WrapCellPointer is True and TabAction is set to dbgColumnNavigation, and you're on the last column of the DataGrid, pressing the Tab key brings you to the next control in the tab index order instead of to the first column of the next row.


Value Property (Column Object)

Sets or returns the underlying data value in a column for the current row. Not available at design time.

Syntax

object.Value [= value]

The Value property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A string expression that represents the underlying data value in a column for the current row.

Remarks

The Value property is useful for simulating data entry within a cell. When this property is set, the value displayed in the cell respects the setting of the column's NumberFormat property.

This property always returns a string variant, even if the data type of the underlying field is numeric.

Use the Text property to access the formatted data value in a column for the current row.


WrapCellPointer Property

Sets or returns a value that determines the behavior of the arrow keys.

Syntax

object.WrapCellPointer [= value]

The WrapCellPointer property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines the behavior of arrow keys, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | The cell pointer will wrap from the last column to the first in the next row (or from the first column to the last in the previous row).
False | (Default) The cell pointer will not wrap to the next (or previous) row, but will stop at the last (or first) column of the current row.

Remarks

If TabAcrossSplits is False, the cell pointer will wrap only within the current split. If TabAcrossSplits is True, the cell pointer will move from one split to the next before wrapping occurs.

If TabAction is set to 2 - Grid Navigation, the tab key will behave like the arrow keys, and will automatically wrap to the next or previous cell.


WrapText Property (Column Object)

Sets or returns a value indicating whether an object word wraps text at cell boundaries.

Syntax

object.WrapText [= value]

The WrapText property syntax has these parts:

Part | Description
object | An object expression that evaluates to an object in the Applies To list.
value | A Boolean expression that determines whether an object word wraps, as described in Settings.

Settings

The settings for value are:

Setting | Description
True | A line break occurs before words that would otherwise be partially displayed.
False | (Default) No line break occurs and text is clipped at the cell's right edge.

Remarks

Use this property in conjunction with the RowHeight property to produce multi-line displays.