[RESOLVED] Process data from bookmarked row of datagrid to text box

[Bobbles]

Also 98% of pro's don't use the Data Environment.

Rob
PS It is very brave of me to mention that, as one member who loves them, gave me a negative rating for saying something similar once. (Hope he feels very guilty)

[Elroy]

98% of pro's don't use the Data Environment.

Hi Bobbles. I'll agree with you. Early on, I explored the Data Environment, but I haven't even messed with it in many years. Now, it's just an irritant to me because you have to install the DAO350 (which isn't even the latest version of the DAO) to get the VB6 IDE installed on a computer. It's about the only part of the IDE that I never use, other than most of the canned Add-Ins, and the canned module templates. And those "canned" pieces can be easily deleted with no harm done.

Best Regards,
Elroy

[dilettante]

Also 98% of pro's don't use the Data Environment.

That's crazy talk. If anything you have it backwards! Most of the casual plinkers still here have "learned" VB from copy/pasting creaky old VBScript snippets from ASP scripts. There was no way to pass the MCPD/MCSD exams without understanding how to use the DataEnvironment.


I think the problem with this stems from the way the DataGrid seizes control over its DataSource. When this is an ADO Recordset or a DataMember of a DataEnvironment the AbsolutePosition will always be changed back to 1.

As a result if you try to bind TextBox or other controls to the same source they can never move off the first row.

Bound TextBox controls are "stuck" on the first row

I attached a demo that creates a small Jet MDB on first run.


The only real way around this is to write event handlers for anything that binds new values or changes the row selection of the bottom-level DataGrid in the hierarchy. From there you can try to fish out the ID value for the selected row and use that for another query.

For example you could have a 3rd Command defined to accept this ID as a parameter. You'd have to close the Command's Recordset if open, then re-execute the Command passing this ID. Then you would have to bind each control to that Command's Recordset. Kind of ugly.

Plus you'd have to examine the EditMode value and conditionally call Update as each bound control loses focus. Also the DataGrid "one level up" would not be updated to reflect the change so you have another can of worms to deal with.


No, this just isn't a well conceived idea as far as I can determine. It may be a flaw in the way data binding was changed during the move to ADO, it may be a bug, or it may be intended behavior.

You are probably better off allowing the user to make edits within the DataGrid. Or rip out the DataGrids and replace them with MSHFlexGrids if they are just being used as row selection controls.

If there is a way to make what you ask for work I sure don't remember it. I can't find anything of the sort in the manuals or by trial and error for that matter either.


Don't expect a lot of help here. Most "professionals" have either moved on from VB to .Net or something else by now.

[Schmidt]

Also 98% of pro's don't use the Data Environment.

That's crazy talk...

In my opinion, the statement is fairly accurate.

And as you've just encountered yourself (by not being able to make the TextBox-Bindings work in conjunction with the Docs-DataGrid) -
there's apparently a reason, why real professionals stay away from the DataEnvironment (and the ADODC-Control as well).

Not to be misunderstood, I think ADO- (or any form of) DataBinding is a nice thing, which can significantly
reduce the amount of code in a (DB-)Project, allowing for quite generic, dynamically generated DataEntry-Forms.

With only a few small Helper-Functions (in a *.bas), one can completely avoid the DE or ADODCs,
and still use (ADO-)DataBinding, allowing the Data to remain "outside" (in Recordset- or other non-visual Containers) -
and not copied "By-Value" into visual COMponents or Controls.

Below is your modified example, which works with such a "handmade" Binding-approach
(including Record-Editing over the Text-Fields, and persistence of the made changes back into the DB).

The Form-Code for that is not really large:

Private RsFolders As Recordset, RsDocs As Recordset
 
Private Sub Form_Load()
  Set RsFolders = BindDataSourceReadOnlyTo(DGFolders, GetRs("Select * From Folders"))
  DGFolders_RowColChange Empty, 0 'initiate the full-row-selection of the first Folders-record on DGFolders
End Sub
 
