Populating Datagrid (97)

Topic

New Reply

We have placed a microsoft datagrid version 6 (OLEDB) on an access form, but are unable to populate it with a recordset. There is no Datasource, recordset or database property to set.

Can anybody help me?

Reply | Quote

Replies

It will be a lot of work to get this to work in Access 97, if it is possible at all (I’ve never tried it). DAO is the data object model used in Access 97, but the Data Grid expects an ADO data source. The Data Grid is not a native Access control, but an ActiveX control. AFAIK, the only way to control ActiveX controls is in code.

Why do you want the data grid in Access 97?

Reply | Quote

We are using ADO. We need the control because users want to select contents and paste in excel. I know we can write a button to do this but the users want the select. Can you help

Reply | Quote

Can anybody help with the code?
We currently have three developers, including myself and we can not work this one out.

Reply | Quote

Do you have at least ADO 2.5 installed on the machine? The DataGrid is an ADO object.and you set its DataSource property in code using an ADO recordset. Here’s some code from an A2k demo that populates a DataGrid:

This is code to populate the grid (DG0 is the control name):

  Dim cnn As ADODB.Connection
  Dim rstSource As ADODB.Recordset
  Dim rstDest As ADODB.Recordset
  Dim strSrc As String
  
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
    strSrc = "select * from customers"
    Set rstSource = cnn.Execute(strSrc)
    Set rstDest = MakeRst(rstSource)
    OpenAndFillRst rstSource, rstDest
    rstSource.Close
    cnn.Close
    Set DG0.DataSource = rstDest 

Here are the two functions it calls:

Function MakeRst(ByVal rstSource As ADODB.Recordset) As ADODB.Recordset
  Dim rstTemp As ADODB.Recordset
  Dim fld As ADODB.Field
    Set rstTemp = New ADODB.Recordset
    For Each fld In rstSource.Fields
      If fld.Type  adChapter Then
        rstTemp.Fields.Append fld.Name, fld.Type, fld.DefinedSize,  _
                              fld.Attributes And adFldIsNullable
        With rstTemp(fld.Name)
          .Precision = fld.Precision
          .NumericScale = fld.NumericScale
        End With
      End If
    Next fld
    Set MakeRst = rstTemp
End Function
 
Sub OpenAndFillRst(ByVal rstSource As ADODB.Recordset, _
                    ByVal rstDest As ADODB.Recordset)
  Dim fld As ADODB.Field
    If rstSource.State = adStateClosed Then Exit Sub
    If rstSource.EOF And rstSource.BOF Then Exit Sub
    If rstSource.CursorType  adOpenForwardOnly Then
      If Not rstSource.EOF And Not rstSource.BOF Then
        rstSource.MoveFirst
      End If
    End If
    rstDest.CursorLocation = adUseClient
    rstDest.Open
     Do While Not rstSource.EOF
      rstDest.AddNew
      For Each fld In rstSource.Fields
        If fld.Type  adChapter Then rstDest(fld.Name).Value = fld.Value
      Next fld
      rstDest.Update
      rstSource.MoveNext
    Loop
End Sub

Reply | Quote

I’m using an Access XP data file to provide source data for a number of User Forms in Word XP (in non-mail merge applications). Everything is working fine, but I wanted to add the capability for the user to edit the data from within Word. This particular user could also just edit the data in Access, but I would like to make it as seamless as possible, and doing the editing from within Word would be easiest for the user. And next time I might not have the option of using Access directly.

I thought that I might be able to open the table in an OLE container, like I have opened Word or Excel documents on a form in Access, but I couldn’t even find a control to do that (in Word VBA, anyway). So then I tried the FlexGrid control. It was a new for me, but it sounded promising, especially since the DataGrid control–which I had used before a LONG time ago–wouldn’t work at all because it wasn’t licensed. Anyway, I was able to populate it, but I couldn’t seem to edit the data.

