-
December 20th, 2009, 06:56 PM
#1
[RESOLVED] Recordset Counting problem
Hi everyone. I have gotten great help here so I figured i would come back for this. The aspect of my present project is to do a keyword search through a access db and put that resulting record set into a collection, count the number of lines in the collection and reduce that number every time a segment of the resulted collection is viewed.
My code for creating the search result subset is this:
Code:
Private Sub KeyWordSearch()
Dim SQLStr, sString, UserName As String
Dim sPath As String
Dim Counter As Long
Dim tmpStr As String
Dim Count As Integer
Dim iHnd As Long
iHnd = getPalSubForm(WindowClass, Combo1.Text, RoomOutboundTextBoxClass, SendTxtIndex)
iHnd = SendTextHnd
Trigger = "$key"
On Error GoTo KeyWordError
cmdPos = InStr(1, RichTextBox2.Text, Trigger, vbBinaryCompare)
If cmdPos = 0 Then Exit Sub
cmdVer = Trim$(Right$(RichTextBox2.Text, (Len(RichTextBox2.Text) - cmdPos) - 4))
RichTextBox4.Text = Right(cmdVer, Len(cmdVer))
sString = RichTextBox4.Text
SQLStr = "Select tblBook.*,tblQUOTE.* From tblBook,tblQUOTE Where (tblQUOTE.Quote Like '%" & sString & "%') And tblBook.Book_ID=tblQUOTE.Book_ID Order by tblQUOTE.Book_ID,tblQUOTE.Chapter ASC"
Dim db1 As Connection
Set db1 = New Connection
db1.CursorLocation = adUseClient
sPath = App.Path & "\kjvbible.mdb"
db1.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
Set adoPrimaryRS1 = New Recordset
Set adoPrimaryRS1 = db1.Execute(SQLStr, , adCmdText)
Dim sr As New SearchResult
Dim a$
UserName = Text1.Text
colSearches.Add sr, UserName
While Not adoPrimaryRS1.EOF
tmpStr = adoPrimaryRS1!Book_Title & " " & adoPrimaryRS1!Chapter & ":" & adoPrimaryRS1!Verse
a$ = tmpStr
sr.Add a$
adoPrimaryRS1.MoveNext
RichTextBox1.Text = RichTextBox1.Text & tmpStr & " $$ "
Wend
Count = sr.LinesCount
If Count > 12 Then
Call SendMore((Text1.Text), 10)
RTB5 = Text1.Text & " Your Keyword or Phrase Search Results Are: " & Count & " Verse(s) " & RichTextBox1.Text & " Type $more For More Results "
RTB5.SelLength = Len(RTB5.Text)
With RTB5
.SelBold = True
.SelFontSize = 8
.SelColor = RGB(101, 0, 192)
End With
Call SendMessageByString(iHnd, WM_SETTEXT, 0&, RTB5)
Call SendMessage(iHnd, WM_KEYDOWN, 13, 0)
' Close RecordSet and Database
adoPrimaryRS1.Close
db1.Close
RTB5 = ""
Trigger = ""
RichTextBox1.Text = ""
RichTextBox2.Text = ""
RichTextBox4.Text = ""
Exit Sub
Else
End If
RTB5 = Text1.Text & " Your Keyword or Phrase Search Results Are: " & Count & " Verse(s) " & RichTextBox1.Text
RTB5.SelLength = Len(RTB5.Text)
With RTB5
.SelBold = True
.SelFontSize = 8
.SelColor = RGB(101, 0, 192)
End With
Call SendMessageByString(iHnd, WM_SETTEXT, 0&, RTB5)
Call SendMessage(iHnd, WM_KEYDOWN, 13, 0)
Close Recordset And Database
adoPrimaryRS1.Close
db1.Close
RTB5 = ""
Trigger = ""
RichTextBox1.Text = ""
RichTextBox2.Text = ""
RichTextBox4.Text = ""
Exit Sub
KeyWordError:
RTB5 = Text1.Text & ": You Have A Search Pending and Cannot do Another Search. Please Type $more for Additional Results or $stop to Clear Your Search"
RTB5.SelLength = Len(RTB5.Text)
With RTB5
.SelBold = True
.SelFontSize = 10
.SelColor = RGB(101, 0, 192)
End With
Call SendMessageByString(iHnd, WM_SETTEXT, 0&, RTB5)
Call SendMessage(iHnd, WM_KEYDOWN, 13, 0)
' Close RecordSet and Database
adoPrimaryRS1.Close
db1.Close
RTB5 = ""
Trigger = ""
RichTextBox1.Text = ""
RichTextBox2.Text = ""
RichTextBox4.Text = ""
Exit Sub
End Sub
This code works just fine creating the search result. And giving me my initial count. The next set of commands are what are troubling me:
Code:
Private Sub SendMore(UserName As String, NumLines As Integer)
Dim r$, i%
Dim a$, sr As SearchResult
If UserName = "" Then Exit Sub
On Error GoTo NoResultsForThisUserName
Set sr = colSearches(UserName)
For i = 1 To NumLines
a$ = sr.NextLine
r$ = r$ + a$ + " $$ "
Next
RichTextBox1.Text = r$
NoResultsForThisUserName:
Exit Sub
End Sub
Code:
Private Sub MoreVer()
Dim iHnd As Long
iHnd = getPalSubForm(WindowClass, Combo1.Text, RoomOutboundTextBoxClass, SendTxtIndex)
iHnd = SendTextHnd
Dim u$
If colSearches.Count = 0 Then
RTB5 = Text1.Text & ": You Dont Have Any More Search Results To Display."
RTB5.SelLength = Len(RTB5.Text)
With RTB5
.SelBold = True
.SelFontSize = 10
.SelColor = RGB(101, 0, 192)
End With
Call SendMessageByString(iHnd, WM_SETTEXT, 0&, RTB5)
Call SendMessage(iHnd, WM_KEYDOWN, 13, 0)
Exit Sub
Else
End If
u$ = Text1.Text
If u$ <> "" Then SendMore u$, 10
RTB5 = Text1.Text & " Your Keyword or Phrase Search Results Are: " & Count & " Verse(s) " & RichTextBox1.Text & " Type $more for more results, "
RTB5.SelLength = Len(RTB5.Text)
With RTB5
.SelBold = True
.SelFontSize = 8
.SelColor = RGB(101, 0, 192)
End With
Call SendMessageByString(iHnd, WM_SETTEXT, 0&, RTB5)
Call SendMessage(iHnd, WM_KEYDOWN, 13, 0)
RichTextBox1.Text = ""
End Sub
In an example, I do a earch and the count result is 72. The first time I execute the morever sub, it shows 63. Then 60 to the end of the pages of results I viewed.
I also have a class module:
Code:
Private Lines() As String
Public LinesCount As Integer
Public LinesLeft As Integer
Public LinesSent As Integer
Private colSearches As New Collection
Public Sub Add(NewLine As String)
ReDim Preserve Lines(LinesCount)
Lines(LinesCount) = NewLine
LinesCount = LinesCount + 1
End Sub
Public Property Get NextLine() As String
If LinesSent < LinesCount Then
NextLine = Lines(LinesSent)
LinesSent = LinesSent + 1
LinesLeft = LinesCount - LinesSent
Else
NextLine = "End of Results. Please Type $stop to Clear Your Search" + vbCrLf
LinesSent = LinesCount - 1
End If
End Property
At the end when all records are displayed, i then get floods of more text I don't want, repeating In the else section of the Class module.
Any ideas or suggestions?
Last edited by intercepter; December 20th, 2009 at 07:05 PM.
Reason: fix tags
-
December 21st, 2009, 08:03 AM
#2
Re: Recordset Counting problem
I'm not sure but:
There is this loop:
Code:
For i = 1 To NumLines
a$ = sr.NextLine
r$ = r$ + a$ + " $$ "
Next
It calls the NextLine property of your class 10 times always, because NumLines is 10 in the call.
Now, look at your Property Get:
Code:
Public Property Get NextLine() As String
If LinesSent < LinesCount Then
NextLine = Lines(LinesSent)
LinesSent = LinesSent + 1
LinesLeft = LinesCount - LinesSent
Else
NextLine = "End of Results. Please Type $stop to Clear Your Search" + vbCrLf
LinesSent = LinesCount - 1
End If
In the Else statement you let LesSent = LinesCount - 1
So when the next call comes then If LinesSent < LinesCount will be true and the last line be returned again. (and again).
You never actually remove lines from your collection, so Count = sr.LinesCount will always produce a number.
-
December 22nd, 2009, 12:47 AM
#3
Re: Recordset Counting problem
Actually, it is suppose to return the next 10 in the collection. And it does that, however, I want to solve the first problem of showing the proper count. Are you suggesting that I add something in the count=sr.linescount section of the search function?
-
December 22nd, 2009, 10:12 AM
#4
Re: Recordset Counting problem
You see, this loop is giving as many lines as you have wanted in the NumLines parameter:
Code:
For i = 1 To NumLines
a$ = sr.NextLine
r$ = r$ + a$ + " $$ "
Next
Maybe you stop it early like
Code:
For i = 1 To NumLines
a$ = sr.NextLine
r$ = r$ + a$ + " $$ "
If InStr(a$, "End of Results.") = 1 Then Exit For
Next
Also the LinesSent = LinesCount - 1 in your Else clause of the Property Get NextLine
will always make another line ready because the If condition asks: If LinesSent < LinesCount Then
maybe you let LinesSent = LinesCount
-
March 12th, 2010, 01:21 AM
#5
Re: Recordset Counting problem
Sorry for the long delay. Its been busy around here. And thank you for your help. What I want to know now, is how to show the number of records left to be sent. And I have tried several options and nothing seems to work for me.
The search subs show the proper number in the beginning, and then after the user types the $more command to get the next set, that number is correct as well, however, when running the sub again, the total value in "Count" is the same.
Example:
I do a search and get 73 results. I type $more and I see that I have 63 more results left. When I hit $more again, I expect to see 53 for the count. But it stays at 63. This all happens in the subs I posted earlier. What I am having trouble is with figuring out how to get that number to keep going down.
Yes I also changed my ELSE Clause to LinesSent = LinesCount
Last edited by intercepter; March 12th, 2010 at 01:23 AM.
-
March 12th, 2010, 01:39 AM
#6
Re: Recordset Counting problem
I do not see anywhere that you are changing the count or removing items from the collection after a search. Am I missing something or could this be the problem?
-
March 12th, 2010, 01:46 AM
#7
Re: Recordset Counting problem
That is where the problem seems to be. I have tried to modify the "Count" value on several of the subs and I get errors. Where would you suggest I try to modify the "Count" value? Or should I do something with the collection itself? This is where I am stumped.
-
March 12th, 2010, 11:23 AM
#8
Re: Recordset Counting problem
I am a little unclear on what you are doing but it would seem that the count is the actual count of items in the collection, in which case I would think that you would need to remove the items from teh collection as they are sent.
Of course since you are also keeping track of lines sent you could use a formula in your display like count-linessent to display the remaining number as well.
Last edited by DataMiser; March 12th, 2010 at 11:26 AM.
-
March 12th, 2010, 11:38 AM
#9
Re: Recordset Counting problem
Howdy. You have
Dim sr As New SearchResult
Dim a$ : mentioned in "KeyWordSearch"
So in Sub KeyWordSearch you declared and intialized sr and a$; But in "SendMore" you are redeclaring it and aren't even initializing it. You have to send in those varialbles to SendMore as parameters.
As it is in "SendMore" sr and a$ are totally different variables than the onces mentioned in "KeyWordSearch". And who knows what's inside those variables.
-Juan
-
March 12th, 2010, 12:06 PM
#10
Re: Recordset Counting problem
I just looked at your code again and I see that I was wrong about what I just wrote. BUT 'sr' in "SendMore" is still not the same 'sr' that is in "KeyWordSearch" from what I can tell and for what it's worth.
-
March 12th, 2010, 01:37 PM
#11
Re: Recordset Counting problem
Originally Posted by DataMiser
I am a little unclear on what you are doing but it would seem that the count is the actual count of items in the collection, in which case I would think that you would need to remove the items from teh collection as they are sent.
Of course since you are also keeping track of lines sent you could use a formula in your display like count-linessent to display the remaining number as well.
What I am trying to do is create a collection of db entries. Count the total search results, group them into 10 item segments if there are more than 12 and as the group of items gets sent out, reduce the number of items via a counting function.
The function to send them out in groups of 10 works without repeating anything already sent to that user. The problem is, the Count variable cant be manipulated because I get an error saying it is protected. I assume protected in the KeyWordSearch sub. With that in mind, I am trying to figure out the best way to handle this collection for a particular user. Via counting it, and/or removing items from the collection before returning to a wait state waiting for the user to type $more again for their next set of results.
I hope this makes what I am trying to do more clear. I also tried count-linessent and again I get the protected error. To be specific on the error itself I get: Complie Error: Function or interface marked as restricted, or the function uses an automation type not supported in visual basic
I also tried to use NewCount = Count - LinesSent and it says I need an object for LinesSent. I believe I need to use SearchResult and that doesn't even work either.
Any suggestions on where to look?
Last edited by intercepter; March 12th, 2010 at 01:43 PM.
-
March 12th, 2010, 03:09 PM
#12
Re: Recordset Counting problem
Actually you would need to use the class name as well since your linessent variable is part of a class and is public.
Code:
NewCount=Count-MyClass.LinesSent
-
March 12th, 2010, 03:11 PM
#13
Re: Recordset Counting problem
Upon looking again I see you have a LinesLeft variable so I would think you could use that as in MyClass.LinesLeft in your display function
Code:
RTB5 = Text1.Text & " Your Keyword or Phrase Search Results Are: " & MyClass.LinesLeft & " Verse(s) " .....
Assuming of course that the class is named MyClass and it is accessable fromt his routine where you do the display.
Last edited by DataMiser; March 12th, 2010 at 03:13 PM.
-
March 12th, 2010, 03:14 PM
#14
Re: Recordset Counting problem
Of course another option is to do a paging recordset
-
March 13th, 2010, 01:15 AM
#15
Re: Recordset Counting problem
MOD(x) will give give 3, 6,9,12 or not.
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
|