Private Sub DGFolders_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  EnsureFullRowSelectOn DGFolders 'needed in the RowColChange-Event of any DataGrid which needs a FullRowSelect-Visualizing
  
  'any record-change in DGFolders needs to cascade down the Hierarchy (so let's select a new RsDocs appropriately)
  CheckForDocChanges 'before switching to new Docs, make sure (potential) former changes are saved
  Set RsDocs = BindDataSourceReadOnlyTo(DGDocs, GetRs("Select * From Documents Where FolderID=?", RsFolders!FolderID))
  Dim i As Long '(re)bind the new RsDocs-DataSource also to the TextFields (which are editable)
  For i = 0 To txtField.UBound: Set txtField(i).DataSource = RsDocs: Next
End Sub
 
Private Sub DGDocs_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  EnsureFullRowSelectOn DGDocs 'needed in the RowColChange-Event of any DataGrid which needs a FullRowSelect-Visualizing
End Sub

Private Sub CheckForDocChanges()
  If Not RsContainsChanges(RsDocs) Then Exit Sub
  If MsgBox("Save Changes on current Documents?", vbYesNo) = vbYes Then RsDocs.UpdateBatch
End Sub
  
Private Sub Form_Unload(Cancel As Integer)
  ValidateControls
  CheckForDocChanges
End Sub

Producing this:


Here is the Demo-Zip for the approach: DataGridsMasterDetail.zip

HTH

Olaf

[ptrifonov]

In my opinion, the statement is fairly accurate.

And as you've just encountered yourself (by not being able to make the TextBox-Bindings work in conjunction with the Docs-DataGrid) -
there's apparently a reason, why real professionals stay away from the DataEnvironment (and the ADODC-Control as well).

Not to be misunderstood, I think ADO- (or any form of) DataBinding is a nice thing, which can significantly
reduce the amount of code in a (DB-)Project, allowing for quite generic, dynamically generated DataEntry-Forms.

With only a few small Helper-Functions (in a *.bas), one can completely avoid the DE or ADODCs,
and still use (ADO-)DataBinding, allowing the Data to remain "outside" (in Recordset- or other non-visual Containers) -
and not copied "By-Value" into visual COMponents or Controls.

Below is your modified example, which works with such a "handmade" Binding-approach
(including Record-Editing over the Text-Fields, and persistence of the made changes back into the DB).

The Form-Code for that is not really large:

Private RsFolders As Recordset, RsDocs As Recordset
 
Private Sub Form_Load()
  Set RsFolders = BindDataSourceReadOnlyTo(DGFolders, GetRs("Select * From Folders"))
  DGFolders_RowColChange Empty, 0 'initiate the full-row-selection of the first Folders-record on DGFolders
End Sub
 
Private Sub DGFolders_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  EnsureFullRowSelectOn DGFolders 'needed in the RowColChange-Event of any DataGrid which needs a FullRowSelect-Visualizing
  
  'any record-change in DGFolders needs to cascade down the Hierarchy (so let's select a new RsDocs appropriately)
  CheckForDocChanges 'before switching to new Docs, make sure (potential) former changes are saved
  Set RsDocs = BindDataSourceReadOnlyTo(DGDocs, GetRs("Select * From Documents Where FolderID=?", RsFolders!FolderID))
  Dim i As Long '(re)bind the new RsDocs-DataSource also to the TextFields (which are editable)
  For i = 0 To txtField.UBound: Set txtField(i).DataSource = RsDocs: Next
End Sub
 
Private Sub DGDocs_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
  EnsureFullRowSelectOn DGDocs 'needed in the RowColChange-Event of any DataGrid which needs a FullRowSelect-Visualizing
End Sub

Private Sub CheckForDocChanges()
  If Not RsContainsChanges(RsDocs) Then Exit Sub
  If MsgBox("Save Changes on current Documents?", vbYesNo) = vbYes Then RsDocs.UpdateBatch
End Sub
  
Private Sub Form_Unload(Cancel As Integer)
  ValidateControls
  CheckForDocChanges
End Sub

Producing this:


Here is the Demo-Zip for the approach: DataGridsMasterDetail.zip

HTH

Olaf

This piece of code do the job. Thank you Olaf, thank you all for helping me to resolve this.
I've learned a lot and I am very happy that you all shared your advises with me.

Best regards,

Plamen