ListView problem: no refreshing after item deletion

I’ve spent whole 3 days to resolve a problem in my program. I still didn’t do that and therefore I decided to ask here for help.

-My program have TreeView and ListView control. After selecting an item from TreeView, program fills ListView up from database.
-ListView has right-click context menu with options for adding a copy of existing item and deleting an item from ListView and from database itself.

Everything works fine except two things:

1. When I’m deleting a listview item, it is wiped out from the database but it is still shown as a ListView item. If I change selected item from TreeView and come back to the previous one, the item from ListView is deleted. Also, if I’m working in debugging mode, everything’s working fine.

I’ve tried many things to resolve this issue without success.

2. The second problem is related with adding a copy of existing item to the database. It works fine for the first attempt of copying But, if I click new formed item with mouse, I’m getting an error: 3021: Either BOF or EOF is true.. etc etc… I’m not sure why I get this message if I only click on new item which is exactly a copy of an existing item?

Any help would be helpful
rgds


Here's a code which updates ListView from database:

VB Code:

Public Sub UpdateMainLV()
'subroutine updates ListView control from database
 
    Dim con33 As ADODB.Connection
    Dim rs33  As ADODB.Recordset
 
    Set con33 = New ADODB.Connection
    con33.CursorLocation = adUseClient
    con33.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb"
    Set rs33 = New ADODB.Recordset
 
    frmViewer.ListView1.ListItems.Clear ''==============
    
    Dim SQL33 As String
    SQL33 = "SELECT * FROM base_rec WHERE sub_cat = " & "'" & frmViewer.TreeView1.SelectedItem.Text & "'" & " AND lv_category= " & "'" & frmViewer.TreeView1.SelectedItem.Parent.Text & "' ORDER BY sub_cat DESC;"
    rs33.Open SQL33, con33, 3, 4
    rs33.MoveFirst
    
    Dim i As Integer
 
    For i = 0 To rs33.RecordCount - 1
        Set lvwItem = frmViewer.ListView1.ListItems.Add(1, , rs33.Fields!lv_category)
        lvwItem.SubItems(1) = rs33.Fields!sub_cat
        lvwItem.SubItems(2) = rs33.Fields!lvp_name
        lvwItem.SubItems(3) = rs33.Fields!comment
        lvwItem.SubItems(4) = rs33.Fields!date
        rs33.MoveNext
    Next i
 
    rs33.Resync
    rs33.Close
    con33.Close
    Set rs33 = Nothing
    Set con33 = Nothing
    
    frmViewer.ListView1.Refresh

And here's a code which calls this routine :

VB Code:

Private Sub mnulvDelete_click() '<============== BRISI LV ITEM
 
    Dim con         As ADODB.Connection
    Dim rs7         As ADODB.Recordset
 
 
    Dim SQL7 As String
    SQL7 = "SELECT * FROM base_rec WHERE lvp_name='" & CStr(lblSel.Caption) & "';"
 
    Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb"
    Set rs7 = New ADODB.Recordset
    rs7.Open SQL7, con, 3 , 4
            
    rs7.Delete
    
    rs7.Update
    rs7.Resync
    rs7.Close
 
 
    Dim indx As Integer
    Dim lvrows As Integer
 
    lvrows = ListView1.ListItems.Count
    For indx = 1 To lvrows
        If indx > lvrows Then Exit For
 
        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            ListView1.ListItems.Remove (indx)
            indx = indx - 1
            lvrows = lvrows- 1
        End If
   Next
 
Call frmKV.UpdateMainLV
 
End Sub

For the 1st problem, you are querying your DB for lblSel.Caption, but in the listview you are looking for lblKliknutoNa.Caption. If they are not the same, you either won't delete anything or delete the wrong thing from the listview

For the 2nd problem, I am just assuming your recordset doesn't include the new item. You are clicking on the new item which isn't in the current recordset? You may need to refresh the recordset. If still a problem, we'd probably need to see code that selects items in the db based on listivew selection

LaVolpe, thank you for the response!
I'll try to explain a bit

Quote:

---

For the 1st problem, you are querying your DB for lblSel.Caption, but in the listview you are looking for lblKliknutoNa.Caption. If they are not the same, you either won't delete anything or delete the wrong thing from the listview

---

lblSel.Caption ih sere instead of a global variable. It contains a ListView subitem value which is used for selecting of item which has to be deleted.
I've already told that this code works, but it works only if you change TreeView selected item to some other TV item and then move back to the item which selects LV items on the place where we previously deleted our LV item. It also works when we are in debugging mode. A bit strange, isn't it?

Quote:

---

For the 2nd problem, I am just assuming your recordset doesn't include the new item. You are clicking on the new item which isn't in the current recordset? You may need to refresh the recordset. If still a problem, we'd probably need to see code that selects items in the db based on listivew selection

---

Subroutine for creating of a copy is quite similar to the routine for deleting

VB Code:

Private Sub mnulvCopy_click()  '<============== COPY A LV ITEM
 
    Dim con          As ADODB.Connection
    Dim rs6          As ADODB.Recordset
    Dim rs6pom       As ADODB.Recordset
 
    Dim i            As Integer
 
 
    Dim SQL6 As String
    SQL6 = "SELECT * FROM baza_receptura WHERE lvp_name ='" & CStr(lblSel.Caption) & "';"
    Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb"
    Set rs6 = New ADODB.Recordset
    rs6.Open SQL6, con, 3 , 4
        
            
    Set rs6pom = rs6.Clone
    rs6pom.AddNew
            
    For i = 1 To rs6.Fields.Count - 1
        rs6pom(i) = rs6(i)
    Next i
            
    Dim lenstr As Integer
    lenstr = Len(rs6.Fields(4).Value)
    
 
  
    If InStr(lenstr - 7, rs6.Fields(4).Value, " (copy)", vbTextCompare) = 0 Then
    rs6pom.Fields(4).Value = rs6.Fields(4).Value & " (copy)"
 
    rs6pom.Update
    rs6pom.Close
    
    Else
    MsgBox "Only one instance allowed!"
    End If
 
    rs6.Update
    rs6.Resync
    rs6.Close
 
    Call frmKV.UpdateMainLV

