[RESOLVED] MsFlexgrid - Deleted record is still there

[ScottMatthews]

I have a form with MSFlexgrid control and a button [edit/delete].
(The data is stored in an Access file if that matters)

The button calls a 2nd Edit form where the user views the selected record.
I run a 2nd recordset to pull just the one selected row in the MsFlexgrid Control to feed this screen. All data displays fine here.

From here they have a [Save] button and [Delete] button on the form.

I have the editing working great using ADO. (.update).

But, when I use the Delete button, the record is deleted from the Access Table, but still shows up on the MSFlexgrid after "Unloading" the edit form.

Here is the code for "pulling" the one record for the Edit/Delete screen:

      strWhere = " where SUVC = " & strSUVC & " AND " & _
                      "SF = " & strSF & " AND " & _
                      "CLN = " & strCln
      strSql = "Select * from VendorMaster" & strWhere

      Set rstESupplier = New ADODB.Recordset
      With rstESupplier
         .CursorType = adOpenKeyset
         .CursorLocation = adUseClient
         .LockType = adLockOptimistic
         .Open strSql, adoActiveConnection
         .MoveFirst
      End With

For the delete button, the following code is used:

      With rstESupplier
        .Delete
      End With

I have tried using ".MoveNext" , ".Requery", ".Update" after .Delete, but none work.

I call the same function (Update Grid) after ".Delete" that I do when I load the original form with the MSFlexGrid.

If I close the Form with the MSFlexgrid control and then go back to into it, the data is refreshed. The Deleted record is gone.

What additional things do I need to do when "deleting" a record?

Thanks.

[dglienna]

You would have to delete the column in the flxgrid when you delete from the table. I usually just requery the table and repopulate the grid. It only takes a second.

[ganeshmoorthy]

or you can just reload the grid data, so that it has only the latest records

[ScottMatthews]

I am attempting to Reload the MSFlexgrid as you describe, but the MsFlexgrid on the screen still has the record. I thought by doing the below, after the ".Delete' would do the trick.

Here is the UpdateGrid code I use in reloading the MSFlexgrid:

Public Sub UpdateGrid()
    Dim strSql            As String
    Dim strOrderBy        As String
    Dim intRecordCount    As Integer
    Dim intTheRow         As Integer
    
    strSql = "Select aka, buyer, suvc, sf, cln, baserate, perwhat,  oldsurcharge, " & _
                    "newsurcharge, startdate, enddate, changepo, appType, " & _
                    "apptime, getamtfmtable, distance, tlperwhat, " & _
                    "brchanged, fscrule from vendormaster"
    strOrderBy = " order by enddate"
    strSql = strSql + strOrderBy
    Set rstSupplier = New ADODB.Recordset
    With rstSupplier
        .ActiveConnection = adoSupplier
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic
        .Source = strSql
    End With
    rstSupplier.Open
    intRecordCount = rstSupplier.RecordCount
    
    intTheMaxRow = 20
    intTheMaxColumn = 17
    
    fgrdSupplier.Rows = intRecordCount + 1
    fgrdSupplier.Cols = 18       ' Note: zero based
    fgrdSupplier.FixedCols = 0   ' Gets rid of the "Row" heading
    fgrdSupplier.FixedRows = 1
        
    intTheRow = 0
    With fgrdSupplier
        .TextMatrix(0, 0) = "Supplier"
        .TextMatrix(0, 1) = "Buyer"
        .TextMatrix(0, 2) = "SUVC"
        .TextMatrix(0, 3) = "SF"
        .TextMatrix(0, 4) = "CLN"
        .TextMatrix(0, 5) = "Base"
        .TextMatrix(0, 6) = "Per"
        .TextMatrix(0, 7) = "Old"
        .TextMatrix(0, 8) = "New"
        .TextMatrix(0, 9) = "Start"
        .TextMatrix(0, 10) = "End"
        .TextMatrix(0, 11) = "P"
        .TextMatrix(0, 12) = "F"
        .TextMatrix(0, 13) = "W"
        .TextMatrix(0, 14) = "T"
        .TextMatrix(0, 15) = "M"
        .TextMatrix(0, 16) = "TL"
        .TextMatrix(0, 17) = "BrChanged"
    Do Until rstSupplier.EOF
        intTheRow = intTheRow + 1
        .TextMatrix(intTheRow, 0) = rstSupplier.Fields("aka").Value
        .TextMatrix(intTheRow, 1) = rstSupplier.Fields("buyer").Value
        .TextMatrix(intTheRow, 2) = rstSupplier.Fields("suvc").Value
        .TextMatrix(intTheRow, 3) = rstSupplier.Fields("sf").Value
        .TextMatrix(intTheRow, 4) = rstSupplier.Fields("cln").Value
        .TextMatrix(intTheRow, 5) = rstSupplier.Fields("baserate").Value
        .TextMatrix(intTheRow, 6) = rstSupplier.Fields("perwhat").Value
        .TextMatrix(intTheRow, 7) = rstSupplier.Fields("oldSurcharge").Value
        .TextMatrix(intTheRow, 8) = rstSupplier.Fields("newSurcharge").Value
        .TextMatrix(intTheRow, 9) = rstSupplier.Fields("startdate").Value
        .TextMatrix(intTheRow, 10) = rstSupplier.Fields("enddate").Value
        .TextMatrix(intTheRow, 11) = rstSupplier.Fields("changepo").Value
        .TextMatrix(intTheRow, 12) = rstSupplier.Fields("appType").Value
        .TextMatrix(intTheRow, 13) = rstSupplier.Fields("appTime").Value
        .TextMatrix(intTheRow, 14) = rstSupplier.Fields("getAmtfmTable").Value
        .TextMatrix(intTheRow, 15) = rstSupplier.Fields("distance").Value
        .TextMatrix(intTheRow, 16) = rstSupplier.Fields("tlperwhat").Value
        .TextMatrix(intTheRow, 17) = rstSupplier.Fields("brchanged").Value
        
        rstSupplier.MoveNext
    Loop
    End With
    rstSupplier.MoveLast
   
End Sub

[brucevde]

Isn't this the same problem as your previous thread?

In the UpdateGrid procedure you are using the adoSupplier connection. The other procedure is using a variable called adoActiveConnection.

[ScottMatthews]

My mistake.

I was not calling UpdateGrid() in the Delete_Click function.

Thanks for the help.