Delete button problem

Okay,

so here's my problem.
I have a MS Hierarchical Flex Gird Control [fgdCompany] which is used to display records that are taken from a database. Everything works great but not the Delete button. What the delete button is supposed to do is when a record is selected in fgdCompany, it is supposed to delete that current record. But the problem is, it always deletes the first record in the fgdCompany. Not the record that is selected in fgdCompany. What I am doing wrong? Please help me debug this error!!!!

Code used by me for the Delete button is given below :

Code:

---

Dim rsCompany As New ADODB.Recordset
  Dim cn As New ADODB.Connection
  Dim strSQL As String
 
  cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & App.Path & "\Sample.mdb;" & _
                        "Persist Security Info:False"
                                           
  cn.Open
  strSQL = "SELECT [Company].* FROM [Company]"
  rsCompany.Open strSQL, cn, adOpenStatic, adLockPessimistic
 
  If rsCompany.RecordCount > 0 Then
    msg = MsgBox("Delete Record?", vbYesNo)
    If msg = vbYes Then
      rsCompany.Delete
      rsCompany.MovePrevious
      If rsCompany.EOF Then
        rsCompany.MovePrevious
      ElseIf rsCompany.BOF Then
        rsCompany.MoveNext
      End If
      If rsCompany.EOF And rsCompany.BOF Then
        txtName = ""
        txtAddress = ""
        txtTelephoneNo = ""
        txtVehiclesReg = ""
        Exit Sub
      End If
    End If
    If rsCompany.Fields("Company Name") <> "" Then
      txtName = ""
    End If
    If rsCompany.Fields("Address") <> "" Then
      txtAddress = ""
    End If
    If rsCompany.Fields("Telephone Numbers") <> "" Then
      txtTelephoneNo = ""
    End If
    If rsCompany.Fields("Vehicles Registered") <> "" Then
      txtVehiclesReg = ""
    End If
  ElseIf rsCompany.RecordCount = 0 Then
    txtName = ""
    txtAddress = ""
    txtTelephoneNo = ""
    txtVehiclesReg = ""
    MsgBox "No records to be deleted."
  End If
 
  Set fgdCompany.DataSource = rsCompany
 
  If rsCompany.RecordCount <> 0 Then
    rsCompany.Update
  End If

---

Obviously you are not specifying the record you want to delete.
You open a new recordset which will automatically be set to point to the first record.
Your new recordset has no idea where your flexgrid cursor is.
What you do is determine which record you want to delete, like

Code:

---

  Dim Name$, Addr$, sqlDel$
  Const colName = ? here goes the column number where the name is
  Const colAddr = ? here the column number of the address note that column numbers start at 0

  With fgdCompany
    Name = .TextMatrix(.Row, colName)
    Addr = .TextMatrix(.Row, colAddr)
    'now we can delete with an SQL statement
    sqlDel = "DELETE FROM [Company] WHERE [Company Name]='" & Name & "' AND [Address]='" & Addr & "'"
    cn.Execute sqlDel 'let the connection execute the delete command (the connection is assumed open here)
  End With

---

Now if you still have a reference to the recordset you had put in the grid you can perform a recordset.Requery to actualize.
If not you have to reload it.

Code:

---

  Dim strSQL$, rsCompany as New ADODB.Recordset
  strSQL = "SELECT [Company].* FROM [Company]"
  rsCompany.Open strSQL, cn, adOpenStatic, adLockPessimistic
  Set fgdCompany.DataSource = rsCompany

---

When you open a recordset, it points to the first record. You'll need to navigate the recordset to that your flexgrid is showing before you issue the delete.


oops. Looks like WoF is alot quicker than me this morning.

Or you could use a delete query with a where clause instead which is much more efficient than using a select then a delete.

Well, yes. That's what I had provided as a solution, isn't it?

Yep I guess you did, I somehow overlooked the delete in the first code block and saw the select in the second

Btw.: would there be another possibility to determine at which record the cursor of the grid is actually pointing?
As I have yet found none, that's how I work around usually, provided my table has an index key field with a unique number (what most tables should have anyway).
I write the SELECT so that the Key field is th first one in the recordset.
When setting the FlexGrid, the key goes to column 0. We can set column 0 width to zero, so we don't see the key value.
When wanting to know the key of the current record where the cursor is on I do:

Code:

---

  CurrentKey = Val(flx.TextMatrix(flx.Row, 0))

---

To delete this current record you can then simply:

Code:

---

  DBConn.Execute "DELETE FROM myTable WHERE [KeyField]=" & CurrentKey

---

Also a reference to the Recordset should be kept in a variable when the flex is loaded in the first place like

Code:

---

  Private rsList as ADODB.Recordset
  ...
  rsList.Open SQLstring, DBConn, adOpenStatic, adLockReadOnly
  set flxList.DataSource = rsList

---

So to refresh the list you can now do a rsList.ReQuery and Set flxList.DataSource = rsList