We are cloning selected LV item to a new one. That's the main difference.

As you can see, there's no code for selecting LV items (that code is working fine). This subroutine creates a new DB record and invites frmKV.UpdateMainLV suboutine (shown above) which reads records again and create ListView items. It works but as I said, if you make selection with mouseclick it shows an error that we reached BOF or EOF.

Quote:

---

Originally Posted by Goshx

LaVolpe, thank you for the response...

I've already told that this code works, but it works only if...

---

If it only works in some cases, then your code does not work. When something works sometimes and not all times, you have a logic bug.

Looking at your code in post #1, the only way a listview item will be deleted is if .SubItems(2) matches that lblKliknutoNa.Caption label. So, I'd assume it doesn't match which means you are changing it elsewhere or it doesn't hold the value you are expecting

P.S. Your loop can be less complicated by doing it in reverse. You don't have to keep track of index or count and other code you added to avoid errors within the loop

Code:

---

For indx = ListView1.ListItems.Count To 1 Step -1
...
Next

---

.SubItems(2) always matches. That's for sure. It gets value from LV and ask for a record in my database.

I only can't understand why LV control make changes (in the cese of deletion) after we change focus on some other data and after that we come back. This is a kind of mystery for me. Why not immediately? Why after a "walk"? Why it works during debugging?

That only means that LV isn't refreshed correctly. But I honestly don't know how to refresh it on some other way.





Second problem (with copying)

Maybe there is some confusion on my part? Which listview is not "updating" correctly? I may have been concentrating on the wrong code portion

In your mnulvDelete_Click event, having deleted a record from the Table, you are going to all the trouble of deleting the item from the listview and then completely refreshing the ListView in UpdateMainLV which seems to be a waste of time. I suspect you should do one or the other not both.

EDIT: Just read LaVolpe's last post; I am also assuming that there is only 1 Listview involved, as per your original post - is that a correct assumption?

Also, I would use the SQL 'DELETE FROM' statement.
This code

Code:

---

    SQL7 = "SELECT * FROM base_rec WHERE lvp_name='" & CStr(lblSel.Caption) & "';"

    Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb"
    Set rs7 = New ADODB.Recordset
    rs7.Open SQL7, con, 3 , 4
           
    rs7.Delete
   
    rs7.Update
    rs7.Resync
    rs7.Close

---

can be replaced by

Code:

---

    Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db.mdb"
    SQL7 = "DELETE FROM base_rec WHERE lvp_name='" & CStr(lblSel.Caption) & "';"
    con.Execute SQL7

---

and then use LaVolpe's suggestion for locating the listview item(s) to delete or just re-populate the ListView.

EDIT 2: Your original code to delete will only delete the first record returned from the SELECT, in the case of a 'copied' record, since you're not using an 'ORDER BY' clause the first record could be the original or the copy - this depends entirely on the mood of the Database System, one day it might be the original and on another the copy. The DELETE FROM I am suggesting will delete all records matching the selection criteria, so it may or may not be what you want.

Picking up on the point made by LaVolpe, you should be consistant with your selection criteria. In one routine you are using the selected treeview item and in the other you're using the contents of a label. If those two do get out of step you're going to have all sorts of problems. Why run the risk? just use one or the other.

As for creating a copy of an existing record, I'm not sure what value cloning the recordset is adding. The cloning process effectively creates a 'linked copy' of the recordset and its contents, you are then doing it manually in a For / Next loop. Why not just add a copy of the record to the original recordset? I suspect your second problem is associated with closing the cloned recordset which I believe will also close the recordset it was cloned from, thus when you try to perform the Update on the original recordset it has been closed, giving you the error.

There also seems to be a logic problem in the copying process. You are assuming that only one record will be returned from the selection, whereas later on you are checking whether the record is already a copy. Since you're not using an ORDER BY clause, you're in danger of creating multiple copies of the same record. eg you may have two records thus:
.... .... abcd efgh
.... .... abcd efgh (copy)
you will then test the first record to see if it's a copy; as it isn't you'll add .... .... abcd efgh (copy) again bacause you're not checking if more than one record has been returned.

Well, if someone is interested in, I can send source on e-mail address (plz write me on PM). Many things which you mentioned above is more the question of syntax. I know, it is a bit hard to discuss about some topic if you can't see the whole program.

So, the best solution is to see the whole program (more precise, it is just a part of a bigger program) and assure yourself how it is working. After that we can publish working code which can help to the people with a similar problem.

Doogle

