[RESOLVED] Getting wrong recordset RecorCount-VBForums
Results 1 to 26 of 26

Thread: [RESOLVED] Getting wrong recordset RecorCount

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Resolved [RESOLVED] Getting wrong recordset RecorCount

    Hello experts

    Code:
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
     Call connect
     sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where ID = " & lvw.ListItems(I)
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
        MsgBox RS.RecordCount
        Exit For
        End If
        Next
    I 'm getting 1 as RS.RecordCount

    I only get the right RecordCount when I remove the loop and the if statement.

    Code:
    Call connect
     sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1"
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
        MsgBox RS.RecordCount
    thank you

  2. #2
    Fanatic Member
    Join Date
    Apr 2012
    Posts
    920

    Re: Getting wrong recordset RecorCount

    Your first SQL has Where ID = & lvw.ListItems(I) but your second one doesn't
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,272

    Re: Getting wrong recordset RecorCount

    how many records should you get for each ID?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    thank you gentelmen for your interest
    I want to retrieve records who are checked in the listview.
    For example if I have 10 records checked in the listview, I should get RS.RecordCount = 10 when I ran this query:
    Code:
    sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where ID = " & lvw.ListItems(I)
    thanks

  5. #5
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,391

    Re: Getting wrong recordset RecorCount

    But you are running it EACH time...you should (if 10 records are Selected in your Listview) see 10 messageboxes. Each one will have the number of records for that ID. If there is only one record per ID, you need to keep adding the recordcounts and after the For Loop, display that number.

    What you are doing is getting SOME number (based on your query) EACH time there is a Checked Item in your listview.

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    SamOscarBrown thank you for the help

    I want to print reports for records which are checked on the listview.
    How can I select them?
    Thank you again

  7. #7

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    How can I select the checked records on the listview.

    Name:  lvw.png
Views: 53
Size:  1.5 KB

    Code:
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
     Call connect
     sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where ID = " & lvw.ListItems(I)
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
        
        Exit For
        End If
        Next
    
    MsgBox RS.RecordCount
    This code returns RecordCount = 1
    Last edited by samer22; Feb 8th, 2018 at 07:10 PM.

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    I wonder why it is working great here.
    Code:
    Dim Msg As String
    Msg = MsgBox("Voulez Vous vraiment supprimer ce dossier?", vbQuestion Or vbYesNo)
    
    If Msg = vbYes Then
      For I = lvw.ListItems.Count To 1 Step -1
        If lvw.ListItems.Item(I).Checked = True Then
          StrSql = "delete * from Tbl2 where PID = " & lvw.ListItems(I)
          DB.Execute StrSql
          StrSql = "delete * from Tbl1 where ID = " & lvw.ListItems(I)
          DB.Execute StrSql
          lvw.ListItems.Remove (I)
        End If
      Next
    End If
    In this code I can filter my records based on checked items in the listview.

    But I failed here.

    Code:
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
     Call connect
     sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where ID = " & lvw.ListItems(I)
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
        
        Exit For
        End If
        Next
    
    MsgBox RS.RecordCount

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,272

    Re: Getting wrong recordset RecorCount

    to get the 10 (or however many checked) records into a single recordset, you need to combine them in the where clause
    Code:
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
      strwhere = strwhere "ID = '" & lvw.ListItems(I) & "' or "
     end if
    next
    strwhere = left(strwhere, len(strwhere) - 4)      ' remove final " or "
    debug.print strwhere     ' check if it looks correct
     Call connect
     sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where " & strwhere
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
        
    MsgBox RS.RecordCount
    i did not test this, so may contain some typo or code error

    the delete code posted above works correctly, because it just runs for single record for each checked item, which is not the same as returning a recodset, though as sam suggested it could return a recordset of one record for each checked item, i do not presume that is what you would want


    @sam as there is an exit for it will only ever return the last checked item in the listview
    Last edited by westconn1; Feb 9th, 2018 at 07:19 AM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,391

    Re: Getting wrong recordset RecorCount

    @sam as there is an exit for it will only ever return the last checked item in the listview
    Ah yeah. Missed that line.

  11. #11

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    westconn1
    Thanks a lot for the code.
    Perhaps I am misusing it.
    This what I did but didn' work.
    Code:
    Dim I As Long
    Dim sSQL As String
    Dim strwhere As String
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
     Call connect
    strwhere = "id = " & lvw.ListItems(I) & ""
       sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where " & strwhere
     
    Debug.Print strwhere
     End If
    Next
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
        
    MsgBox RS.RecordCount
    The msgbox still returns the total number of records in database is 1.

    I have 10 records in database.
    This is the output When I print strwhere with all items checked.

    id = 9
    id = 8
    id = 7
    id = 6
    id = 5
    id = 3
    id = 2
    id = 1

    When I uncheck some items, this is how the output look like

    id = 6
    id = 5
    id = 3
    Thank you
    Last edited by samer22; Feb 9th, 2018 at 02:48 PM.

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    This is the ouput when I print sSQL

    Code:
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
     Call connect
    strwhere = "id = " & lvw.ListItems(I) & ""
       sSQL = "Select * from Tbl1 where " & strwhere
    Debug.Print sSQL 
     End If
    Next
    Code:
    Select * from Tbl1 where id = 9
    Select * from Tbl1 where id = 8
    Select * from Tbl1 where id = 7
    Select * from Tbl1 where id = 6
    Select * from Tbl1 where id = 5
    Select * from Tbl1 where id = 3
    Select * from Tbl1 where id = 2
    Select * from Tbl1 where id = 1

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,272

    Re: Getting wrong recordset RecorCount

    your sql is still only looking at a single ID for each iteration of the loop
    you are getting exactly the right result for the code you are using

    you did not follow at all, what i did to build the sql sting in a loop then only open a recordset once after the loop
    just paste the code i posted then see how strwhere looks before opening the recordset
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    Thank you sir for replying me
    Code:
    Dim I As Long
    Dim sSQL As String
    Dim strwhere As String
    
    For I = lvw.ListItems.Count To 1 Step -1
     If lvw.ListItems.Item(I).Checked = True Then
     strwhere = "id = " & lvw.ListItems(I) & ""
     End If
    Next
    strwhere = Left(strwhere, Len(strwhere) - 4)
    Debug.Print strwhere
     Call connect
     sSQL = "Select * from Tbl1 where " & strwhere
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly
    The output of printing strwhere is
    Code:
    ID
    However the RS.RecordCount is giving the right count (10)
    thank you

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,272

    Re: Getting wrong recordset RecorCount

    However the RS.RecordCount is giving the right count (10)
    i am not at all sure why that should happen, or if the correct records are being returned, as the where argument is not at all correct, it should probably just return all records

    The output of printing strwhere is
    because that is what the code, as you have it, would do, it does not follow what i posted

    you need the where string to look like
    "Id = 2 or ID = 4 or ID = 7"
    yours would be in reverse numeric order as the loop is from listcount -1 to 0, which is not really required in this instance, could just as easily be from 0 to listcount -1, only when removing items from within a loop need the loop from the highest number to lowest
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    THANKS SIR FOR YOUR INTEREST
    I'm getting error when I tried to copy your code.

    Name:  QQQ.jpg
