Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    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)

    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
    Charlotte

  2. #17
    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 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

  3. #18
    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)

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

  4. #19
    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)

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

    --Karyl

  5. #20
    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)

    Or you could adapt the code to a multifield key. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  6. #21
    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)

    << Or you could adapt the code to a multifield key. >>

    Sure. No problem. I can learn how to do that by this afternoon. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    I added the field, and everything works great--except adding a new record. Guess I forgot to mention that. . . <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    Thanks for all your help!

    --Karyl

  7. #22
    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)

    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:

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


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

  8. #23
    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)

    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:=wdWorkgroupTemplates Path) & 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

  9. #24
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,496
    Thanks
    3
    Thanked 42 Times in 42 Posts

    Re: Updates From Datagrid (XP)

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

Page 2 of 2 FirstFirst 12

Posting Permissions

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