I've tried your suggestions with ORDER BY, didn't help ((((

I still think that the problem is with the database, not with ListView itself. Program works with only one record in the recordset and possibly that produces errors after adding or deleting. I'm not quite sure how to solve this problem because I didn't work with only one record before. EOF is triggered because of that.

I already told, it is impossible o solve this problem without to se how it works. Therefore I uploaded source for the module together with its database.

http://www.mediafire.com/?73mjm9911kcz2j2

I ho[e that we'll finally be able to find that stupid bug.

@Goshx - I am not sure why you decided to PM me for assistance!

You have two highly invested people assisting you already - and they are asking questions and making suggestions but seems you aren't "in tune" with what they are saying.

Are you comfortable using DEBUG and stepping through code - examining your variables and what not?

Have you ever used DEBUG.PRINT to write execution trace details to the debug window to assist you in seeing what's happening as your code runs?

Not too many people are willing to download someones project and run it - we are all pretty much interested in seeing a code snippet that isn't working and helping you get that working.

You have a lot of debugging and tracing to do yourself IMO...

As pointed out earlier one of your problems is here

Code:

---

    SQL7 = "SELECT * FROM base_rec WHERE lvp_name='" & CStr(lblSel.Caption) & "';"

 
        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            ListView1.ListItems.Remove (indx)

---

You are deleting an item from the database based on one critera and then removing an item from the list view based on another. The item does not dissappear from the list because you must not be using the right value. It dissappears after the listview has been reloaded because it no longer exists in the database.

As to the second problem I do not see any code for when you click on the listview so I am not sure what could be going on there.

A few tips:
There is no need to move to the first record after opening a recordset. It will be at the first record already.

When looping through an entire recordset rather than

Code:

---

    For i = 0 To rs33.RecordCount - 1
       
        rs33.MoveNext
    Next i

---

Use

Code:

---

Do While Not rs33.EOF
    rs33.MoveNext
Loop

---

Recordcount is not available in some cases. The loop above will always work, will be slightly faster and will use slightly less memory.

You do not need to create 33 different connection and recordset objects. From the looks of things you are only using these recordsets locally which means you could use the same name wihtout needing to add the numbers on each.

No point in calling resync in most of these cases especially when you are closing the recordset on the next line.

No need to call .Update after a .Delete .Update is used to add or edit a record.

When setting a connection string better to create a public variable and set the string once in your form load then reference the variable everywhere else. This way if for any reason you need to change the target db name or location you can do it in one place rather than having to change it 33 times or more times.

Quote:

---

Originally Posted by szlamany

@Goshx - I am not sure why you decided to PM me for assistance!

You have two highly invested people assisting you already - and they are asking questions and making suggestions but seems you aren't "in tune" with what they are saying.

Are you comfortable using DEBUG and stepping through code - examining your variables and what not?

Have you ever used DEBUG.PRINT to write execution trace details to the debug window to assist you in seeing what's happening as your code runs?

Not too many people are willing to download someones project and run it - we are all pretty much interested in seeing a code snippet that isn't working and helping you get that working.

You have a lot of debugging and tracing to do yourself IMO...

---

I've decided to ask you for help because this problem stays unsolved for more than a month.

It is impossible to solve this without to see whole code. All of us like snippets but sometimes is impossible to find a bug without to see all code.

I've spent a lot of time debugging and after that I decided to send my problem here. Therefore, I apologize myself to you again because I disturbed you.
The rest of your advices are something very abstract. If all of us debug our programs until we find the solution, this forum wouldn't exist. Bad thing is that we can spent much of time for that.

Quote:

---

Originally Posted by DataMiser

As pointed out earlier one of your problems is here

Code:

---

    SQL7 = "SELECT * FROM base_rec WHERE lvp_name='" & CStr(lblSel.Caption) & "';"

 
        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            ListView1.ListItems.Remove (indx)

---

You are deleting an item from the database based on one critera and then removing an item from the list view based on another. The item does not dissappear from the list because you must not be using the right value. It dissappears after the listview has been reloaded because it no longer exists in the database.

---

DataMiser, thank you very much in any case
!
I'll try to concentrate myself on this part of code. The problem which you showed isn't a real problem. I simply translated some variables in English to make thing more simple. In the full project both variables are assigned to lblKliknutoNa.Caption value.

Quote:

---

Originally Posted by Goshx

...I've spent a lot of time debugging and after that I decided to send my problem here...

---

Does that mean you STEP through your code one line at a time and look at the variables in the IMMEDIATE window?

Just want to make sure we don't have a language issue here.

OMG... sounds funny but when I changed for..next loop with this:

vb Code:

Do While Not rs33.EOF
     rs33.MoveNext
Loop

errors related with the EOF disappeared!! I didn't know that Recordcount can make such kind of problems. )))

The only problem which still remains is related with refreshing of deleted items in listview.
As I told before.... if you click with mouse on some another treeview item and go back, deleted item doesn't more exist.

Your suggestion about syntax in my code are good but I already know that. Various rsXX names for recordsets are made intentionally because of readability of code. When I finish with bugs, I'll clear the unnecessary things.

no....... they didn't disappear.... unfortunately. In the first moment it looked like that. In any case, this loop work better now.

Quote:

---

Originally Posted by szlamany

Does that mean you STEP through your code one line at a time and look at the variables in the IMMEDIATE window?

Just want to make sure we don't have a language issue here.

---

Dear friend, I did that many times before I decided to ask for the help here. The program generates error with EOF and I can't catch why.....

In reference to the 1st issue. You need to post the actual code that you are using if you want us to see the problem. In the code you posted you are referencing 2 different things and that would be a problem.

When you execute a remove from the list the object should be removed then and there. If it is still there after the statement has completed that indicates that you have did something wrong. Put a break point on that line where the remove takes place and see if the line executes. If it does not then that means no match was found. If it does get to this line check the value of your indx var and see if it is correct.

Here's the actual source code

http://www.mediafire.com/?26zu52rnb12kgst

It is impossible to understand the problem while you can't see how the program works.... I can send many posts here without any results

You have to play a little bit with context menu in list view to realize whats' going on

I'm not really interested in downloading the project from mediafire. Simply post the real code for the second portion that is included in your first post. i.e. The routine where the delete and remove is actually done.

Also you need to do as I suggested in my previous post and let us know the results.

vb Code:

Option Explicit
 
 
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs22 As ADODB.Recordset
 
Private sngListViewX As Single
Private sngListViewY As Single
 
Dim nod As Node
Dim lvwItem As ListItem
Dim lvwItem2 As ListItem
 
'------------------------------------------------------------
 
 Private Const LVM_FIRST As Long = &H1000
 Private Const LVM_HITTEST As Long = (LVM_FIRST + 18)
 Private Const LVM_SUBITEMHITTEST As Long = (LVM_FIRST + 57)
 Private Const LVHT_ONITEMICON As Long = &H2
 Private Const LVHT_ONITEMLABEL As Long = &H4
 Private Const LVHT_ONITEMSTATEICON As Long = &H8
 
 Private Const LVHT_ONITEM As Long = (LVHT_ONITEMICON Or _
                                     LVHT_ONITEMLABEL Or _
                                     LVHT_ONITEMSTATEICON)
 Private Type POINTAPI
   X As Long
   Y As Long
 End Type
 
 Private Type LVHITTESTINFO
    pt As POINTAPI
    flags As Long
    iItem As Long
    iSubItem  As Long
 End Type
 
 Dim lX As Single, lY As Single
 
 Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
 
'--------------------------------------------------------------------
 
 
 
Private Sub listView1_MouseUp(Button As Integer, Shift As _
    Integer, X As Single, Y As Single)
 
            Dim HTI As LVHITTESTINFO
    With HTI    '============================
        .pt.X = (lX \ Screen.TwipsPerPixelX)
        .pt.Y = (lY \ Screen.TwipsPerPixelY)
        .flags = LVHT_ONITEM
    End With
    
    Call SendMessage(ListView1.hwnd, LVM_SUBITEMHITTEST, 0, HTI)
 
 Dim lst As ListItem
    If (HTI.iItem > -1) Then
        Set lst = ListView1.ListItems(HTI.iItem + 1)
 
 
    lblKliknutoNa.Caption = frmViewer.ListView1.ListItems(HTI.iItem + 1).SubItems(2)
    lblKliknutoNa2.Caption = "item " & HTI.iItem + 1 & " i SubItem " & HTI.iSubItem
    
    End If
    ' =================
    sngListViewX = X 
    sngListViewY = Y 
 
    If ListView1.ListItems.Count > 0 Then 
  If Button = vbRightButton Then
    If Not (ListView1.SelectedItem Is Nothing) Then
      If Not (ListView1.HitTest(X, Y) Is Nothing) Then
        ListView1_Click
        PopupMenu popMNU2
      Else
      End If
    End If
    popMNU2.Enabled = False
  End If
 End If
    
    
End Sub
 
 
 
Private Sub cmdDodajNovu_Click()
    frmDodaj_u_stablo.Show vbModal
End Sub
 
Private Sub Form_Load()  '=======================
 
    Dim SQL As String
    SQL = "SELECT DISTINCT kategorija FROM kategorije"
Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\normativi.mdb"
Set rs = New ADODB.Recordset
    rs.Open SQL, con, 3, adLockOptimistic
Call FormirajStablo
 
ListView2.Visible = False
 
cmdDodajNovu.Top = frmViewer.Height - 1600
cmdUDaljiKat.Top = frmViewer.Height - 1600
TreeView1.Height = frmViewer.Height - 2100
End Sub
'===========================================================
 
Private Sub FormirajStablo()    '======================= create TreeView1 tree
 
Dim i As Integer
Set nod = TreeView1.Nodes.Add(, , "Recepti", "Baza receptura") 
 
rs.MoveFirst
For i = 0 To rs.RecordCount - 1
    With TreeView1.Nodes
        'rezultat stavljamo u treeview
        Set nod = .Add("Recepti", 4, rs.Fields("kategorija"), rs.Fields("kategorija"))
    End With
rs.MoveNext
Next i
rs.Close
Call UcitajStablo
Set nod = Nothing
End Sub
 
'===========================================================
 
Private Sub UcitajStablo()  '======================= load data into TreeView1
Dim SQL As String
SQL = "SELECT * From kategorije"
rs.Open SQL, con, 3, 4
Dim i As Integer
 
Dim nodtekst, a, bazni As String
rs.MoveFirst
For i = 0 To rs.RecordCount - 1
    With TreeView1.Nodes
        bazni = rs.Fields!kategorija
        nodtekst = rs.Fields!podkategorija 
            Set nod = .Add(bazni, 4, nodtekst & " " & a & rs.Fields!Id, nodtekst & " " & a) 
    End With
rs.MoveNext
Next i
rs.Close
Set nod = Nothing
End Sub
 
'===========================================================
 
Private Sub MenuQuit_Click()
Unload Me
End Sub
 
Private Sub TreeView1_Click()   '=======================
With ListView1.ListItems
    .Clear
End With
 
If TreeView1.SelectedItem.Text = "Baza receptura" Then
Exit Sub
    ElseIf TreeView1.SelectedItem.Parent.Text = "Baza receptura" Then
    Exit Sub
Else
    FormirajListView
End If
 
 
End Sub
 
'===========================================================
'===========================================================
 
Public Sub FormirajListView()   '======================= Create ListView1
 
    Dim i   As Integer
    Dim SQL As String
    ListView1.ListItems.Clear
    SQL = "SELECT * FROM baza_receptura WHERE pod_kategorija = " & "'" & TreeView1.SelectedItem.Text & "'" & " AND za_zivotinju = " & "'" & TreeView1.SelectedItem.Parent.Text & "' ORDER BY pod_kategorija DESC;"
 
    rs.Open SQL, con, 3, 4
 
    If rs.RecordCount = "0" Then
        'MsgBox "Recepture doesn't exist fot this category"
        Beep
        rs.Close
        Exit Sub
    Else
 
        Do While Not rs.EOF
            Set lvwItem = ListView1.ListItems.Add(1, , rs.Fields!za_zivotinju)
            lvwItem.SubItems(1) = rs.Fields!pod_kategorija
            lvwItem.SubItems(2) = rs.Fields!nazvanje
            lvwItem.SubItems(3) = rs.Fields!komentar
            lvwItem.SubItems(4) = rs.Fields!datum
            rs.MoveNext
        Loop
 
    End If
    rs.Close
End Sub
 
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ListView1_Click()   '=======================
    
    ListView2.ListItems.Clear
    
    Dim lListItem   As ListItem
    Dim string2klik As String
 
    Set lListItem = ListView1.HitTest(sngListViewX, sngListViewY)
 
    If (lListItem Is Nothing) Then
        Exit Sub 'MsgBox "Niste nista izabrali"
    Else
        string2klik = lListItem.SubItems(2)
        frmViewer.lblPrenos = string2klik
  
        Set rs2 = New ADODB.Recordset
        Dim sql2 As String
 
        sql2 = "SELECT * FROM baza_receptura  WHERE nazvanje = " & Chr$(34) & string2klik & Chr$(34)
        rs2.Open sql2, con, adOpenDynamic, adLockOptimistic
        'rs2.MoveFirst ' 
        txtNazvanje.Visible = True
        txtNaziv.Text = rs2.Fields(4).Value '>>>DEBUGGER SHOWS EOF ERROR HERE!<<<
        txtNaziv.Visible = True
        txtKomentar.Text = rs2.Fields(5).Value
        txtKomentar.Visible = True
        txtRezultat.Text = ""
 
        Dim X           As Integer
        Dim lvindeks As Integer
        lvindeks = 1
        For X = 8 To rs2.Fields.Count - 1
        'popunjavanje male lv kontrole
            If rs2.Fields(X).Value > 0 Then
 
                'Set lvwItem = ListView2.ListItems.Add(1, , rs2.Fields(3))
                Set lvwItem2 = ListView2.ListItems.Add(lvindeks)
                lvwItem2.SubItems(1) = rs2.Fields(X).Name
                lvwItem2.SubItems(2) = CStr(rs2.Fields(X).Value)
                'lvwItem2.SubItems(3) = CStr(lvindeks)
            lvindeks = lvindeks + 1
            Else
            End If
        Next X
        ListView2.Visible = True
 
        rs2.Close
 
    End If
    Set lListItem = Nothing
    
 
End Sub
 
 
Private Sub ListView1_DblClick()    '=======================
 
Call ListView1_Click
 
    Dim lListItem   As ListItem
    Dim string2klik As String
 
    Set lListItem = ListView1.HitTest(sngListViewX, sngListViewY)
 
 
        string2klik = lListItem.SubItems(2)
        frmViewer.lblPrenos = string2klik
  
        Set rs22 = New ADODB.Recordset
        Dim sql2 As String
 
        sql2 = "SELECT * FROM baza_receptura  WHERE nazvanje = " & Chr$(34) & string2klik & Chr$(34)
        rs22.Open sql2, con, adOpenDynamic, adLockOptimistic
        
        txtNazvanje.Visible = True
        txtNaziv.Text = rs22.Fields(4).Value
        txtNaziv.Visible = True
        txtKomentar.Text = rs22.Fields(5).Value
        txtKomentar.Visible = True
        txtRezultat.Text = ""
 
        Dim X           As Integer
        Dim stringic    As String
        Dim naziv_polja As String
        Dim razmak      As String
        razmak = Chr$(9) & Chr$(9) & Chr$(9)
 
        For X = 8 To rs22.Fields.Count - 1
            
            If rs22.Fields(X).Value > 0 Then
 
                stringic = txtRezultat.Text
                txtRezultat.Text = stringic & rs22.Fields(X).Name & razmak & rs22.Fields(X).Value & vbCrLf
              
            Else
            End If
 
        Next X
 
        txtRezultat.Visible = True
        frmDvaKlika.Show vbModal
        rs22.Close
 
End Sub
 
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
Private Sub TreeView1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
 On Error Resume Next
 If Button = 2 Then 
  Dim hItem As Long, nod As Node, xRect As RECT, xPop As Integer, yPop As Integer
  Set nod = TreeView1.HitTest(X, Y)
  hItem = GetTVItemFromNode(TreeView1.hwnd, nod)
  If hItem Then
   TreeView_GetItemRect TreeView1.hwnd, hItem, xRect, CTrue
 
   xPop = TreeView1.Left + ScaleX(xRect.Left, vbPixels, vbTwips)
   yPop = TreeView1.Top + ScaleY(xRect.Bottom, vbPixels, vbTwips)
   PopupMenu popMNU, , xPop, yPop
  End If
 End If
End Sub
 
 
 Private Sub ListView1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    lX = X
    lY = Y
 End Sub
 
Private Sub mnulvPreimenuj_click()
    frmNovoIme.Show
End Sub
 
Private Sub mnulvKopiraj_click()  '<============== COPY LV ITEM
 
    Dim con          As ADODB.Connection
    Dim rs6          As ADODB.Recordset
    Dim rs6pom       As ADODB.Recordset
 
    Dim i            As Integer
 
 
    Dim SQL6 As String
    SQL6 = "SELECT * FROM baza_receptura WHERE nazvanje ='" & CStr(lblKliknutoNa.Caption) & "';"
    Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\normativi.mdb"
    Set rs6 = New ADODB.Recordset
    rs6.Open SQL6, con, adOpenDynamic, adLockOptimistic
        
            
    Set rs6pom = rs6.Clone
    rs6pom.AddNew
            
    For i = 1 To rs6.Fields.Count - 1
        rs6pom(i) = rs6(i)
    Next i
            
    Dim lenstr As Integer
    lenstr = Len(rs6.Fields(4).Value)
    
    Dim kopi_tekst As String
    'kopi_tekst = rs6.Fields(4).Value
    If InStr(lenstr - 9, rs6.Fields(4).Value, " (copy)", vbTextCompare) = 0 Then
    rs6pom.Fields(4).Value = rs6.Fields(4).Value & " (copy)"
 
    rs6pom.Update
    rs6pom.Close
    
    Else
    MsgBox "ONLY ONE INSTANCE ALLOWED"
    End If
 
    rs6.Resync
    rs6.Close
 
    Call frmDvaKlika.apdejtujGlavniLV
    
End Sub
 
Private Sub mnulvUdaljiti_click() '<============== DELETE LV ITEM
 
    Dim con         As ADODB.Connection
    Dim rs7         As ADODB.Recordset
 
 
    Dim SQL7 As String '??
    SQL7 = "SELECT * FROM baza_receptura WHERE nazvanje ='" & CStr(lblKliknutoNa.Caption) & "';"
    'SQL7 = "DELETE FROM baza_receptura WHERE nazvanje ='" & CStr(lblKliknutoNa.Caption) & "';"
    Set con = New ADODB.Connection
    con.CursorLocation = adUseClient
    con.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\normativi.mdb"
    Set rs7 = New ADODB.Recordset
    rs7.Open SQL7, con, adOpenDynamic, adLockOptimistic
            
    rs7.Delete
    
    rs7.Resync
    rs7.Close
 
    Dim indx As Integer
    Dim lvredova As Integer
 
    lvredova = ListView1.ListItems.Count
    'For indx = ListView1.ListItems.Count To 1 Step -1
    For indx = 1 To lvredova
        If indx > lvredova Then Exit For
                                            ' 
        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            ListView1.ListItems.Remove (indx)
            indx = indx - 1
            lvredova = lvredova - 1
        End If
   Next
 
 
Call frmDvaKlika.apdejtujGlavniLV
 
End Sub

vb Code:

Public Sub apdejtujGlavniLV()
 
    Dim con33 As ADODB.Connection
    Dim rs33  As ADODB.Recordset
 
    Set con33 = New ADODB.Connection
    con33.CursorLocation = adUseClient
    con33.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\normativi.mdb"
    Set rs33 = New ADODB.Recordset
 
    frmViewer.ListView1.ListItems.Clear ''==============
    
    Dim SQL33 As String
    SQL33 = "SELECT * FROM baza_receptura WHERE pod_kategorija = " & "'" & frmViewer.TreeView1.SelectedItem.Text & "'" & " AND za_zivotinju = " & "'" & frmViewer.TreeView1.SelectedItem.Parent.Text & "' ORDER BY pod_kategorija DESC;"
    rs33.Open SQL33, con33, 3, 4
 
    
    Do While Not rs33.EOF
        Set lvwItem = frmViewer.ListView1.ListItems.Add(1, , rs33.Fields!za_zivotinju)
        lvwItem.SubItems(1) = rs33.Fields!pod_kategorija
        lvwItem.SubItems(2) = rs33.Fields!nazvanje
        lvwItem.SubItems(3) = rs33.Fields!komentar
        lvwItem.SubItems(4) = rs33.Fields!datum
        rs33.MoveNext
    Loop
 
    'rs33.Resync
    rs33.Close
    con33.Close
    Set rs33 = Nothing
    Set con33 = Nothing
    
    frmViewer.ListView1.Refresh
    
End Sub

That's the main logic of this module. There's a lot of code for context menu handling but the rest is devoted to TreeView and ListView control.

So did you set the breakpoint like I said before? If so what happened? If not then you should do so and let us know the results.

I am curious why in the code above in one case you are using CSTR() and then in the other using TRIM() There is no point whatsoever in using CSTR() on a caption since caption is a string already even if it contains a numeric value. The trim on the other hand would be a problem if there are any leading or trailing spaces in the listview subitem you are testing.

Also like I mentioned before there is no reason to use .resync

Line 188 in Post#23

Code:

---

If rs.RecordCount = "0" Then

---

might give you problems as it will never = "0". (I think I'm surprised that you don't get a run-time error 13)
It should be:

Code:

---

If rs.RecordCount = 0 Then

---

txtNaziv.Text = rs2.Fields(4).Value

This line (231) generates error related with EOF

Doogle
There's a lot of crap code here, don't pay attention on that for now.

thread is too large for my wee brain this morning..

(1) have you solved the listbox not showing the deletion straight away yet?
if not have you tried simply refreshing the listbox directly after your command to delete the entry.

like listbox.refresh or refresh listbox which ever works in your version

(2) have you stuffed you code full of stops and/or msgboxes to access the condition of the data at all times during the process to track down the problem?

here to help

Quote:

---

Originally Posted by incidentals

thread is too large for my wee brain this morning..

(1) have you solved the listbox not showing the deletion straight away yet?
if not have you tried simply refreshing the listbox directly after your command to delete the entry.

like listbox.refresh or refresh listbox which ever works in your version

(2) have you stuffed you code full of stops and/or msgboxes to access the condition of the data at all times during the process to track down the problem?

here to help

---

:) You didn't sleep well last night, I can see
Not a listbox but listview.
1) Yes. That line exists (33)
2) Many things I already did without success. As the result of that you can see meny stupid commands which still exists in my code )))) Many desperate attempts to do something, that's it.

