unbound checkboxes in a bound grid?

[TomLeMes]

I've seen quite a lot of discussion regarding checkboxes in grids, but haven't really found the answer to my problem. I have a grid with record source set to look at a cursor created using SQLEXEC (pointing to SQL database). Normally that's fine, except that for my new grid I want to include a column of checkboxes to allow users to select multiple records.

I have managed stick some code into my column class so that the desired column contains checkboxes (it takes out the textbox too). However, these checkboxes are all stuck on the value I set them up with on creation (either all on or all off). Seems the problem is related to the fact that because the grid is bound to a recordsource, my checkboxes are kind of stuck? Is there a way to have this one column 'unbound' (and so free for users to click) whilst the rest of the columns are bound as normal?

I've tried setting

mycol.controlsource = ''

but that doesn't work. I've also tried copying the data into an array, then adding a further (logical) column, but that whole process seems pretty long-winded, as I would then have to copy the array elements back into a new cursor. Is there a better way?

 

[JimWWinter]

First of all, a CheckBox value is either numeric or logical - not character.

In your SQLEXEC field list you can include something like

SELECT 0 as nChoice, ;

to get a numeric field in your cursor that you can use for your CheckBox.

Regards,
Jim

 

[TomLeMes]

Thanks Jim,

The [red]Select 0 as nChoice[/red] works a treat, in that it allows me to bind my checkboxes, however they still don't seem to be responding to my clicks. I can set them programmatically (say on the click of a button that I've put on the form for debug purposes) but ideally I'd like the checkboxes to toggle on and off when clicked! Any ideas?

BTW, I wasn't actually trying to set the value of the checkbox, rather trying to clear the controlsource of the column. Either way it wasn't really working was it?!

Tom

 

[ChrisCarroll]

A couple of thoughts:

1) Check that the cursor you've got as your recordsource is read/write eg browse it and see you can alter the nChoice column

2) You don't want to clear the controlsource of the checkbox column at all. If you do that, all rows of that column will be set/unset together. You definitely want it bound to the nChoice column

3) So go back to having mycol.controlsource set in the property sheet as aliasname.nChoice

--
(It is possible to add a checkbox to a column in the form designer, and to delete the textbox as well. Deleting the textbox involves pressing the Delete key at just the right moment...)

Chris

 

[JimWWinter]

Tom,

Not really sure as I normally use a logical value and it works fine for me. But I don't know how to get a logical value from a SQLEXEC.

One thing that might affect it - is your columns Sparse property set to .F.?

Good luck,
Jim

 

[TomLeMes]

Hi Chris,

1) When I browse the cursor it does let me change the nChoice column. However, it allows any numeric value, so looks like the [red]Select 0 as nChoice[/red] has created a numeric field, rather than a logical one. Would that be a problem?

2) Agreed. That whole clearing the controlsource thing was from earlier when I was trying to create an unbound column. The column is now bound to the nChoice field.

3) This is currently the case. However my clicks are still not changing the checkbox values.

Jim: hmmmm I expect there must be a way, equivalent to your [red]Select 0 as nChoice[/red] that creates a logical value - I'll do some experimentation. Thanks both for your input,

Tom

 

[TomLeMes]

I've succeeded in creating a logical field using the following in my SQLEXEC select string:

cast (0 as bit) as nChoice

(this then shows in my VFP cursor as T or F and when browsing the cursor I can edit the values). However, that still doesn't fix my issue with the fact that the checkboxes won't click!

Not sure if this has any bearing on things, but when i run the SQLEXEC query directly in MS SQL (enterprise manager), trying to edit the nChoice field results in the message: Cannot Edit this Cell

 

[JimWWinter]

Tom,

Shouldn't have any effect on your grid. The Cannot Edit this Cell message is in SQL Server and your grid is in FoxPro.

BTW, good catch on creating the logical field!

Regards,
Jim

 

[ChrisCarroll]

(aside: The 'Can't edit' message in MS SQL is because from SQL Servers's point of view it the column doesn't correspond to any data in the database.
This has no bearing on the Foxpro situation, where FoxPro has created a cursor in which nChoice is now editable data.)

I have just created a form from scratch to do what I understand you need, with a recordsource based on:

SQLEXEC(1,"Select CAST(0 as bit) as nChoice, * from Status","Statuses")

and it works perfectly, so I am confident that what has been said so far is right.

Further thoughts then on the grid:
1) Do you have RecordSourceType=Alias rather than anything else?
2) Are you triple sure that nothing is set to read-only or disabled?
3) Is it an option to recreate the grid from scratch?
4) Is it possible that in the course of adding and deleting controls the controlsource is lost? Reset the column controlsource after doing that bit?
4 1/2) Explicitly set the new controls ControlSource after adding it rather than assuming it has inherited from the column?
5) When you try to click on the checkbox, do you get a little message in the status bar saying that the control isn't editable?

 

[TomLeMes]

Chris and Jim,

Thanks to both of you for your help with this - it seems that the problem was related to the programmatic removal of the textbox control and programmatic addition of the checkbox to the column (your point 4 perhaps Chris?).

As you suggested sometime earlier, adding the controls manually (at design time) fixed the problem. Sorry, I guess I was being a bit stubborn, wanting to do it as part of the custom class behaviour, but then realised that there was nothing gained by doing it that way.

Anyway, the long and the short of it is that it works fine now. Thanks again,

Tom

 

[ChrisCarroll]

Well, it's worth making some effort to get code to work.
Cheers,
Chris