VB6 Data Grid - Cell Data Validation technique unclear 1

[h4fod]

Hi
I have a dataGrid control populated by way of an ado control. I have set arguments as shown to enable read / write access. I now want to validate grid cell data before it updates the field data in the underlying ado / access database. I have tried many permutations of using the 'click' 'change' and 'validate' options of the Datagrid control to trap the automatic update which would otherwise occur - but have failed!.

For example one data bound table field stores [RewardsPoints]and I wish to set a range check of >=0 and <=1000. Intuitively I would validate data using following syntax which returns an error. Would then check say Column(1) for numeric data etc etc

If DataGridRewards.Columns(0).CellValue <= 0 Or DataGridRewards.Columns(0).CellValue >= 1000 Then
'Prevent update from ocurring - msgbox advice to user
End If

Would appreciate help on this.

many thanks in anticipation.

For info:
Also, data Grid AllowAddnew, AllowUpdate, Allowupdates all set to true - since this is a requirement.

Part Code only:

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source= " & "C:\temp\rewardsdatabase.mdb" & " ;Persist Security Info = false"
conn.Open

'Build an SQL string to select all the rewards currently in the file (tblRewards)
strSQL = "SELECT * FROM tblRewards"
adoPoints.Open (strSQL), conn, adOpenStatic, adLockOptimistic


Set DataGridRewards.DataSource = adoPoints
DataGridRewards.Refresh

 

[CP60]

Have a look at the following grid events:

Private Sub DataGrid1_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
End Sub

Private Sub DataGrid1_BeforeUpdate(Cancel As Integer)
End Sub

Also, you could declare the recordset using WithEvents and then use the following event:

Private Sub rsADO_WillChangeField(ByVal cFields As Long, ByVal Fields As Variant, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

End Sub

Setting adStatus to adStatusCancel will cancel the field change if your validation check fails

 

[h4fod]

Hi
Thanks for post. Been trying for some time now to get a solution using your first suggestion. Code for event as follows. Basically, I assume that if the modified field value is out of range, the cell value would be returned to its 'original' value.

However, I cannot seem to trap the 'original' value in a particular cell (in column(1) in this example) using the cick event of the DataGrid object and assign it to a variable (public scope - varTemp) for use in the sub above. If I can do this then I am confident that I can get this solution to work. The DataGrid dosent seem to have a property/method to access the field value of the cell which has been 'clicked'.

The intChange variable below correctly stores the cell value prior to update, as expected.

many thanks

Private Sub DataGridRewards_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
Dim intChange As Integer
'Assign proposed field change value to a temporary local variable
intChange = DataGridRewards.Columns(1).Value

'If proposed value is out of range then re-assign original value
If intChange <= 0 Or intChange > 1000 Then
    DataGridRewards.Columns(1).Value = varTemp
End If
'Etc Etc for other cols

End Sub

 

[CP60]

In that event.

TheDataField = DataGridRewards.Columns(1).DataField

The recordset field current value, before, during and after updating
x = adoPoints.Fields(TheDataField).Value

The recordset field original value prior to the column or recordset field from updating
y = adoPoints.Fields(TheDataField).UnderlyingValue

 

[h4fod]

Hi
I note from your last post that the focus is on programming the ado recordset object. Since I am looking (at the moment anyway) on programming the dataGrid object, is there any way that this can be achieved or I will need the ado workaround - your last post. If so, despite your last post still little unclear how to code the validation routine. Hope you can clarify - thanks.

 

[CP60]

I wrote: "In that event"

Meaning, in the BeforeColUpdate() event you posted add the code...Simple as that.
The ADO code would be easier to work with, as the grid is bound to it.

But, if you want just grid code:

Private Sub DataGridRewards_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
    Dim bErr As Integer

    If DataGridRewards.Columns(1).Value <> OldValue Then
        'Some Validation here
        If DataGridRewards.Columns(1).Value < 0 Then
            'Assuming Validation failed
            bErr = vbTrue
        End If
    End If
    
    Cancel = bErr 'This will rest the change in the grid's cell
End Sub

You will also need to use the grid event:
Private Sub DataGrid1_BeforeUpdate(Cancel As Integer)
in case the user clicks on a different record instead of a different column.

 

[h4fod]

Hi
Eureka! Yes its clear now - and works as I wanted it to. Looking at one the arguments of the data grid BeforeColUpdate, namely 'Oldvalue'it should have been more apparent to me. So, many thaks indeed for your helpful responses. In the next week or so I shall experiment with the ado solution you suggested too. Hope you can help out if I get stuck here!!

thanks again

 

[CP60]

Welcome.

Please note: ColIndex is the column being edited, so you will want to use a condition on that so your column #1 doesn't get checked each time.

If ColIndex = 1 Then
If DataGridRewards.Columns(1).Value <> OldValue Then

End If
End If

 

[h4fod]

Hi
Thanks, noted. Before I received your post I extended the if constructs as follows using Colums(index) as shown and this seems to work. Validation warning message helpful to user to constrain input values.

Private Sub DataGridRewards_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)

'Note that oldvalue is an argument variable in the DataGrid object.
'Compare the old cell value - that is the existing value against
'the value entered by the user.  If the value satisfies validation
'rule comit this value, otherwise restore the cell value to its 'old'
'value.

Dim bErr As Integer
    'Data Validate Points Field - Column (1)
    If Val(DataGridRewards.Columns(1).Value) <> OldValue Then
        'data validation - points Field
        If DataGridRewards.Columns(1).Value < 0 Or DataGridRewards.Columns(1).Value > 3000 Then
            'Assuming Validation failed
            MsgBox "points value must be between 0 and 3000"
            bErr = vbTrue
        End If
    End If
    
    'Data Validate Description Field - Column (2)
    If Val(DataGridRewards.Columns(2).Value) <> OldValue Then
        'data validation - points Field
        If DataGridRewards.Columns(2).Value = "" Then
            'Assuming Validation failed
            MsgBox "You must enter a description"
            bErr = vbTrue
        End If
    End If
    
    
    Cancel = bErr 'This will rest the change in the grid's cell

End Sub

 

[CP60]

You cannot do it that way. When column 2 validates, the OldValue of column 2 will test against the value of column 1.
Do this:

Dim bErr As Integer

Select Case ColIndex
Case 1
'Data Validate Points Field - Column (1)
etc.

Case 2
'Data Validate Points Field - Column (2)
etc.

End Select

Cancel = bErr 'This will reset the change in the grid's cell

 

[h4fod]

Yes, see what you mean. Thanks for the last post - have now modified my code. Will try ado solution on the weekend and follow up.

Thanks again