In Posts 20, 22 amd 26. I have asked that you set a break point and see if the code executes a given line and if so check the value of your vars at that point then let us know what the result is. You have not even acknowledged this.

This is something you need to do if you want to get your project working.

DataMiser
Maybe you didn't see but in the post #28 i wrote that code in line 238 generates EOF error.
Obviously, clicking on ListView, it checks database and try to fil txtNaziv.Text = rs2.Fields(4).Value but without success.

I saw it but that is not the issue I was addressing and not what I asked you to provide. I can not help if you ignore 1/2 of what I say.

As for the EOF error this should never happen as you should be checking to make sure the record exists before you try to assign values from it.

Quote:

---

Originally Posted by DataMiser

I saw it but that is not the issue I was addressing and not what I asked you to provide. I can not help if you ignore 1/2 of what I say.

As for the EOF error this should never happen as you should be checking to make sure the record exists before you try to assign values from it.

---

I'm trying to be as much cooperative as I can. I appreciate your good will to help me but I can't understand what you wanted from me in posts #20 and #22.

I've copied the whole source related with these problems. Also, I wrote where one of my problems is placed. The second problem with ListView refresh I can't locate at all. Even with the help of debugger.

Code:

---

    For indx = 1 To lvredova
        If indx > lvredova Then Exit For
                                            '
        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
          ListView1.ListItems.Remove (indx)
            indx = indx - 1
            lvredova = lvredova - 1
        End If
  Next

