-
June 27th, 2012, 07:45 AM
#1
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
-
June 27th, 2012, 08:48 AM
#2
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
-
June 27th, 2012, 08:51 AM
#3
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.
Last edited by d.paulson; June 27th, 2012 at 08:54 AM.
-
June 27th, 2012, 11:24 AM
#4
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.
Always use [code][/code] tags when posting code.
-
June 27th, 2012, 04:34 PM
#5
Re: Delete button problem
Well, yes. That's what I had provided as a solution, isn't it?
-
June 27th, 2012, 06:54 PM
#6
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
Always use [code][/code] tags when posting code.
-
June 28th, 2012, 08:24 AM
#7
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|