Deleting Item from ListView Field from MS-Access Table

Hi Guys,

I'm wondering on how to delete an item in the ListBox that field from the Table MS-Access.

VB Code:

Private Sub CmdDelete_Click()
Call OpenConn
 If Me.List1.ListIndex = -1 Then
 MsgBox "You should select a Level to delete", vbExclamation, "Delete Level"
 Else
 Me.List1.RemoveItem Me.List1.ListIndex
 
 
RSLevels.Open "Levels", Conn, adOpenDynamic, adLockOptimistic, adCmdTable
 
    With RSLevels
          If Not (.BOF = True Or .EOF = True) Then
         .Delete adAffectCurrent
          If Not (.BOF = True Or .EOF = True) Then
         .MoveNext
          If .EOF Then RS.MoveLast
         .Requery
          Me.List1.Refresh
         .Close
          End If
          End If
   End With
   End If
   
End Sub

I've used the above code, it's deleted the select item from the ListBox perfectly, but once you cameback to the Form you will notice that the Item deleted is the First Item in the Table.

Can anyone help to delete the Selected Item from the ListBox as well as from the Table ??

Many Thanks in advance,

habibalby

Hi Once again,

I've made it with the ListView and also same.

VB Code:

Private Sub CmdDelete_Click()
Call OpenConn
 If Me.ListView1.SelectedItem.Selected = 0 Then
 MsgBox "You should select a Level to delete", vbExclamation, "Delete Level"
 Else
 
RSLevels.Open "Levels", Conn, adOpenDynamic, adLockOptimistic, adCmdTable
 
    With RSLevels
  
         .Delete adAffectCurrent
         .Update
         .Requery
          Me.ListView1.Refresh
         .Close
   End With
 
    If ListView1.SelectedItem.Index <> 1 Then
    ListView1.ListItems.Remove ListView1.SelectedItem.Index
    End If
    End If
End Sub

Regards,
habibalby

You are deleting an unknown row? How did you know, in your code, what row to delete? You should have something like

VB Code:

RSLevels.Open "select [id] from levels where [id] = " & val(listView1.SelectedItem.key), Conn, adOpenDynamic, adLockoptimistic, adCmdTable
 
RSLevels.Delete

Quote:

---

Me.ListView1.Refresh

---

This will not refresh your listview with the data you have in your rs (in VB6 at least).

Take note that ID was assumed a numeric data type (INT, LONG,etc)

dRAMmer

Well, I've used your suggestion to get rid of this, but I end up with a Syntex Error.

VB Code:

RSLevels.Open "Select Levels.[ID] From Levels Where Levels.[ID] = " & Val(ListView1.SelectedItem.Key), Conn, adOpenDynamic, adLockOptimistic, adCmdTable
 
RSLevels.Delete

Hi,

Well, now the item is deleted from the ListView, but it won't be deleted from the Table.

VB Code:

RSLevels.Open "Select ID From Levels Where ID=" & Val(ListView1.SelectedItem.Key), Conn, adOpenDynamic, adLockOptimistic, adCmdText
 
With RSLevels
If Not (.BOF = True Or .EOF = True) Then
        .Delete
         If Not (.BOF = True Or .EOF = True) Then
        .MoveNext
         If .EOF Then RS.MoveLast
        .Update
        .Requery
        .Close
        End If
        End If
End With
 
    If ListView1.SelectedItem.Index <> 1 Then
    ListView1.ListItems.Remove ListView1.SelectedItem.Index
    MsgBox " Item Deleted", vbExclamation, "Delete Item"
    End If

Any Help ????????

Thanks,
Habibalby

Hi!
You might have forgotten to set the key of each listitem to the id of the table.

Hi mate,

Which Key you mean ?? in the Table I have two field one is the ID which is AutoNumber and the Other is Level Which is Text.

Is there any other key I have to set for the ListItem ??

Thanks,
Habibalby

Hi once again,

I've made the Key as per the Column Heard Caption which is level. and I've tried to delete also, but I can't always is getting error Syntax error.

Thanks,
Habibalby

VB Code:

ListView1.ListItems.Add ,"ID" & .fields("id"),.fields("level")

Deleting the the record

VB Code:

RSLevels.Open "Select ID From Levels Where ID=" & Val(mid(ListView1.SelectedItem.Key,3,len(ListView1.SelectedItem.Key)-2)), Conn, adOpenDynamic, adLockOptimistic, adCmdText
rslevels.delete
'or
'
Dim sKey as string
sKey = ListView1.SelectedItem.Key
Mid(sKey, 1, 2) = Space(2)
 
RSLevels.Open "Select ID From Levels Where ID=" & Val(skey), Conn, adOpenDynamic, adLockOptimistic, adCmdText
RSLevels.Delete

VB Code:

ListView1.ListItems.Add ,"ID" & .fields("id"),.fields("level")

Deleting the the record

VB Code:

RSLevels.Open "select ID from Levels where ID=" & val(mid(listview1.selecteditem.key,3,len(listview1.selecteditem.key)-2)), conn, adopendynamic, adlockoptimistic, adcmdtext
rslevels.delete
'
'or
Dim sKey as strin
sKey = listview1.SelectedItem.Key
Mid(Skey, 1, 2) = space(2)
rslevel.open "Select ID From Levels Where ID=" & val(skey),conn, adopendynamic, adlockoptimistic, adcmdtext
rslevels.delete

HI mate,

I've tried both techniques, but it didn't works. Can you have a look at the simple project that I posted here, download it and check if y ou can modify it??


Thanks,
Habibalby

Where's the project? You forgot to post it?

Quote:

---

Originally posted by dRAMmer
Where's the project? You forgot to post it?

---

Here it is, take notice of the comments.

Many thanks mate. But one more think I'd like to ask you, is it wasn't deleted becuase the ID's not entered along with the name. ??

VB Code:

ListView1.ListItems.Add , "emp" & .Fields("ID"), .Fields("Name")

Thanks,
habibalby

Yes it was, you need to have a reference to the record you want to manipulate, so you have to set the key of the listitem to the reference (ID, for that matter) of your row. ;)

Hi Mate. Yes, I got it now, but the code also it can be run without the Dim RA as Long Variable Like:

VB Code:

Skey = ListView1.SelectedItem.Key
Mid(Skey,1,3) = Space(3)
 
Sql = "Delete From TableName where [ID]=" & Val(Skey)
Conn.Excute Sql, SKey
 
If Skey = 0 then
'Do Nothing
Else 
Msgbox "Item Deleted"
End if

Regards,
Habibalby