---

You need to set a breakpoint on the line I have marked in red then run your program and try to remove an item from the list view. If it does not hit the break point then that is a problem. If it does hit the break point then check the value of your subitems(s) and your indx vars as see if they look correct for the item you are trying to delete.

Let us know the result.

As for your other error it means that what you are searching for returned no results. You should always check and see if your query returned results before trying to read the fields of the query. Either you would use recordcount as I have here or test for EOF before accessing the fields.

Code:

---

  sql2 = "SELECT * FROM baza_receptura  WHERE nazvanje = " & Chr$(34) & string2klik & Chr$(34)
        rs2.Open sql2, con, adOpenDynamic, adLockOptimistic
      if rs2.recordcount>0 then
            txtNazvanje.Visible = True
            txtNaziv.Text = rs2.Fields(4).Value '>>>DEBUGGER SHOWS EOF ERROR HERE!<<<
            txtNaziv.Visible = True
            txtKomentar.Text = rs2.Fields(5).Value
            txtKomentar.Visible = True
            txtRezultat.Text = ""
      Else
            Msgbox "Record not found"
      End If

---

Quote:

---

Originally Posted by DataMiser

Code:

---

    For indx = 1 To lvredova
        If indx > lvredova Then Exit For
                                            '
        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
          ListView1.ListItems.Remove (indx)
            indx = indx - 1
            lvredova = lvredova - 1
        End If
  Next

