-
Feb 8th, 2018, 02:57 PM
#1
Thread Starter
Fanatic Member
[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
-
Feb 8th, 2018, 03:17 PM
#2
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
-
Feb 8th, 2018, 03:19 PM
#3
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
-
Feb 8th, 2018, 03:34 PM
#4
Thread Starter
Fanatic Member
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
-
Feb 8th, 2018, 05:19 PM
#5
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.
-
Feb 8th, 2018, 05:31 PM
#6
Thread Starter
Fanatic Member
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
-
Feb 8th, 2018, 07:05 PM
#7
Thread Starter
Fanatic Member
Re: Getting wrong recordset RecorCount
How can I select the checked records on the listview.
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.
-
Feb 9th, 2018, 03:36 AM
#8
Thread Starter
Fanatic Member
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
-
Feb 9th, 2018, 07:11 AM
#9
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
-
Feb 9th, 2018, 07:13 AM
#10
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.
-
Feb 9th, 2018, 09:59 AM
#11
Thread Starter
Fanatic Member
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.
-
Feb 9th, 2018, 03:04 PM
#12
Thread Starter
Fanatic Member
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
-
Feb 9th, 2018, 05:14 PM
#13
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
-
Feb 9th, 2018, 05:35 PM
#14
Thread Starter
Fanatic Member
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
However the RS.RecordCount is giving the right count (10)
thank you
-
Feb 9th, 2018, 07:29 PM
#15
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
-
Feb 9th, 2018, 08:15 PM
#16
Thread Starter
Fanatic Member
Re: Getting wrong recordset RecorCount
THANKS SIR FOR YOUR INTEREST
I'm getting error when I tried to copy your code.
-
Feb 9th, 2018, 08:20 PM
#17
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
-
Feb 9th, 2018, 08:21 PM
#18
Thread Starter
Fanatic Member
Re: Getting wrong recordset RecorCount
-
Feb 9th, 2018, 08:24 PM
#19
Thread Starter
Fanatic Member
Re: Getting wrong recordset RecorCount
This is what I'm getting now
-
Feb 9th, 2018, 08:26 PM
#20
Thread Starter
Fanatic Member
Re: Getting wrong recordset RecorCount
yeah My field ID is numeric
-
Feb 9th, 2018, 08:33 PM
#21
Thread Starter
Fanatic Member
Re: Getting wrong recordset RecorCount
-
Feb 9th, 2018, 09:34 PM
#22
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
-
Feb 10th, 2018, 03:24 AM
#23
Thread Starter
Fanatic Member
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.
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.
-
Feb 10th, 2018, 07:36 AM
#24
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
-
Feb 10th, 2018, 01:23 PM
#25
Thread Starter
Fanatic Member
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
-
Feb 10th, 2018, 01:43 PM
#26
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|