Views: 24
Size:  44.9 KB

  17. #17
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,272

    Re: Getting wrong recordset RecorCount

    my bad
    Code:
    strwhere = strwhere & "ID = '" & lvw.ListItems(I) & "' or "
    i did not test this, so may contain some typo or code error
    if your ID database field is numeric, you do not need the ' ' enclosing the criteria, i put them in, as i did not know if the ID was a text field with numbers
    Code:
    strwhere = strwhere & "ID = " & lvw.ListItems(I) & " or "
    Last edited by westconn1; Feb 9th, 2018 at 08:25 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    Name:  QQQ.jpg
Views: 24
Size:  18.0 KB

  19. #19

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    Name:  QQQ.jpg
Views: 24
Size:  14.8 KB
    This is what I'm getting now

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    yeah My field ID is numeric

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    Name:  QQQ.jpg
Views: 23
Size:  14.5 KB

  22. #22
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,272

    Re: Getting wrong recordset RecorCount

    re-read post #9, especially read the comments in the code and the order that things happen i in the code
    look at post #15 to see what the where criteria should look like when printed to the immediate window, from the image above it would appear that you did not remove the final or that is generated in the loop

    DO NOT OPEN THE RECORDSET IN THE LOOP
    you only need to open the recordset once when the loop has completed

    perhaps someone else can explain these concepts to you better, as you do not seem to comprehend at all what i have been posting
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: Getting wrong recordset RecorCount

    westconn1
    Thanks million times for your patience with me and for your help.
    I was opening the recordset inside and outside the loop but I was always getting errors.
    This is what I get when opening the recordset outside the loop.

    Name:  QQQ.jpg
Views: 18
Size:  22.0 KB

    strwhere = strwhere & "ID = '" & lvw.ListItems(I) & " or "

    the problem was caused by this tiny ' after ID.
    I didn't pay attention to it.
    Thank you millio times sir and I apologise for my carelessness.

  24. #24
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,391

    Re: [RESOLVED] Getting wrong recordset RecorCount

    This thread is marked Resolved, yet your last post still shows an error. Just to make sure you have it correct, post your latest code again...let's see if you followed the instructions or not.

    Sam

  25. #25

    Thread Starter
    Fanatic Member
    Join Date
    Oct 2013
    Posts
    539

    Re: [RESOLVED] Getting wrong recordset RecorCount

    SamOscarBrown
    Thank you for your interest
    This is the code that is working
    Code:
    For I = 1 To lvw.ListItems.Count
     If lvw.ListItems.Item(I).Checked = True Then
      strwhere = strwhere &  "ID =" & lvw.ListItems(I) & " or "
     end if
    next
    strwhere = left(strwhere, len(strwhere) - 4)      
    Connect
     sSQL = "Select ForeName + ' ' + SurName  AS FullName, Date_proch, Etat from Tbl1 where " & strwhere
     RS.CursorLocation = adUseClient
    RS.Open sSQL, DB, adOpenForwardOnly

  26. #26
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    961

    Re: [RESOLVED] Getting wrong recordset RecorCount

    Hi,

    when loadind Data to a Listview pack the ID(Autoincrement) within the Listview KEY

    sample Table
    AD_ID Autoincrement
    AD_Name Text
    AD_Vorname Text
    etc...

    then when you load the Listview...
    Do While not Rs.Eof
    Set Li = ListView1.ListItems.Add
    Li.Key = Rs.Fields("AD_ID").Value & "x"

    then Key has to be Alphanumric, that's why you add the "x"

    when deleting....
    Dim ID As Long
    Dim sSQL As String

    ID = Val(Listview1.SelectedItem.Key)
    sSQL = "Delete From Adressen Where AD_ID = " & ID
    Cn.Execute sSQL

    looks alot more readable, what do you think


    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width