---

You need to set a breakpoint on the line I have marked in red then run your program and try to remove an item from the list view. If it does not hit the break point then that is a problem. If it does hit the break point then check the value of your subitems(s) and your indx vars as see if they look correct for the item you are trying to delete.

Let us know the result.

---

The breakpoint is triggered and listitem is removed correctly. It is some kind of mystery. During debugging everything is working fine ))))
No errors shown.

What does this line do?

Code:

---

Call frmDvaKlika.apdejtujGlavniLV

---

Quote:

---

Originally Posted by Goshx

The breakpoint is triggered and listitem is removed correctly. It is some kind of mystery. During debugging everything is working fine ))))
No errors shown.

---

Then don't break - but do this instead...

Put a DEBUG.PRINT statement below the line indicated...

Code:

---

        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            Debug.Print indx
            Debug.Print ListView1.ListItems.Count
            ListView1.ListItems.Remove (indx)
            Debug.Print ListView1.ListItems.Count
            indx = indx - 1
            lvredova = lvredova - 1
        End If

---

what number appears in the immediate window.

Quote:

---

Originally Posted by DataMiser

What does this line do?

Code:

---

Call frmDvaKlika.apdejtujGlavniLV

---

---

it calls subroutine for updating ListView1. I've sent the source code of it in the post #24

