[RESOLVED] Still get values from checkbox

[dexjel140503]

guys why is it that im having an error saying "INDEX OUT OF BOUNDS" here in this code? i just followed the same logic i learned here in this forums. here is the code..

If ListView2.ListItems.Count = 0 Then
MsgBox "Nothing to add.", vbCritical + vbApplicationModal, "Message"
Exit Sub
Else
For nI = 1 To ListView2.ListItems.Count
If ListView2.ListItems(nI).Checked = True Then

Set rs = New ADODB.Recordset
rs.Open "Select * from sendtbl where empid like '%" &ListView2.ListItems(nI).SubItems(4) & "%'", cn, 3, 2
rs.Delete
rs.Update
rs.Close
Set rs = Nothing

Call load
End If

Next nI

End If

can someone help me please??

[Españolita]

not totally sure, but try this as a first step.
put the Set statements outside the For-Next loop
remove the rs.delete statement because if you delete it, there is nothing to update

EDIT, When you update, you also need to actually change a part of the record

[TysonLPrice]

I'm not sure but shouldn't it be:

For nI = 1 To ListView2.ListItems.Count - 1

[DataMiser]

Yes it is here that is the problem

For nI = 1 To ListView2.ListItems.Count

List are indexed starting at 0 so the last index is always 1 lower than the count. So if there are 5 items in the list Count returns 5 but the actual index numbers are 0,1,2,3,4 so when the loop gets to 5 you will get index out of bounds. You would also be skipping the first item in the list since your loop starts at 1

For nI = 0 To ListView2.ListItems.Count -1

Is the correct way to do this

[techgnome]

Close... it should be

For nI = 0 To ListView2.ListItems.Count - 1

If there are 10 items in the list .Count will equal 10, but the index is 0-based so it runs from 0 to 9 ... which is why it needs to be 0 and .Count - 1 .... otherwise 1) you'll never reach that first item and 2) you exceed its bounds.

-tg

[DataMiser]

I did not notice the loop was starting at 1 intitally but I did go back and edit before I saw your post

[techgnome]

To be fair, I was actually replying to Tyson's post when I wrote that... seems the quick quote button doesn't click the "Include quote in reply" checkbox and I keep forgetting to check it.... :P

-tg

[dexjel140503]

sorry for the late reply. i tried your all your advices but still i get the same error.. i managed to change the code a bit to follow your advice.. currently i have this:

Set rs = New ADODB.Recordset
For nI = 0 To ListView2.ListItems.Count - 1
If ListView2.ListItems(nI).Checked = True Then 'here is where i get the error guys
rs.Open "Select * from sendtbl where empid like '%" & ListView2.ListItems(nI).SubItems(4) & "%'", cn, 3, 2
rs.Delete
rs.Update
rs.Close
Set rs = Nothing

Call load
End If

Next nI

i also tried changing the nI to 1 and it doesn't get the error but when i click the button it only "UNCHECKED" the records which i have checked.

[Max187Boucher]

Set rs = Nothing

you are setting rs to nothing and then going to next item . Remove that put it after Next NI
And for your selected items if it unchecks them you might have to make a function that will remember all your selected items

[dexjel140503]

thanks for the advice max but i dont think it is in that part, something's wrong in the indexing. i managed to overcome the error of unchecking the checked items but now although it's deleting a record, it only deletes one record per one click not multiple records in one click.

[DataMiser]

Sorry, the Listview actually uses a index base of 1 for the listitems in VB6 so this should have been fine

For nI = 1 To ListView2.ListItems.Count

Using the code we gave will give you an error when it tries to process item 0 as it would not exist.

I do see a few issues in that little block though.
For example why are you calling Load and what does it do? Does it by chance reload the listview? Is so then that is the source of your problem because once you have deleted an item from the list then there are fewer items in the list but your code is trying to process the original number of items and thus throws an index out of bounds error.

You should do a reverse loop if you are going to be removing items to avoid such errors

For nI = ListView2.ListItems.Count to 1 Step -1

Also it is bad practice to Open a recordset, call delete, call update then close not to mention you are destroying the recordset object but may try to access it again if more than one item is checked which would result in yet another error not to mention the wasted time selecting, loading when all you are doing is deleting records.

Would be better to move the Call Load out of the loop, Do away with all the RS stuff there and simply execute a delete query when needed. Would be faster, neater and more multiuser friendly.

[DataMiser]

In keeping with what you have you could try

Set rs = New ADODB.Recordset
For nI = ListView2.ListItems.Count to 1 step -1
    If ListView2.ListItems(nI).Checked = True Then 'here is where i get the error guys
        rs.Open "Select * from sendtbl where empid like '%" & ListView2.ListItems(nI).SubItems(4) & "%'", cn, 3, 2
        rs.Delete  ' If more than one record was returned by the query this will delete only the first record
        'rs.Update ' This statement is useless
        rs.Close
        Call load ' Probably should be moved to after the Next NI, no point in reloading the data over and over
   End If

Next nI
Set rs = Nothing

This would probably be better but be aware that if more than one record matches then more than one record will be deleted

For nI = 1 to ListView2.ListItems.Count 
    If ListView2.ListItems(nI).Checked = True Then 'here is where i get the error guys
        cn.Execute "Delete from sendtbl where empid like '%" & ListView2.ListItems(nI).SubItems(4) & "%'"        
    End If
Next nI
Call load

[ryanframes]

sorry for the late reply. i tried your all your advices but still i get the same error.. i managed to change the code a bit to follow your advice.. currently i have this:

Set rs = New ADODB.Recordset
For nI = 0 To ListView2.ListItems.Count - 1
If ListView2.ListItems(nI).Checked = True Then 'here is where i get the error guys
rs.Open "Select * from sendtbl where empid like '%" & ListView2.ListItems(nI).SubItems(4) & "%'", cn, 3, 2
rs.Delete
rs.Update
rs.Close
Set rs = Nothing

Call load
End If

Next nI

i also tried changing the nI to 1 and it doesn't get the error but when i click the button it only "UNCHECKED" the records which i have checked.

i also tried changing the nI to 1 and it doesn't get the error but when i click the button it only "UNCHECKED" the records which i have checked

it because ur script is tell to do it..

If ListView2.ListItems(nI).Checked = True Then 'it's declare that u want to unchecked the records that u have checked

[DataMiser]

it because ur script is tell to do it..

If ListView2.ListItems(nI).Checked = True Then 'it's declare that u want to unchecked the records that u have checked

Actually no that is not the case, that line is simply checking to see if it is checked. It is not making a change to it.

[dexjel140503]

woah! that's great men. really appreciate your help. i just made some revisions in the table fields and it all goes well.. thank you.. mwah mwah!