So, moving on again, I dug out my VB6 CD (sitting here waiting to be installed since before I traveled to China last October to bring my second daughter home). Once that was installed, I was able to play around with the DataGrid control. I used your code from this thread, Charlotte, and was able to populate it, and I seem to be able to edit the data within the grid, but I don’t know how to save the edits once I’m done. What I remember from using Access data in VB4 (a long time ago), this was a pretty simple thing to do. But VB4, if I remember correctly, had built in data access tools. Unfortunately, I couldn’t find anything similar in VBA.

So, if someone could point me in the right direction to find documentation on how to do this, or let me know it can’t be done so I can stop trying to figure it out, or direct me to a simpler solution altogether, I’d greatly appreciate it. Many thanks!

–Karyl

Reply | Quote

If the datagrid is being populated from an query against an Access database, then the edits should be going back into Access as soon as the user completes them. Is something else happening? Or are you just wearing your Word hat and assuming you have to take steps to “save”?

Reply | Quote

No, I was expecting the save to happen automatically. If that is the default behavior, then I suspect I did something wrong in the way I’m populating the grid. Below is the code (pretty much just the way you had it). Hope you can spot the problem. I highlighted the changes I remember making. Sorry I can’t remember the trick for keeping the indenting intact.Many thanks!

–Karyl

Function MakeRst(ByVal rstSource As ADODB.Recordset) As ADODB.Recordset
Dim rstTemp As ADODB.Recordset
Dim fld As ADODB.Field
Set rstTemp = New ADODB.Recordset
For Each fld In rstSource.Fields
If fld.Type adChapter Then
rstTemp.Fields.Append fld.Name, fld.Type, fld.DefinedSize, _
fld.Attributes And adFldIsNullable
With rstTemp(fld.Name)
.Precision = fld.Precision
.NumericScale = fld.NumericScale
End With
End If
Next fld
Set MakeRst = rstTemp
End Function

Sub OpenAndFillRst(ByVal rstSource As ADODB.Recordset, _
ByVal rstDest As ADODB.Recordset)
Dim fld As ADODB.Field
If rstSource.State = adStateClosed Then Exit Sub
If rstSource.EOF And rstSource.BOF Then Exit Sub
If rstSource.CursorType adOpenForwardOnly Then
If Not rstSource.EOF And Not rstSource.BOF Then
rstSource.MoveFirst
End If
End If
rstDest.CursorLocation = adUseClient
rstDest.Open
Do While Not rstSource.EOF
rstDest.AddNew
For Each fld In rstSource.Fields
If fld.Type adChapter Then rstDest(fld.Name).Value = fld.Value
Next fld
rstDest.Update
rstSource.MoveNext
Loop
End Sub

Private Sub UserForm_Initialize()
Dim cnn As ADODB.Connection
Dim rstSource As ADODB.Recordset
Dim rstDest As ADODB.Recordset
Dim strSrc As String
Dim strDB As String

strDB = Options.DefaultFilePath(wdUserTemplatesPath) & “DrywallBidItems.mdb”

Set cnn = New ADODB.Connection
cnn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strDB
strSrc = “select * from qryBidItems”
Set rstSource = cnn.Execute(strSrc)
Set rstDest = MakeRst(rstSource)
OpenAndFillRst rstSource, rstDest
rstSource.Close
cnn.Close
Set frmDataGrid.DGO.DataSource = rstDest
End Sub

Reply | Quote

Actually, my brain was asleep when I posted that message. I’m not sure whether you can update a recordset from a datagrid, but there is no direct connection. I simply wasn’t thinking. You’re taking a recordset that could be updateable, rstSource, and using it to create a disconnected hierarchical recordset to populate the data grid. I don’t have AXP installed on this machine so I can’t test it, but I think what you would have to do is open a new, updateable recordset on the original source query and step through the hierarchical recordset behind the grid, testing for changed values and updating the original recordset.

Reply | Quote