Quote:

---

Originally Posted by szlamany

Then don't break - but do this instead...

Put a DEBUG.PRINT statement below the line indicated...

Code:

---

        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            Debug.Print indx
            Debug.Print ListView1.ListItems.Count
            ListView1.ListItems.Remove (indx)
            Debug.Print ListView1.ListItems.Count
            indx = indx - 1
            lvredova = lvredova - 1
        End If

---

what number appears in the immediate window.

---

in the case of 3 LV items it shows:

Debug.Print indx=3
Debug.Print ListView1.ListItems.Count=3

Debug.Print ListView1.ListItems.Count=2

That means, everything is fine

Quote:

---

Originally Posted by DataMiser

As for your other error it means that what you are searching for returned no results. You should always check and see if your query returned results before trying to read the fields of the query. Either you would use recordcount as I have here or test for EOF before accessing the fields.

Code:

---

  sql2 = "SELECT * FROM baza_receptura  WHERE nazvanje = " & Chr$(34) & string2klik & Chr$(34)
        rs2.Open sql2, con, adOpenDynamic, adLockOptimistic
      if rs2.recordcount>0 then
            txtNazvanje.Visible = True
            txtNaziv.Text = rs2.Fields(4).Value '>>>DEBUGGER SHOWS EOF ERROR HERE!<<<
            txtNaziv.Visible = True
            txtKomentar.Text = rs2.Fields(5).Value
            txtKomentar.Visible = True
            txtRezultat.Text = ""
      Else
            Msgbox "Record not found"
      End If

---

---

this peace of code isn't a bad idea, generally. This way we can avoid database error. But... what to put instead of Msgbox? How to force the query to return something? :)

Ok so the question is why are you calling it? The code above this call removes an item from the listview. At this point that item would be gone. I can not think of any reason you would need to reload or update the list view. The remove does that for you.

It may be possible that the code to reload the listview is getting executed before the data has actually been removed from the db and as such is being reloaded into the listview after you have removed it. When you do somethign that causes the list view to be loaded again it would be gone as the db delete has happened by then. Also when you set the break point by the time it got to the call the data was deleted from the db so it did not show in the list.

This would be my best guess but I have not studied that code. Try commenting out that line which calls the subroutine and see what happens when you run your program.

Quote:

---

Originally Posted by Goshx

this peace of code isn't a bad idea, generally. This way we can avoid database error. But... what to put instead of Msgbox? How to force the query to return something? :)

---

A query will not return something if there is nothing there to return. The question you need to ask yourself is why is it not returning anything.

Quote:

---

Originally Posted by DataMiser

Ok so the question is why are you calling it? The code above this call removes an item from the listview. At this point that item would be gone. I can not think of any reason you would need to reload or update the list view. The remove does that for you.

It may be possible that the code to reload the listview is getting executed before the data has actually been removed from the db and as such is being reloaded into the listview after you have removed it. When you do somethign that causes the list view to be loaded again it would be gone as the db delete has happened by then. Also when you set the break point by the time it got to the call the data was deleted from the db so it did not show in the list.

This would be my best guess but I have not studied that code. Try commenting out that line which calls the subroutine and see what happens when you run your program.

---

I've never thought about that, to be honest. But you're right. Removing this line of code problem with deleting is solved.
:)

