DBGrid Check Box as Column

The DBGrid that ships with VB does not support a 'checkbox' column. If this is what you are after you may have to find a third party control. You could dynamically load and position checkbox controls within the appropriate cells but that becomes a bit of a nightmare to coordinate. What I have done in the past is set the field and DBgrid column to a 'Yes/No' value. With the 'button' option checked for the grid column, the value is changed back and forth. This is the setup for the grid:

Right click the grid -->"Retrieve Fields"
Right click again -->"Properties"
  -'Columns' (tab)--> select the column that displays the Yes/No--> 'NumberFormat' dropdown, select the display for the column.
  -'Layout' (tab)-->select your yes/no column-->make sure 'locked' and 'button' are checked

Add this code. You will have to change the column # in code to suit:

Private Sub DBGrid1_BeforeUpdate(Cancel As Integer)

With DBGrid1.Columns(1) '//change column # to suit
  If .Value = "Yes" Then
    .Value = -1
  Else
    .Value = 0
  End If
End With

End Sub

Private Sub DBGrid1_ButtonClick(ByVal ColIndex As Integer)
If ColIndex = 1 Then
With DBGrid1.Columns(1)
  If .Value = "Yes" Then
    .Value = "No"
  Else
    .Value = "Yes"
  End If
End With
End If
End Sub

Private Sub DBGrid1_OnAddNew()
'//set the default value on new record
DBGrid1.Columns(1).Value = "No"
End Sub

JGV, thanks.  Need just one more bit of information.

Private Sub DBGrid1_ButtonClick(ByVal ColIndex As Integer)
If ColIndex = 1 Then
With DBGrid1.Columns(1)
  If .Value = "Yes" Then
    .Value = "No"
  // this causes the value to change each time you click right?

To loop through the rest of the columns...should I use a while statement in each of these functions?
(give example if possible).  Thanks,
I've increased the points because I normally give 100 for any answer requiring code.

Also, I'm getting an error, "Invalid or unqualified reference".. Invalid Data Type.

Private Sub DBGrid1_BeforeUpdate(Cancel As Integer)
***    If .Value = "Yes" Then
        .Value = -1
    Else
        .Value = 0
    End If

Then the routine jumps here.

Kathy

jgv.. are you still there.  I need just a little more help.

Sorry, was away for xmas.

When you are defining the database you are using in your app, you have to set the approriate field's datatype to "Yes/No" or "Boolean". In VB, at design time, you have to set the appropriate columns within the DBGrid to "yes/no". If you have missed one of these things you will get the data conversion error.

One more thing.  I believe that after the focus shifts from one record in the DBGrid to the second record in the DBGrid, the value shows as -1.  Is this correctable?  I tried to do an After Update, change to "Yes", but that didn't work.

:)  Thanks again.

If you set the 'NumberFormat' for the column to 'Yes/No' then the DBGrid will display Yes or No in place of 0 or -1. Right click the grid and select properties. Under the 'Columns' tab, select the column and make sure that the 'NumberFormat' is set to 'Yes/No'.

Very helpful, and excellent programmer.

glad i could help :)

As you could tell I wasn't able to get the answer I needed in that other question.

So as you pointed out when you asked how I was referencing the second grid frm the combo or first grid....  I am going to have to post a new question to figure out how to filter the second grid based on a row/column in the first grid.  So...hopefully you'll be around tomorrow.

Hi Kathy,

If I understand correctly, you have two dbgrids which are tied to two different recordsets (Data controls). The first dbgrid contains summerized records and the second dbgrid contains the details for *every* item in the first grid. When you click a row in the first dbgrid, you want to find and show the corresponding record in the second dbgrid.

Here's one way to do this. You obviously have a field in the first recordset that you use to search and find the corresponding record in the second recordset. You can use the RowColChange Event in the first grid to determine when a new row is selected. The column that contains the search criteria is used to find the matching record in the second recordset (using the 'Findfirst'). The record pointer in the second grid will then jump to that record.

If the second dbgrid's purpose is to show the details only for selected record then I would suggest that you use bound text boxes instead. It seems a little messy to use a grid control to show only one record. You would use a data control to hold all the records that correspond with the first dbgrid. When you do a 'findfirst' with this data control and the record is located, all the text boxes that are bound to it are updated. Easier and cleaner :)

Paste the following in the "RowColChange" event for the first dbgrid (I have recently upgraded to VB6 and the dbgrid has become a datagrid. You will have to change the names):

Dim search_critera As String

If LastRow > 0 Then
    '//assumes that column 1 contains the field information
    '//used to search the second dbgrid
    search_critera = DataGrid1.Columns(1).Value
    '//find the matching record in the second grid
    Data2.Recordset.FindFirst "<your_field>='" & search_critera & "'"
End If

jgv:

Sorry to bother you again, one problem came up with this code you gave me..

If the user presses the keys to type Yes or Y or anything, it looks OK until you move to the next record.  Then the KeyPress function takes place and it is changed back to a no.

So in the orininal grid, everything works fine as long as you press the button to change Yes/No.  But if you use the keyboard to type the correct answer... the answer is switched when moving to the next record.

Does this make since?

jgv:
I've posted a new question number 10266463 under general VB if you have time to look at it.  I thought it might be easier for you since you helped me to solve the original question.

In a case where you have a column that displays a boolean value you should only allow the value to be changed with the button. If you allow the user to type in a value they could misspell it (or mess it up in general) which would create a problem. Lock the column so there can be no manual changes, only the button click. The locked/unlocked option is either under the 'Column' tab or the 'Layout' tab in the grid's property sheet.

Thanks jgv:

Unfortunately, I couldn't figure it out and had to rebuild the page.


Sincerely,

Kathy

Just read your other question. In your keypress event make sure you are setting the value of the yes/no column to "Yes", not "yes". The check made in the _BeforeUpdate event is case sensitive.