OK, I proved to myself that you can update a recordset from a datagrid by updating the source recordset in code based on the field values in the datagrid, but I’m still trying to figure out the mechanics for doing it right! Getting the right field works, but I’m shaky on matching up the records. I can make it update the first record, but after that it falls over. BTW the trick for keeping code formatting is to use the [Pre] tags from the TagPanel.

Reply | Quote

OK, this isn’t great code, but it works to update the original source from the datagrid.

Public Sub UpdateSource(rstDest As ADODB.Recordset)
  Dim cnn As ADODB.Connection
  Dim rstSource As ADODB.Recordset
  Dim fld As ADODB.Field
  Dim errADO As ADODB.Error
  Dim errsCol As ADODB.Errors
  Dim strSrc As String
  Dim lngRecCnt As Long
  Dim strCrit As String
  
  On Error GoTo UpdateSource_err
  
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
    Set errsCol = cnn.Errors
    
     strSrc = "select * from customers"
      
    Set rstSource = New ADODB.Recordset
    rstSource.CursorLocation = adUseClient
    rstSource.Open strSrc, cnn, adOpenDynamic, adLockOptimistic, adCmdText
    
    rstDest.MoveFirst
    
    With rstDest
      
        ' check to see if there were changes to the data
        If .EditMode = adEditInProgress Or .EditMode = adEditAdd Then
           rstSource.MoveFirst
      
          Do
             ' create the criteria to match the key field
             strCrit = .Fields(0).Name & " = '" & .Fields(0) & "'"
 
             'find the matching record in the source recordset
             rstSource.Find strCrit
              
             ' loop through the fields in rstDest and compare them to
             ' the fields in the source recordset
             For Each fld In .Fields
                If fld.Type  adChapter Then
 
                    ' if the value has changed, update the original source
                    If rstSource(fld.Name).Value  fld.Value Then
                      rstSource(fld.Name).Value = fld.Value
                    End If
 
                End If
             Next fld
             'update the source recordset
             rstSource.Update
              
             'move to the next record in rstDest
            .MoveNext
          Loop Until .EOF
       End If
      
    End With
    
UpdateSource_exit:
  On Error Resume Next
  rstSource.Close
  cnn.Close
  Set rstSource = Nothing
   Exit Sub
UpdateSource_err:
  If errsCol.Count > 0 Then
    For Each errADO In errsCol
      MsgBox errADO.Number & "-" & errADO.Description
    Next errADO
    errsCol.Clear
  Else
    MsgBox Err.Number & "-" & Err.Description
  End If
  Resume UpdateSource_exit
End Sub

Reply | Quote

Thanks for taking time to figure this out. I can’t seem to get it to work, however. When I try to call the subroutine, I get an “argument not optional” message, but nothing I’ve figured out to use within the parentheses seems to work.

–Karyl

Reply | Quote

The code works from a form in an Access database. If you’re trying to do it from Word, it should still work, but you need to pass it the datagrid’s datasource when you call it. Like this:

UpdateSource DG0.DataSource

Reply | Quote

Thanks so much. That’s what I was doing, but I was putting it in parentheses. I’ve been away from coding too long!

Anyway, this is what happened. I changed Field 2 in Record 1. When I ran the code, it changed Field 2 in Record 1, as it should. But it also changed Fields 2 and 3 in Records 2 and 3 to the same values as those in Record 1. And then I got this error message: -2147467259-Key column information is insufficient or incorrect. Too many rows were affected by update.

So, obviously a glitch somewhere. But I don’t have more time to play with it for now, so I’m going to give it up for this application and just edit directly in Access. If I find some other approach, I’ll let you know. Many thanks for playing with this for me.

–Karyl

Reply | Quote

The code I posted assumed that the value in the first column of the datagrid was a unique key. If you were using a multifield key or if your data did not have unique keys (i.e., the many side of a one-to-many relationship), then you would get exactly the result you encountered.

Reply | Quote

It was a multifield key. I’ll try adding a unique key and just hide it from the user. Thanks!

–Karyl

Reply | Quote

Or you could adapt the code to a multifield key.

Reply | Quote

