Remove Selected item from database when removed from listview(Resolved -Thanks!!!!! )
Hi All
Need a little help with this one, I am trying to write code to remove a selected item from the database when the user selects an item in the list view and here is what I have so far
Dim adoRemove As Recordset
Dim vacancyid As String
Dim code As String
vacancyid = Form7.txtFields(0).Text
code = ListView1.List(ListView1.ListIndex)
Adoremove.open = "select * from tblrequired requirements where lngvacancyid = " & vacancyid And bytcode = code ,db, , adOpenStatic, adLockOptimistic
.Delete
.Close
I have two columns in the listview and I want to the sql statement to get the value from the 2nd column in the listview for the selected item
Can you help me?
Thanks in advance
I am getting there, just need a little help
Hi There
I have got as far as being able to delete the record is if i do this -
[Highlight=VB]
Dim adoRemove As Recordset
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=XPHOME;uid=Paul ;pwd=;database=ContractManagement;"
Set adoRemove = New Recordset
Dim vacancyid As String
Dim code As String
vacancyid = Form7.txtFields(0).Text
code = 6
'Dim tblrequired As String
tablerequired = tblexpertiserequirements
adoRemove.Open "select * from tblexpertiserequirements where lngvacancyid = " & vacancyid, db, adOpenStatic, adLockOptimistic
adoRemove.MoveFirst
adoRemove.Find "bytexpertisecode = '" & code & "'"
adoRemove.Delete
[Highlight=VB]
This will delete a the record where the code is 6 - I just need to know how to reference the column in the listview which
contains the code
Thanks
Okay so i am still not there
So here we are - I have two columns in the listview
When removing an item from the database, i need to know the Vacancyid ( from the textbox on the form I dont have a problem with that ) and the bytexpertisecode( from the second column in the listview). I cant find out how to do it, i have searched and searched to no avail
Please help
You people are legends !!!
Thanks for helping me out with somethign that was driving me crazy - i will have à cold beer waiting for all!!!!
This is what i am using and it works!
[/Highlight]
Dim adoRemove As Recordset
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=XPHOME;uid=Paul ;pwd=;database=ContractManagement;"
Set adoRemove = New Recordset
Dim vacancyid As String
Dim code As String
vacancyid = Form7.txtFields(0).Text
code = ListView2.SelectedItem.SubItems(1)
'Dim tblrequired As String
'tablerequired = tblexpertiserequirements
'adoRemove.Open "DELETE FROM tblexpertiserequirements WHERE lngvacancyid = " & ListView2.SelectedItem.SubItems(1), db, adOpenStatic, adLockOptimistic
adoRemove.Open "select * from tblexpertiserequirements where lngvacancyid = " & vacancyid, db, adOpenStatic, adLockOptimistic
adoRemove.Find "bytexpertisecode = '" & code & "'"
adoRemove.Delete
adoRemove.Close
[/Highlight]