Setting DataGrid Properties 1

[zzfive03]

Hello,

I am using a DataGrid component (from the Microsoft DataGrid Control 6.0)

I can successfully load it with my recordset data. However, I need a way to add some sort of checkbox or "selector" to each row.

The idea is that this datagrid is showing a list of "things to print" but i want the user to be able to de-select certain items, if they feel they do not need these. Is there a way/property on the DataGrid component that will allow me to do this?

Your help is appreicated

Mark

 

[CCLINT]

Set the column's Button property to True and use the Button's click event to toggle.

 

[zzfive03]

Hello CCLint, I was searching the forms and I found where you have suggested this elsewhere.. I like the idea, and I am doing it right now..

however, The only thing i cant seem to do is get it to toggle "on click" how/where exactly do I set this code to toggle? How do I capture the click?

Thank you
Mark

 

[CCLINT]

Assumming the field is a boolean (or integer Where 0=False)
DataGrid1.Columns("MyField&quot.Value = Not DataGrid1.Columns("MyField&quot.Value

 

[CCLINT]

select your DataGrid1 in the left object drop-down list of the code window and the ButtonClick event in the right event drop-down window.

 

[zzfive03]

Ok.. i see how it works..

BTW.. when I do this, i get an error messsage that the columne is read only? Is there a proerty to set to make this modifyable??

THANKS FOR YOUR HELP

Mark

 

[CCLINT]

Can you edit the field through the recordset or the Adodc1.Recordset with no problem?
Is the field that the column is bound to also in the datasource?

 

[zzfive03]

yes, it is in the datasource. I basicaly did a "select 1 as [whatever], field1, field2... from.."

I did not know how to dynamicly add a brand new colume, so I just added a generic field to my recordset.

Perhaps this is the problem..

Is it easy to just add a new colume to my datagrid that is not bound to the recordset?

 

[zzfive03]

Here is my current code:

strSQL = "select fname, lname, mname from names"
' the sql runs, and returns a recordset (objrs1)

With PrintJobs
.RecordSelectors = True
Set .DataSource = objRS1

' here is where i am trying to add a new column, it works ok, but i cant seem to assign it a value

With DataGrid
.AllowUpdate = True
.Columns.Add
.Columns.Item(0).Caption = "Print?"

.Columns(0).Button = True
.Columns(0).NumberFormat = "yes/no"

.Columns(0).Value = 1 ' THIS DOES NOT WORK
End With

I also have my onbutton click function, that should work ok once I get my top section working:

Private Sub PrintJobs_ButtonClick(ByVal ColIndex As Integer)
PrintJobs.Columns(0).Value = Not PrintJobs.Columns(0).Value
End Sub

 

[CCLINT]

>Perhaps this is the problem

Yes, this is the problem...Add a tinyinteger field to the table.

 

[zzfive03]

ok did it, and i got it working. My problem was I didnt realize that the data being displayed was the acutal LIVE data directly pointing to the database, fully updateable (if properly configured to do so). I am so used to working in an ASP world, where the recordset is disconnected, and required a resubmit to update any rows.

Thanks for your help CCLINT

 

[CCLINT]

You can also disconnect this recordset that the grid is bound to...

 

[zzfive03]

Weird Error..

OK, now THIS is confusing.. I make a recordset that has 30 results, and i feed it into my datagrid, I then am trying to loop thru my datagrid, and it is skipping rows. I put a debug print in the loop, and even though on the form it shows the data, the debug skips records, and ends up erroring, because there are less rows thought I had, and i get a Ubound error.

Also, I know the data is not changing on the database. Acutaly, I am only using this datagrid for view purpouses, I wonder if disconnecting from the database would help? If so, how do i do that? Thanks

Set objConn = New ADODB.Connection
objConn.ConnectionString = "blah blah"
objConn.Open
Dim objRS1 As ADODB.Recordset
Set objRS1 = New ADODB.Recordset

With objRS1
.LockType = adLockBatchOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open strSQL, objConn
End With

objRS1.MoveLast
TotalJobsToPrint = objRS1.RecordCount
objRS1.MoveFirst

With PrintJobs
.RecordSelectors = True
Set .DataSource = objRS1
.Columns(0).Button = True
.Columns(0).NumberFormat = "yes/no"

TotalJobsToPrint = TotalJobsToPrint - 1
For i = 0 To TotalJobsToPrint
.Row = i
MsgBox i & ": " & .Columns(1).Value
' this loop never finishes, my totaljobs are 30 but it errors out at row 24!!!
Next
.Refresh
End With

 

[CCLINT]

Because of this:

.Row = i

A DataGrid row is only the first visible row to the end.
If you are on record 25 and that is the first visible row in the grid, then that row is now row 0. If you have 30 records and try to set the row to 30, it will try to move 30 rows past the first visible row...which is row 55 - and this row doesn't exist.

So, do not rely on this property to give you what you would normally expect. If you are on the first record of 300 records, then .Row = 300 - 1 will move to the last one.
If you are on record #100, the .Row = 200 - 1 will move you to the last record.
There is another purpose for this, but it isn't to loop through all of the records...

Use the objRS1 object to loop through the records:
With objRS1
.MoveFirst
Do Until .EOF
MsgBox i & ": " & .Collect(1)
.MoveNext
Loop
End With

The dataGrid is bound to the Rs, so, as you loop through the records in the Rs, the grid row should follow.

If you do not want this to happen (the grid row changing when looping through the records) the just create a Rs.Clone and use this to loop through the records.

You can also set the Grid's Bookmark to the Bookmark of any record in a recordset (even a cloned recordset's BookMark, if you do not change the record positions from the original recordset by using Sort/Filter), and the grid should also re-position to that record.

 

[zzfive03]

Thank you.

Thats weird (new to me) that the row would work that way, But I am new to using the DataGrid so theres still a lot to learn.

I think I have my program working just fine now using your suggestion.

THank you for all your help.

Mark

 

[CCLINT]

>Thats weird

Well, as far as the Row Method is concerned, this is very similar to a recordset's Move n Method (actually, there isn't an official Row method, but I consider it a Method instead of a Let property).
So, maybe it should have been call "Move" instead, making it something seperate from the Row property.

As far as the Row Property: Like I said, there are reasons to want to know the visible row we are on. There is also a VisibleRows property. Maybe the "Row" property should have been called the "VisibleRow" property, or something like that.