Re: Delete button problem
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
Re: Delete button problem
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.
Re: Delete button problem
Or you could use a delete query with a where clause instead which is much more efficient than using a select then a delete.
Re: Delete button problem
Well, yes. That's what I had provided as a solution, isn't it?
Re: Delete button problem
Yep I guess you did, I somehow overlooked the delete in the first code block and saw the select in the second
Re: Delete button problem
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