Data grid column format 1

[cenderawasih]

HI.....

I have a data grid to display all the data in the Access database where the user can edit these data. One of the fields in the Access database is in the Yes/No format, meaning it contains a checkbox.

My problem is...when the data from the database are displayed in the data grid the column that is suppose to have the checkbox is displayed as "0" for the unticked checkbox and "1" for the "ticked" checkbox....

What i need is for the column to display the checkbox so that the user can tick on the data they desired.....

1. can i somehow...set the format for that particular column of the data grid so that it can display the checkbox?

2.can i locked only those columns that cannot be edited by the user?

please help

 

[CCLINT]

Lock columns:

DataGrid1.Columns("theColumn&quot.Locked = True

Display Yes/No instead of 0/1:

DataGrid1.Columns("theColumn&quot.NumberFormat="Yes/No"

Add a button to toggle between Yes/No:
thread222-610750

 

[cenderawasih]

Thanks CCLINT for the valuable information....but i need your help on one more thing...

Where can i Set the column's Button property?

i cant find it in the datagrid property.....

please help

 

[cenderawasih]

Ok i try to set the column button property like this:

FrmCodeControl.DataGrid1.Columns(2).Button = True

it works but when i click on the button...the Yes/No value did not refresh immediately....the Yes/No value will only be change when i click on another record but then this error msg appears:

Multiple-step operation generated errors.Check each status value.....


can u help me?

 

[cenderawasih]

Now i've tried to change the datatype for the checkbox field to Number, Field size:integer.....but then this error msg appear:

-> Type missmatch

 

[cenderawasih]

Somebody has any idea on this one???????

 

[CCLINT]

This is probably because the field has no value in it, but is instead NULL.
You will need to check the recordset field value first.
The below example uses a field named "BoolFieldName" and has a field type of 17-adTinyInt, known as a Boolean field.

Option Explicit
Private Const BoolFieldName$ = "TheBoolFieldName"

'Use some proceedure to load the grid and set it's properties, such as the Form_Load
Private Sub LoadGridData()
    'Set DataGrid1.DataSource = rsADO
    DataGrid1.Columns(BoolFieldName).Button = True
    DataGrid1.Columns(BoolFieldName).NumberFormat = "Yes/No"
End Sub

[blue]

Private Sub DataGrid1_ButtonClick(ByVal ColIndex As Integer)
    If StrComp(DataGrid1.Columns(ColIndex).DataField, BoolFieldName, vbTextCompare) = 0 Then
        If IsNull(rsADO.Collect(DataGrid1.Columns(ColIndex).DataField)) Then
            DataGrid1.Columns(ColIndex).Value = True
        Else
            DataGrid1.Columns(ColIndex).Value = (Not CBool(DataGrid1.Columns(ColIndex).Value))
        End If
    End If
End Sub

[black]
Or use a standard data formating object to catch the NULL before the grid is filled with data, and if there is a Null, set the value automatically to False.

 

[cenderawasih]

Hello CCLINT....

Thanks for the procedure... but i have an error on this part:

If IsNull(rsADO.Collect(DataGrid1.Columns(ColIndex).DataField)) Then
DataGrid1.Columns(ColIndex).Value = True

It hightlighted on the : Collect
with error message:

Method or data member not found....

I tried to select the reference for VB but still to no avail.....

the VB doesn't seem to recognized the 'collect' function.

please help.......

 

[CCLINT]

What version of ADO are you using?
Make sure you are using at least ADO 2.5. Check your references.
The Collect method can only be used as you would the Value property of the Field object..
You can also just use:

rsADO.Fields(DataGrid1.Columns(ColIndex).DataField)).Value

 

[cenderawasih]

I'm using Ms.ADO Data Control 6.0 OLEDB and the reference is Ms.ADO Ext.2.5 for DDL & Security......

The system still cannot recognized the 'collect' and the 'fields' method......

 

[CCLINT]

>and the reference is Ms.ADO Ext.2.5 for DDL & Security

No, that is not the reference for ADODB.
Look for a reference to

"Microsoft ActiveX Data Objects Library"
It will also list the version and you should be using vers 2.5 or higher.

rsADO should be used as:
Dim rsADO As ADODB.Recordset
and then opened.

BUT, if you are using the ADODC you need to replace
rsADO
with
Adodc1.Recordset
or what ever the name of your adodc is.

 

[cenderawasih]

Now the problem turns back to probem no.1.... the yes/no value would not change until i click on another record but then this msg appear:

Multiple-step operation generated errors.Check each status value

 

[CCLINT]

Then read through this thread again.
Make sure you are doing every mentioned.
The error is probably because you are using a wrong field type, or setting a wrong value type for the field.

 

[cenderawasih]

i'm using Access 2000 and not access 97 as the database?, can this be the factor of the error i'm getting....?

because i really followed the codes u've given me...the only difference might be the data type of that particular field in my database...The database data type for that field is Yes/No.

I'm really stuck on this one....

 

[CCLINT]

>i'm using Access 2000 and not access 97 as the database?, can this be the factor of the error i'm getting....?

No.

Post the code you are using to set the column properties for the Yes/No column, and the code from the ButtonClick event.

 

[cenderawasih]

The code:

Option Explicit
Private Const boolfieldName$ = "HIDECODE"

Private Sub btnOK_Click()
Dim conn As String
Dim rec As String
Dim str As String
Dim Cat_Desc As String

Cat_Desc = FrmCodeControl.DBcboItem.Text
str = Cat_Desc
conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPATH & "\" & str & ".MDB;Mode=ReadWrite;Persist Security Info=False"
rec = "SELECT ITEM,DESCRIPTION,HIDECODE FROM ALLITEM ORDER BY ITEM"

AdodcItemDesc.ConnectionString = conn
AdodcItemDesc.CommandTimeout = 60
AdodcItemDesc.RecordSource = rec
AdodcItemDesc.Refresh

FrmCodeControl.DataGrid1.Columns(0).Width = 1500
FrmCodeControl.DataGrid1.Columns(1).Width = 7000
FrmCodeControl.DataGrid1.Columns(boolfieldName).Width = 1000
FrmCodeControl.DataGrid1.Columns(0).Locked = True
FrmCodeControl.DataGrid1.Columns(1).Locked = True
FrmCodeControl.DataGrid1.Columns(boolfieldName).Button = True
FrmCodeControl.DataGrid1.Columns(boolfieldName).NumberFormat = "Yes/No"

End Sub


Private Sub DataGrid1_ButtonClick(ByVal ColIndex As Integer)

If StrComp(DataGrid1.Columns(ColIndex).DataField, boolfieldName, vbTextCompare) = 0 Then
If IsNull(AdodcItemDesc.Recordset(DataGrid1.Columns(ColIndex).DataField)) Then
DataGrid1.Columns(ColIndex).Value = True
Else
DataGrid1.Columns(ColIndex).Value = (Not CBool(DataGrid1.Columns(ColIndex).Value))
End If
End If

End Sub


***The data type for HIDECODE field in the ALLITEM table is Yes/No......the error msg is:
"Multiple-step operation generated errors.Check each status value"

When I change the data type to Text,Memo &
Integers it gives error msg :"datatype missmatch"

 

[CCLINT]

Make sure you are using VB6 Serveice Pack 5, and ADO 2.5+