Validating end-user data entries

When finished making changes to a cell, the user can terminate editing by pressing the Enter key, moving to another cell using the arrow keys, or clicking another cell with the mouse. If the user stays in the same row, data is not updated to the database. At the end of each cell editing session, you have the opportunity to reject or change the user's modifications in the BeforeColUpdate event:

Example Title	Copy Code
Private Sub TDBGrid1_BeforeColUpdate( _ ByVal ColIndex As Integer, _ OldValue As Variant, Cancel As Integer)

ColIndex is the index of the column just edited. OldValue is the value of the cell before any changes were made to it. It is possible to edit a cell many times before updating to the database, but OldValue will be the same for all the updating sessions.

You generally use BeforeColUpdate to validate data entered by the user before leaving the cell. You can examine or change the edited data using the Column object's Text and Value properties. In any data mode, you can compare the changes to the original cell data using the OldValue argument. In bound mode only, you can also examine the field value of the data control:

Example Title	Copy Code
Data1.Recordset.Fields(ColIndex).Value

Note that this expression assumes that the grid's columns match the members of the Fields collection of the Data control's Recordset. This assumption holds true if you are using the grid's automatic layout feature. However, if you have defined your own layout, then you should reference the Fields collection by name:

Example Title	Copy Code
Data1.Recordset.Fields("FieldName").Value

Note the similarity between the Column object and Columns collection and the Field object and the Fields collection. You can access the members of either collection by numeric index or name, whichever is more convenient.

You can cancel the update by setting Cancel to True in the BeforeColUpdate event. If canceled, the grid will restore the cell to its value before the current editing session. The restored value may not be the same as OldValue if the user has edited the cell multiple times. The cell movement will be canceled and the current cell remains in place.

If the update is not canceled, the AfterColUpdate event will fire:

Example Title	Copy Code
Private Sub TDBGrid1_AfterColUpdate(ByVal ColIndex As Integer)

and the current cell moves to a new location if appropriate. The current cell does not change if the user has left editing by pressing the Enter key. Note that unless the cell is moved to another row, the user has only changed the visible data in the grid; no change will be made to the database. Before the change has been updated to the database, the user can always restore the cell to OldValue by pressing the Esc key. For more information, see Database Operations.

The following example prevents the user from leaving the current cell if the value entered is an empty string. Although your first inclination may be to display a message box for the user in BeforeColUpdate, this will not work as expected because the message box will cause the BeforeColUpdate event to fire again before it is finished, resulting in an endless series of message boxes. The solution is to post a message to the grid using the PostMsg method, then show the message box in the handler for the PostEvent event, which will not fire until the BeforeColUpdate event has finished.

Example Title	Copy Code
Private Sub TDBGrid1_BeforeColUpdate( _ ByVal ColIndex As Integer, _ OldValue As Variant, Cancel As Integer) If TDBGrid1.Text = "" Then ' Schedule the PostEvent event. TDBGrid1.PostMsg 1 ' The following line is used to keep the cell blank (remove it to restore ' the old cell value). OldValue = "" ' Cancel the update and keep focus on this cell. Cancel = True End If End Sub Private Sub TDBGrid1_PostEvent(ByVal MsgId As Integer) ' 1 is the argument to PostMsg in BeforeColUpdate. If MsgId = 1 Then MsgBox "Cells may not be empty" End Sub

Example Title

Copy Code

Private Sub TDBGrid1_BeforeColUpdate( _

        ByVal ColIndex As Integer, _

        OldValue As Variant, Cancel As Integer)

 

    If TDBGrid1.Text = "" Then

        ' Schedule the PostEvent event.

        TDBGrid1.PostMsg 1

 

        ' The following line is used to keep the cell blank (remove it to restore

        ' the old cell value).

        OldValue = ""

 

        ' Cancel the update and keep focus on this cell.

        Cancel = True

    End If

End Sub

 

Private Sub TDBGrid1_PostEvent(ByVal MsgId As Integer)

 

    ' 1 is the argument to PostMsg in BeforeColUpdate.

    If MsgId = 1 Then MsgBox "Cells may not be empty"

 

End Sub