[RESOLVED] Recordset Counting problem

[intercepter]

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:

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:

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

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:

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?

[WoF]

I'm not sure but:
There is this loop:

  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:

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.

[intercepter]

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?

[WoF]

You see, this loop is giving as many lines as you have wanted in the NumLines parameter:

  For i = 1 To NumLines
      a$ = sr.NextLine
      r$ = r$ + a$ + " $$ "
  Next

Maybe you stop it early like

  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

[intercepter]

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

[DataMiser]

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?

[intercepter]

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.

[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.

[viperbyte]

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

[viperbyte]

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.

[intercepter]

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?

[DataMiser]

Actually you would need to use the class name as well since your linessent variable is part of a class and is public.

NewCount=Count-MyClass.LinesSent

[DataMiser]

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

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.

[DataMiser]

Of course another option is to do a paging recordset

[dglienna]

MOD(x) will give give 3, 6,9,12 or not.