I have "only" two problems yet ))))

Quote:

---

Originally Posted by DataMiser

A query will not return something if there is nothing there to return. The question you need to ask yourself is why is it not returning anything.

---

:):):)

Obviously, the parameter (variable) string2klik didn't get new value.

So where is that being set?

When I create a copy of listitem new listitem (copy) arrives at the bottom of the list. But... label which keeps value needed for string2klik keeps old value. After that, if I double click on the newly created item it shows error- but not always

Quote:

---

Originally Posted by DataMiser

So where is that being set?

---

string2klik is the same as lblKliknutoNa

here is that place:

lblKliknutoNa.Caption = frmViewer.ListView1.ListItems(HTI.iItem + 1).SubItems(2)

I'm using SubItems(2) as the source for queries

It is not the same it is a different var it has to be set somewhere, so where is string2klik being set?

line 221 and line 274 as local variables

I just went back to have a look. The code is very hard to follow. It appears you are calling the click event from the mouse up event. You are also calling it from the double click event. I haven't tested this myself but there is a chance that this could cause the click event to fire twice when you single click and 3 times when you double click.

I see where you set the variables - to subitems(2) Not sure why you are bouncing around using 3 different ways of calling the same query nor am I sure why you are calling the click event in such cases but it makes it very hard to keep track of what is going on and as a result would make it hard to locate your problem.

When your error occurs check the variables and the subitems value to see what they are at the time of the error and maybe that will help.

DataMiser

Quote:

---

I just went back to have a look. The code is very hard to follow. It appears you are calling the click event from the mouse up event. You are also calling it from the double click event. I haven't tested this myself but there is a chance that this could cause the click event to fire twice when you single click and 3 times when you double click.

---

Yas... the whole module is a little bit messy. Text in bold-italic is quite possible.

Quote:

---

I see where you set the variables - to subitems(2) Not sure why you are bouncing around using 3 different ways of calling the same query nor am I sure why you are calling the click event in such cases but it makes it very hard to keep track of what is going on and as a result would make it hard to locate your problem.

---

As I told before, there's a lot of crap code here. I'm creating database connection in every subroutine and so on... It is easy to correct later but it doesn't influent on program itself. I made some experiments and therefore we have different approaches to the same thing.

Quote:

---

When your error occurs check the variables and the subitems value to see what they are at the time of the error and maybe that will help.

---

I did that hundred of times already ))))

But in any case.... with your help I solved the problem related with correct deleting of list item. I didn't pay my attention on that call to subroutine for updating of listview control. It is solved by your help and.... thank you for that!

Quote:

---

I did that hundred of times already ))))

---

So what was the results?

Quote:

---

Originally Posted by DataMiser

So what was the results?

---

When I click slowly on newly added listitem, everything is working fine. If I do that faster, I get error on line 231.

Quote:

---

When your error occurs check the variables and the subitems value to see what they are at the time of the error and maybe that will help.

---

Quote:

---

I did that hundred of times already ))))

---

Quote:

---

So what was the results?

---

Quote:

---

When I click slowly on newly added listitem, everything is working fine. If I do that faster, I get error on line 231.

---

I know the error occurs on that line and I know it is because the query returns no result. What I do not know is what the value of those variables are at the time the error occurs which is what I was referring to when I asked what were the results. Which of course I still do not have an answer.

Quote:

---

Originally Posted by DataMiser

I know the error occurs on that line and I know it is because the query returns no result. What I do not know is what the value of those variables are at the time the error occurs which is what I was referring to when I asked what were the results. Which of course I still do not have an answer.

---

DataMiser
I appreciate your help to me (and not only to me) very much.

You didn't read (I think) at the beginning of this thread that in debugger everything is working fine. Even the previous problem (with refreshing) didn't exist while debugging. When I'm working without debugger I'm getting error at line 231 as I told a few times before.

I can check some variables (if that's needed) and write results here but I simply don't know how to do that if the bug is (obviously) related with single click/double-click routine.

To be honest, I don't have any idea where to put breakpoint in this case. If I put them, the error won't be generated. :confused:

rgds

In your previous post you said

Quote:

---

When I click slowly on newly added listitem, everything is working fine. If I do that faster, I get error on line 231.

---

So if the error occurs the code stops on that line and then you can check the value of those variables. This could go a long way to helping find the problem.

Seems this bug might be related to event-firing - maybe something is being executed twice or something to that affect.

If it's only happening "when you don't break" - but it does happen when you are running in the IDE - then do this

Code:

---

        If Trim(ListView1.ListItems(indx).SubItems(2)) = Trim(lblKliknutoNa.Caption) Then
            Debug.Print ListView1.ListItems.Count
            holdCount = ListView1.ListItems.Count
            ListView1.ListItems.Remove (indx)
            Debug.Print ListView1.ListItems.Count
            If holdCount = ListView1.ListItems.Count Then
                Stop
            End If
            indx = indx - 1
            lvredova = lvredova - 1
        End If

---

I am assuming that the "bug moment" manifests itself in the count "not being reduced" in this area of code. Basically check for that "condition" and STOP - that way you only enter a BREAK when the bug is happening.

Then you can inspect your CALL STACK and see what "route" led you to this evil condition.

Quote:

---

Originally Posted by DataMiser

In your previous post you said

So if the error occurs the code stops on that line and then you can check the value of those variables. This could go a long way to helping find the problem.

---

I beg you for patience, please. I have no idea which variables to check. If you suspect on some of them, please write and I'll do that.

I'm sure that the problem will be solved soon. I'll write in the answer of szlamany's post how I intend to do that.

string2klik = lListItem.SubItems(2) == "receptura CS-5 (kopija) i.e. the correct value
SQL2 gets that value for query i.e., the correct value

txtNaziv.Text = rs22.Fields(4).Value == receptura CS-5 i.e. incorrect (old) value contained in the listitem selected before new double click