Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Populating Datagrid (97)

    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?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Populating Datagrid (97)

    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?

  4. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Datagrid (97)

    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

  5. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Datagrid (97)

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

  6. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating Datagrid (97)

    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):

    <pre> 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 </pre>


    Here are the two functions it calls:

    <pre>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</pre>

    Charlotte

  7. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Datagrid (XP)

    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

  8. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating Datagrid (XP)

    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"?
    Charlotte

  9. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Datagrid (XP)

    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
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Dim strDB As String

    strDB = Options.DefaultFilePath(wdUserTemplatesPath) & "DrywallBidItems.mdb"</span hi>

    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" <span style="background-color: #FFFF00; color: #000000; font-weight: bold">& strDB</span hi>
    strSrc = "select * from <span style="background-color: #FFFF00; color: #000000; font-weight: bold">qryBidItems"</span hi>
    Set rstSource = cnn.Execute(strSrc)
    Set rstDest = MakeRst(rstSource)
    OpenAndFillRst rstSource, rstDest
    rstSource.Close
    cnn.Close
    Set <span style="background-color: #FFFF00; color: #000000; font-weight: bold">frmDataGrid</span hi>.DGO.DataSource = rstDest
    End Sub

  10. #9
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Datagrid (XP)

    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

  11. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating Datagrid (XP)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  12. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Populating Datagrid (XP)

    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.
    Charlotte

  13. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updates From Datagrid (XP)

    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! <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15> 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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> BTW the trick for keeping code formatting is to use the <!t>[Pre]<!/t> tags from the TagPanel.
    Charlotte

  14. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updates From Datagrid (XP)

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

    <pre>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</pre>

    Charlotte

  15. #14
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Populating Datagrid (XP)

    << Data controls are the way that VB forms connect to recordsets. Access forms connect directly through their recordset or recordsource properties. >>

    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

  16. #15
    Star Lounger
    Join Date
    Apr 2001
    Location
    Washington, Washington, USA
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updates From Datagrid (XP)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •