Validations in DBGrid

[daneb]

Hi.

I have a DBGrid bounded to an Access database table. The user is allowed to input values in some cells if necessary. For instance, if all the cells in a certain column must only have a numeric input less than a byte (255), how can I validate the input in that cell? I tried inputting values such as 256, or a string, and an error message was displayed saying "Data type conversion error" which was given automatically by VB. What I want to do is to catch the error and display a customized error message. How can I do this?

I also noticed that automatic validation only occurs when I leave a cell using the Down arrow or by clicking another cell. If I use the other arrows or press Enter, nothing happens. How can I prevent the user from leaving a cell if his/her input is wrong?

Thanks for the info in advance!

[vchapran]

First of all - it's bad idea to use data control. You have not enough flexibility - your case is a good example of that. However you can use something like:

option Explicit
Dim varTemp as Variant
private Sub DBGrid1_AfterColEdit(byval ColIndex as Integer)
Select Case ColIndex
Case 0
'do your validation for first column, for example
'do not allow numeric values. In case if it's numeric,
'restore value saved in varTemp
If IsNumeric(DBGrid1.Columns(ColIndex).Value) then
MsgBox "This column cannot contain number"
DBGrid1.Columns(ColIndex).Value = varTemp
End If
Case 1
If Val(DBGrid1.Columns(ColIndex).Value) > 255 then
MsgBox "Cannot be greater than 255"
DBGrid1.Columns(ColIndex).Value = 255
End If
Case 2
'do validation here
End Select
End Sub

private Sub DBGrid1_ColEdit(byval ColIndex as Integer)
varTemp = DBGrid1.Columns(ColIndex).Value
End Sub



This stupid code just shows you, how you can do some kind of validation with DBGrid and Data control. I used Author table from Biblio.mdb as a source.
Do not accept this code as it is - it's just demonstration. Edit it for your data source. I did not have the time to write everything correct.
HTH
Vlad

[daneb]

Thanks for the reply. I tried your code and it worked! However, sometimes when I input a string in a cell that is supposed to contain a numeric value, the "Data type conversion error" still appears, and this is what I cannot catch. I put an On Error Goto line at the start of the DBGrid1_AfterColEdit sub but the error is undetected. I need to catch this error so that I can also restore the original cell value in case an input of the wrong data type is entered. Any more suggestions?

[vchapran]

Sorry, I suggested to use wrong event procedures. There is a better way:

private Sub DBGrid1_BeforeColUpdate(byval ColIndex as Integer, OldValue as Variant, Cancel as Integer)


Select Case ColIndex
Case 0
'do your validation for first column, for example
'do not allow numeric values. In case if it's numeric,
'restore value saved in varTemp
If IsNumeric(DBGrid1.Columns(ColIndex).Value) then
MsgBox "This column cannot contain number"
DBGrid1.Columns(ColIndex).Value = OldValue
End If
Case 1
If IsNumeric(DBGrid1.Columns(ColIndex).Value) then
If Val(DBGrid1.Columns(ColIndex).Value) > 255 then
MsgBox "Cannot be greater than 255"
DBGrid1.Columns(ColIndex).Value = 255
End If
else
'either enter some numeric as default (uncomment if you like it)
'DBGrid1.Columns(ColIndex).Value = 255
'or do not allow to leave a cell (uncomment if you like this)
Cancel = true
'DBGrid1.Col = ColIndex
End If
Case 2
If Val(DBGrid1.Columns(ColIndex).Value) < 500 then
MsgBox "Cannot be greater less than 500"
DBGrid1.Columns(ColIndex).Value = 500
End If
End Select
End Sub




One more time - stop using data bound stuff. You are going to have more serious problems

Vlad

[daneb]

Thanks again. You're right, I tried your new code and the focus stayed in the cell if an input is invalid, except for the "Data type conversion error" automatically shown by VB.

I just have a follow-up question. What if certain columns in my dbgrid are dependent on values entered in other columns? Say, in my Column3, if a user entered a value belonging to a certain valid range, then Column10 will be available for input, otherwise, Column10 will be disabled. I clicked the Locked property of a column during design time in the Properties page, but I cannot use it during run-time. .Enabled is not a valid property of a column. Do you have any ideas?

[vchapran]

To avoid Data Conversion error move Cancel = True up. It will cancel the communication between Grid and DB.
Regarding disabling Cell. I have just a little experience with this control, because I don't like Data Bound approach. Note, that Microsoft Controls do generic stuff usually. For more sofisticated results you need to use either third party controls (there is a lot of grids on the market - Sheridan, FarPoint, Apex and more), or to use some tricks, like covering Grid Cell with Label for example (during runtime ofcourse), and since Label is not editable, it will look like locked cell.
Vlad

[daneb]

Yes, your suggestion of moving the Cancel = True line worked!

Regarding my previous post of disabling of some cells, what I have worked out is not the disabling of some cells but of entire columns instead. In the DBGrid's Property page, only columns can be locked. Therefore, if an input is valid in cell Row1-Column1 for instance, the corresponding cell in Column10 in the same row will be enabled for inputting. The rest of the cells in Column10 (from Row2 to the last row) will still be disabled since the values in their corresponding cells in Column1 were not modified at the moment.

Thanks for your previous postings. You have helped me a lot! If there's anything more that you would like to share regarding the usage of DBGrid, I'll be glad to receive them.

For those who already had experienced in using the dbgrid, your suggestions are also welcome! Thanks!

[vchapran]

With NWind/Customers you can lock cells in a second row this way:

private Sub DBGrid1_BeforeColEdit(byval ColIndex as Integer, byval KeyAscii as Integer, Cancel as Integer)
If DBGrid1.Columns(0).Value = "ANATR" then
If IsNumeric(Chr(KeyAscii)) then
Cancel = true
End If
End If
End Sub



DBGrid has a lot of events which can be used for validation, locking and so on. Play with them.

Vlad