<>

Sure. No problem. I can learn how to do that by this afternoon.

I added the field, and everything works great–except adding a new record. Guess I forgot to mention that. . .

Thanks for all your help!

–Karyl

Reply | Quote

Do you have the datagrid set up to allow new records? If so, you’ll have to test to see whether you hit EOF when you try to find a match in the original recordset. If you do, you’ll need to branch conditionally to issue an AddNew method call before you start populating the fields in the new record and you won’t test for matches because all the fields in the new record will need to be populated. You’re big problem is going to be creating the unique key for that new record. How were you planning to do that?

If your unique key is one you can create in code, you can add a boolean argument to the UpdateSource function (I called it blnAllowAdd) and use it like this inside the Do Loop in UpdateSource:

             If rstSource.EOF Then
                'this is a new record, so add it if allowed
                If blnAllowAdd Then
                  rstSource.AddNew
                  For Each fld In .Fields
                     If fld.Type  adChapter Then
                         rstSource(fld.Name).Value = fld.Value
                     End If
                  Next fld
                Else
                  MsgBox "You are not allowed to add records."
                  Exit Do
                End If
             Else
                ... etc.

Then just add DG0.AllowAddNew as the last argument passed to UpdateSource.

Reply | Quote

Dear Charlotte,

I finally had time to play with this DataGrid problem some more. I ended up with three controls on my form, the DataGrid, a ADO Data control, and a command button. I replaced all the code you helped me write (to edit the records in the DataGrid) with the code below. With the DataGrid set to allow additions, deletions, and edits, everything works–without any code to make it happen! This is how I thought it should work, but I just couldn’t figure it out. I think the confusing part was that in VB, the DataGrid has a visible DataSource property. But when you use the control in Word VBA, that property is not available except through code.

Thanks for all your help. You got me far enough to have something to present to the client, even though it didn’t do everything they needed. Now it does. I thought you’d like to know the final outcome. Again, many thanks!

–Karyl

Private Sub UserForm_Initialize()
Dim strDB As String
Dim strFolder As String

‘Define the path and name for the database file
strFolder = Options.DefaultFilePath(Path:=wdWorkgroupTemplatesPath) & Application.PathSeparator
strDB = strFolder & “BidItems.mdb”

‘Set the connection string for the ADO data control
Adodc1.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strDB & “;Persist Security Info=False”

‘Set the datasource for the DataGrid
Set DGO.DataSource = Adodc1

‘Set the column widths
frmEditBidItems.DGO.Columns(0).Width = 0
frmEditBidItems.DGO.Columns(1).Width = 30
frmEditBidItems.DGO.Columns(2).Width = 30
frmEditBidItems.DGO.Columns(3).Width = 344

End Sub

Public Sub cmdDone_Click()
‘Close the form
Unload frmEditBidItems
End Sub

Reply | Quote

Thanks very much for the post – it’s always nice to know (1) that a problem got solved, and (2) how it was solved so we can benefit from the experience. Again thanks for your contribution.

Reply | Quote

I’ve been playing with this a bit more, and I’m wondering if I should have used the DataGrid in conjunction with the DataSourceControl instead of popluating it with code. If so, I’m not sure how to do that, although it seems like it should be pretty easy. (And more closely resembles the way I was expecting the control to work based on my previous–and very limited–experience working with data files in VB4.

–Karyl

Reply | Quote

Data controls are the way that VB forms connect to recordsets. Access forms connect directly through their recordset or recordsource properties. I’ve never tried to use a data control in Access and have no idea whether it would work or not, but Access forms and not the same as the forms used in VB and in other Office apps, so I wouldn’t expect it to work the way you see it in VB.

Reply | Quote

<>

I’m trying to connect to Access data through a VBA form in Word. It seems like it should be as easy to do as it is through VB, but it doesn’t seem to be turning out that way! Guess I’ll spend the afternoon looking through all my books to see if I can find anything similar. Thanks!

–Karyl

Reply | Quote