Problem with Incremental Search box

[Colleyville Alan]

I have a incremental search box that has been working fine for a couple of
months but is now acting up. This search box is from the cd that comes with
Getz's book, so I did not write it and have only a superficial understanding
of how it works.

I have had some trouble in the past when my code goes to another form and
comes back sometimes the search function did not work properly. Then I
added a line to requery it and it worked fine. But now I have a different
problem.

I have over 1700 records that populate this based on a query. I have
checked the underlying table and it looks fine and I have rund the query
associated with the listbox and that looks fine as well. But it only
searches to the 1st letter of whatever I initially type in. If I type in
the letter "B", I will get the first entry that begins with "B", but nothing
after that (I cannot even scroll to it). If I type in the letter "Z", I get
there, but no further and now all the records between #1 and "Z" (maybe 1600
of them) are available to me.

This is simply wierd IMHO. Does anyone have an idea where I might look to
find an answer as to why it is behaving so strangely? It was working fine a
little while ago. I've made chgs to the system recently, but none that I
can think of should have affected this.
Thanks.

[Larry Linson]

"Colleyville Alan" wrote

I have a incremental search box that has
been working fine for a couple of
months but is now acting up. This search
box is from the cd that comes with
Getz's book,

Could you be a little more specific, Alan? Edition of the book, chapter,
name of the search box, the topic in the chapter, etc.? And, any mods you
may have made, how you are using it, etc.?

Larry Linson
Microsoft Access MVP

[Colleyville Alan]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:9r*******************@nwrddc01.gnilink.net...

"Colleyville Alan" wrote

> I have a incremental search box that has
> been working fine for a couple of
> months but is now acting up. This search
> box is from the cd that comes with
> Getz's book,

Could you be a little more specific, Alan? Edition of the book, chapter,
name of the search box, the topic in the chapter, etc.? And, any mods you
may have made, how you are using it, etc.?

Larry Linson
Microsoft Access MVP

Ok, here goes: Getz, Litwin, and Gilbert: Access 2000 Developer's Handbook,
Volume 1: Desktop Edition - Chapter 7, pages 413-422 (though I copied the
class module from the cd and only briefly read the pages, I've looked at the
code in the module but do not understand it that well).

I call the following from the Form_Load event:

Sub InitSearch()
'BoundField allows you to have a field to which the list box is bound,
'but isn't the display field.
'The BoundField property must match the field indexed in the Index property.
Dim strMySql As String
Set mis = New IncrementalSearch
mis.DisplayField = "Plan_Name"
mis.BoundField = "Plan_ID"
mis.Index = "Plan_ID"
lstIncSrch.BoundColumn = 2
Set mis.ListBox = lstIncSrch
Set mis.TextBox = txtIncSrch
strMySql = "SELECT DISTINCT [Fund_Holdings_By_Plan].[Plan_Name],
[Fund_Holdings_By_Plan].[Plan_ID] FROM Fund_Holdings_By_Plan " & vbCrLf
strMySql = strMySql & "WHERE (((Fund_Holdings_By_Plan.Plan_ID) Not Like
'TMP*' AND (Fund_Holdings_By_Plan.Plan_ID) Not Like 'CLT*' AND
(Fund_Holdings_By_Plan.Plan_ID) Not Like 'RFP*')) " & vbCrLf
strMySql = strMySql & "ORDER BY [Fund_Holdings_By_Plan].[Plan_Name];"
lstIncSrch.RowSource = strMySql
txtIncSrch.Requery
lstIncSrch.Requery
End Sub
In the 2600+ lines of code, here are all of the references to these two
items:

lstIncSrch.BoundColumn = 2
lstIncSrch.Requery
lstIncSrch.RowSource = strMySql
Set mis.ListBox = lstIncSrch
strPlanId = txtIncSrch.Value
strPlanName = Me.lstIncSrch.Column(0)
strPlanName = Me.lstIncSrch.Column(0) & ".PPT"
txtIncSrch.Requery
txtIncSrch.SetFocus
txtIncSrch.Value = ""

I did not make any changes to the class module, I just copied it from the
sample database per the book's instructions. As to how I am using it, I am
setting the row source to the results of a query (the strMySql stuff above)
to list a group of unique client records. The table looks up by plan name
and returns a plan_ID. I just started having trouble with it recently, but
the latest chgs to the project have been deleting unused tables, queries,
and code. I cannot think of what I did that could change the behavior of
the search box.

