DBGrid Check Box as Column

I have a DBGrid and basically need the column to be a checkbox.  Is this possible, if so how?

For example

Name   Arch  CivEng  ElecEng
Joe      x      x       _    


KathyBrowningAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jgvCommented:
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

0
KathyBrowningAuthor Commented:
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.
0
KathyBrowningAuthor Commented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

KathyBrowningAuthor Commented:
jgv.. are you still there.  I need just a little more help.
0
jgvCommented:
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.

0
jgvCommented:
Made a couple of changes to the code. I assumed that you have alot of columns that are boolean (yes/no). Setting up an array that stores the column numbers (you have to know them ahead of time) will allow you to loop through and set the appropriate value to the column before updating the data base.

Dim yesno(1 To 3) As Integer

Private Sub Form_Load()
'//store column numbers that are defined as 'yes/no'
'//in the dbgrid
yesno(1) = 1
yesno(2) = 3
yesno(3) = 6

End Sub

Private Sub DBGrid1_BeforeUpdate(Cancel As Integer)
Dim x

'//loop through the columns that are 'yes/no' and change
'//to a numeric value in order to save to database
For x = 1 To 3
  With DBGrid1.Columns(yesno(x))
    If .Value = "Yes" Then
        .Value = -1
    Else
        .Value = 0
    End If
  End With
Next

End Sub

Private Sub DBGrid1_ButtonClick(ByVal ColIndex As Integer)

'//if you only assign a button to columns that are 'yes/no'
'//then the following code is all you need

With DBGrid1.Columns(ColIndex)
  If .Value = "Yes" Then
    .Value = "No"
  Else
    .Value = "Yes"
  End If
End With

End Sub

Private Sub DBGrid1_OnAddNew()
Dim x

'//set the default value on new record
For x = 1 To 3
  DBGrid1.Columns(yesno(x)).Value = "No"
Next

End Sub

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KathyBrowningAuthor Commented:
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.
0
jgvCommented:
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'.
0
KathyBrowningAuthor Commented:
Very helpful, and excellent programmer.
0
jgvCommented:
glad i could help :)
0
KathyBrowningAuthor Commented:
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.

0
jgvCommented:
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
0
KathyBrowningAuthor Commented:
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?
0
KathyBrowningAuthor Commented:
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.
0
jgvCommented:
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.
0
KathyBrowningAuthor Commented:
Thanks jgv:

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


Sincerely,

Kathy
0
jgvCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.