Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2005
    Posts
    11

    Unanswered: Datagrid not being updated??

    Hey, I have a main form which contains a datagrid displaying the data contained in a table in my Acces DB. From this form you can start a 'wizard' in which, over several forms, you can enter the information required to add another record to that table in my Access DB. When the user clicks finish on the last form of this wizard, the record is then transferred by code to the DB, which works great. However, at the end of this, the form is unloaded but the datagrid on my main form does not display this new record. I've tried using the refresh and rebind methods of the datagrid after the transfer code on my finish button but with no effect. Anyone know where I could be going wrong?

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi, Try using the REQUERY method. It's different to refresh in that it actually re-queries the data source. Whereas Refresh only shows changes made to the current set of data (that you've queried) i.e. it doesn't actually requery.
    hth
    Chris

  3. #3
    Join Date
    Jul 2005
    Posts
    11
    Ok, I've tried using the requery command on my recordset, still doesn't work??

    Code:
    dataenvironment1.rsCommand1.requery
    frmMain.grdJobDetails.refresh
    Anything I'm doing wrong? :s

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Are you sure the recordset actually contains your new record after requery. Might be worth a debug just to check. If it does then at least you know that your recordset is ok and that the datagrid isn't updating.
    Chris

  5. #5
    Join Date
    Jul 2005
    Posts
    11
    Yep, just checked the table in Access and it has my latest record, but it wasn't shown in the datagrid.

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by minis85
    Yep, just checked the table in Access and it has my latest record, but it wasn't shown in the datagrid.
    Did you check the recordset (not the table) ? i.e. has the recordset refreshed itself with the table ?
    How are you creating the recordset ? Does your query definition include the new record ?
    Chris

  7. #7
    Join Date
    Jul 2005
    Posts
    11
    Erm, not sure how to go about checking the recordset. I use the data environment, the datagrids datasource is set to this data environment, and the datamember of the datagrid is set to command1 of the data enviroment. When the form first loads up it displays all the records correctly, but when I add a record through this 'wizard' I've built and updated the table using ADO, the datagrid doesn't show this new record? So theres no query definition as such on the datagrid, the datagrid is just bound to the data environments command1.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Datagrid? As in VB datagrid or as in "subform"? Are you working IN access or simply using it as a back end?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I'm not terribly familiar with datagrids. You wrote some code:
    Code:
    dataenvironment1.rsCommand1.requery
    . Where are you writing this code? Is there more code ?

    This code implies your datasource is a recordset. Note the rs in rsCommand1. I did a quick search and found the following text here:

    The DataGrid control is a great way to display multiple data rows in a table-like format. Unfortunately, the control is also plagued with bugs. Some have been fixed by Service Patch 3, but some haven't. For instance, if you connect the DataGrid to a DataEnvironment, then make changes to the underlying recordset and refresh the DataGrid with the Refresh method, the control still doesn't reflect the changes. Unfortunately, the Refresh method doesn't work when the control's DataSource is a DataEnvironment. Instead, to show the updated recordset changes, first update the DataEnvironment's recordset, then rebind the DataGrid to the DataEnvironment. So, if you have a Refresh button, it's click event might look like this:

    DataEnvironment1.rsCommand1.Requery
    Set DataGrid1.DataSource = DataEnvironment1

    Now, when you click the Refresh button, the code rebinds the DataEnvironment to the DataGrid and refills the control with the refreshed data.
    So on that basis I guess you need to put the "Set... " line in.
    hth
    Chris

  10. #10
    Join Date
    Jul 2005
    Posts
    11
    Thought you'd solved it then, but no luck!

    I have my main form, 'frmMain', from this the user clicks a button which starts my wizard, which is basically a series of forms in which the user enters various information about a job. On the last form or page of this wizard, there is the finish button, 'cmdFinish', which transfers all these details to my Access 97 database, and a couple of lines of code at the end to refresh/update/rebind the datagrid on my main form, frmMain. Heres the code:

    Code:
    Private Sub cmdFinish_Click()
    Dim strCnn As String
    Dim DataItem As ADODB.Field
    
    Set cnn1 = New ADODB.Connection
        strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\My Documents\Job Doorset Program\Data.mdb;Mode=ReadWrite|Share Deny None;Persist Security Info=False"
    cnn1.Open strCnn
    
    'Open connection
    Set rstData = New ADODB.Recordset
    rstData.CursorType = adOpenKeyset
    rstData.LockType = adLockOptimistic
    rstData.Open "tblData", cnn1, , , adCmdTable
    
    rstData.AddNew
    rstData!DoorsetNum = frmDoorDetails.txtDoorNum
    rstData!JambProf = frmDoorDetails.cmbJambProf
    rstData!HeadProf = frmDoorDetails.cmbHeadProf
    rstData!CillProf = frmDoorDetails.cmbCillProf
    rstData!TransomProf = frmDoorDetails.cmbTransProf
    rstData!MullionProf = frmDoorDetails.cmbMullProf
    
    
    rstData!JambW = frmJamb.txtJambW
    rstData!JambF = frmJamb.txtJambF
    rstData!JambC = frmJamb.txtJambC
    rstData!JambD = frmJamb.txtJambD
    rstData!JambE = frmJamb.txtJambE
    rstData!JambH = frmJamb.txtJambH
    rstData!JambA = frmJamb.txtJambA
    rstData!JambAA = frmJamb.txtJambAA
    rstData!JambAX = frmJamb.txtJambAX
    rstData!JambAAX = frmJamb.txtJambAAX
    rstData!JambCC = frmJamb.txtJambCC
    rstData!JambMaterial = frmJamb.cmbJambMat
    rstData!JambGauge = frmJamb.cmbJambThick
    
    Dim counter As Long
    
    Do Until counter = rstData.Fields.count
        If rstData.Fields(counter).Type = adVarWChar Then
            If rstData.Fields(counter).Value = vbNullString Then
                rstData.Fields(counter).Value = "0"
            End If
        End If
        counter = counter + 1
    Loop
    
    rstData.Update
    
    rstData.Close
    
    Set cnn1 = Nothing
    strCnn = vbNullString
    
    'Set frmMain.grdJobDetails.DataMember = vbNullString
    'frmMain.grdJobDetails.DataMember = DataEnvironment1.Command1
    'DataEnvironment1.rsCommand1.Resync adAffectAllChapters
    DataEnvironment1.rsCommand1.Requery
    Set frmMain.grdJobDetails.DataSource = Nothing
    Set frmMain.grdJobDetails.DataSource = DataEnvironment1
    frmMain.grdJobDetails.ReBind
    frmMain.grdJobDetails.Refresh
    
    Unload Me
    'Me.Hide
    'In time there will be more code here so that the doorset dimensions will be added to the job!
    End Sub
    I've tried allsorts of combinations but the datagrid is just not picking up the new records. The record is definitely being saved to the Access DB, as I can open the database after I've clicked the finish button and its there. The only thing I can think of is that the database is being left 'open' and so the datagrid can't update?

    One other thing I've noticed. I added a data control with datasource set to my Access DB, and a textbox with its source set to a field in that DB and the same thing occured, it will load up the data when the applicatio starts, but won't update?

  11. #11
    Join Date
    Jul 2005
    Posts
    11
    Anyone?

  12. #12
    Join Date
    Jul 2005
    Posts
    11
    Well after several days of working through this myself, I finally found out that I was missing one line of code:
    Code:
    rstData.update
    Must admit, I'm quite astonished no one else picked up on that. But a sincere thanks to howey who at least tried!

Posting Permissions

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