Awhile ago, I found that when searching on another form using mutual fund
info, the search box would not get past a certain point but that was because
the fund names were not always in order. When I edited the table to get the
names consistent, it worked fine. In fact, it *still* works fine - I use
the incremental search stuff on 4 different forms and it works fine on 3 of
them. But as far as I can see, I am not doing anything differently there.
In the code above I have the RowSource specified after initializing the
searchbox, but I just did that a few minutes ago - it used to come before it
and I was simply trying something different.

[Colleyville Alan]

I added a temporary button to my form that simply calls lstIncSrch.requery.
If the box is stuck at a particular location, hitting the requery moves it
one extra line down. But if I type some txt in the txtbox portion, even
though no search takes place, hitting the requery button at that point finds
the text. If I put "Zi" in the box (the last entry in the table) and
requery, the listbox finds that record.

I have older versions of this db as I have been updating and burning to a cd
on a daily basis. Right now I am looking at a version of 3 wks ago that
worked fine, but so far the differences have not been any that I would
expect to affect anything.

"Colleyville Alan" <ae***********@nospam.comcast.net> wrote in message
news:paNCc.106638$eu.11918@attbi_s02...

"Larry Linson" <bo*****@localhost.not> wrote in message
news:9r*******************@nwrddc01.gnilink.net...

"Colleyville Alan" wrote

> I have a incremental search box that has
> been working fine for a couple of
> months but is now acting up. This search
> box is from the cd that comes with
> Getz's book,
Could you be a little more specific, Alan? Edition of the book, chapter,
name of the search box, the topic in the chapter, etc.? And, any mods you may have made, how you are using it, etc.?

Larry Linson
Microsoft Access MVP

Ok, here goes: Getz, Litwin, and Gilbert: Access 2000 Developer's

Handbook, Volume 1: Desktop Edition - Chapter 7, pages 413-422 (though I copied the
class module from the cd and only briefly read the pages, I've looked at the code in the module but do not understand it that well).

I call the following from the Form_Load event:

Sub InitSearch()
'BoundField allows you to have a field to which the list box is bound,
'but isn't the display field.
'The BoundField property must match the field indexed in the Index property. Dim strMySql As String
Set mis = New IncrementalSearch
mis.DisplayField = "Plan_Name"
mis.BoundField = "Plan_ID"
mis.Index = "Plan_ID"
lstIncSrch.BoundColumn = 2
Set mis.ListBox = lstIncSrch
Set mis.TextBox = txtIncSrch
strMySql = "SELECT DISTINCT [Fund_Holdings_By_Plan].[Plan_Name],
[Fund_Holdings_By_Plan].[Plan_ID] FROM Fund_Holdings_By_Plan " & vbCrLf
strMySql = strMySql & "WHERE (((Fund_Holdings_By_Plan.Plan_ID) Not Like 'TMP*' AND (Fund_Holdings_By_Plan.Plan_ID) Not Like 'CLT*' AND
(Fund_Holdings_By_Plan.Plan_ID) Not Like 'RFP*')) " & vbCrLf
strMySql = strMySql & "ORDER BY [Fund_Holdings_By_Plan].[Plan_Name];"
lstIncSrch.RowSource = strMySql
txtIncSrch.Requery
lstIncSrch.Requery
End Sub
In the 2600+ lines of code, here are all of the references to these two
items:

lstIncSrch.BoundColumn = 2
lstIncSrch.Requery
lstIncSrch.RowSource = strMySql
Set mis.ListBox = lstIncSrch
strPlanId = txtIncSrch.Value
strPlanName = Me.lstIncSrch.Column(0)
strPlanName = Me.lstIncSrch.Column(0) & ".PPT"
txtIncSrch.Requery
txtIncSrch.SetFocus
txtIncSrch.Value = ""

I did not make any changes to the class module, I just copied it from the
sample database per the book's instructions. As to how I am using it, I am setting the row source to the results of a query (the strMySql stuff above) to list a group of unique client records. The table looks up by plan name
and returns a plan_ID. I just started having trouble with it recently, but the latest chgs to the project have been deleting unused tables, queries,
and code. I cannot think of what I did that could change the behavior of
the search box.

Awhile ago, I found that when searching on another form using mutual fund
info, the search box would not get past a certain point but that was because the fund names were not always in order. When I edited the table to get the names consistent, it worked fine. In fact, it *still* works fine - I use
the incremental search stuff on 4 different forms and it works fine on 3 of them. But as far as I can see, I am not doing anything differently there.
In the code above I have the RowSource specified after initializing the
searchbox, but I just did that a few minutes ago - it used to come before it and I was simply trying something different.