Add command button to datagrid 2

[brownfox1]

My DataGrid is based on a jet sql query as a recordset, but I would like to add a button to the grid to edit a particular record. The sql query is a hugh join of tables so I don't want the datagrid to be edited directly. I hope I have asked this question in a clear way (P.S visual basic 6.0). Thanks in advance!

 

[BobRodes]

The way I do it is to have an Edit button. Just one, I mean, and not on the datagrid. If the user clicks the Edit button, I take the current record and put its fields into a different form. I then show that form modally. The form has two buttons, save and cancel. I let the user edit, save his changes, and refresh the datagrid.

HTH

Bob

 

[SBerthold]

Or set one of the column's .Button property to TRUE (which then shows a arrow button when the column is clicked on) and then capture the ButtonClick event to bring up a dialog.

 

[BobRodes]

Mine is stodgier though.

 

[SBerthold]

>stodgier

What dat mean? (I cannot find it in my english translation book)

 

[BobRodes]

Googling to "definition stodgy" yields the following:

stodg·y(stj)
adj. stodg·i·er, stodg·i·est
1.
a. Dull, unimaginative, and commonplace.
b. Prim or pompous; stuffy: "Why is the middle-class so stodgyso utterly without a sense of humor!" Katherine Mansfield. See Synonyms at dull.
2. Indigestible and starchy; heavy: stodgy food.
3. Solidly built; stocky.

--------------------------------------------------------------------------------

[From stodge, thick filling food, from stodge, to cram.]

--------------------------------------------------------------------------------

stodgi·ly adv.
stodgi·ness n.

 

[brownfox1]

Thanks for your help guys. I've added a general edit button, which when pressed should take the user to a form that shows the particular record "modally" so they can edit. But what's the code behind the edit button. I'm messing around with this

Private Sub cmdEditRow_Click()
MsgBox (DataGrid1.Bookmark)
End Sub

 

[BobRodes]

On your form, have a set of text boxes and labels that correspond to the fields in the record. When the user clicks the Edit button, first populate these text boxes with the members of the record, then show the form modally. When the user hits a Save button on the form, take the values in the text boxes and save them to the database. Then close the form, and refresh the data grid.

There's a bit more cosmetic stuff, but that's the gist of it.

HTH

Bob

 

[brownfox1]

Thanks Bob, I get that bit. The code I need is that when a row in the grid is selected and the user hits the edit button, the Primary key value of the record in the DataGrid is assigned to a variable that can be used in a sql query to populate the form so that the record can be shown modally. I don't want the ID (Primary key) shown in the datagrid, I guess I can hide the column in the grid by giving it width = 0. Is that how it's generally done? Or is the bookmark property used to hold the ID field?

 

[BobRodes]

I don't know DataGrids that well. Perhaps SBertholdcan help? He seems to know them a lot better than I do.

Bob

 

[SBerthold]

>I don't want the ID (Primary key) shown in the datagrid, I >guess I can hide the column in the grid by giving it width = 0.
>Is that how it's generally done?

Do not set the Width to 0 if you do not want the user to have the capability of showing it again. Instead, use:

DataGrid1.Column.Visible = False



>Or is the bookmark property used to hold the ID field?

Depends on how you are doing this (passing the data to the sub form).
There are several ways, and I am assumming, or I guess I see that you are opening another recordset object in the sub form.

In the following suggested methods, it is best to use a client side cursor and a disconnected recordset.

You could also just pass the main form's recordset object to the sub form, or even using a cloned recordset object (pass myRS.Clone) if preferred (as long as Bookmarks are supported - this is more effecient than opening another recordset object). The Bookmarks used will be the same for the original and cloned, and an Update affects both the same, unless the Requery method of the original or cloned recordset is called.

I think less overhead, and less chance of mistakes, than the Clone method, would be the often overlook possibility of just passing the Fields collection as a single record:

'Setting the Sub form's "record" property from the Parent Form:
Set MySubForm.myRecord = myRS.Fields

to an object variable in the sub form, as in:

'Sub Form
Option Explicit
Private mMyRecord As ADODB.Fields
Public Property Set myRecord(vRecord As ADODB.Fields)
Set mMyRecord = vRecord
End Property

And then access the record's field as in:

mMyRecord.Item("SomeField").Value

In the sub form you can then use an INSERT Statement or Stored Proceedure action off of the Connection\Command Object to update the db table, and when the sub form is closing, pass a boolean back to the parent form to see if the local copy recordset needs an Update or BatchUpdate.
If using an INSERT Statement or Stored Proceedure to update with, then the parent form's recordset should be disconnected and just the Update method called (No Batch Updating needed then).

You just cannot use AddNew with the Fields collection in this sub form, or move to another record, without having the actual recordset object available.

 

[BobRodes]

>Set MySubForm.myRecord = myRS.Fields

I never thought of doing that! Great idea. Nice post overall, too; have a star.

Bob

 

[SBerthold]

Thank you Bob!