Incremental search w/ textbox and listbox

[DFS]

This works pretty well, and it's easy, but it's not the ultimate solution.
The kludgey part is it uses a hidden field to incrementally capture the
keystrokes in the visible field (because executing code from KeyPress in the
visible field doesn't work - for some reason Access doesn't see the letters
until Exit or AfterUpdate is executed - maybe Refresh too). So you have to
make sure the data in the hidden field matches what's in the visible field.
You have to handle backspace at least - haven't tried other keys.

The Controls:

TextBox1 - visible, user enters search string here
TextBox2 - hidden, user doesn't see
ListBox1 - visible, show the results of the incremental search

The Routines:

Private Sub TextBox1_KeyPre ss(KeyAscii As Integer)

'ADD LETTERS TO HIDDEN TEXTBOX
If KeyAscii <8 Then
Me.TextBox2 = Me.TextBox2 & Chr(KeyAscii)
Else 'USER ENTERS A BACKSPACE
If Len(Me.TextBox2 ) 0 Then
Me.TextBox2 = Left(Me.TextBox 2 , Len(Me.TextBox2 ) - 1)
End If
End If

'CALL SEARCH ROUTINE
Call searchDB

End Sub


Public Sub searchDB(search Str)

'THIS VERSION DOESN'T USE WILDCARDS
cSQL = "SELECT FIELDS "
cSQL = cSQL & "FROM TABLE "
cSQL = cSQL & "WHERE ..."
If Len(Me.TextBox2 ) 0 Then
cSQL = cSQL & "AND LEFT(Trim(searc hField)," & Len(Me.TextBox2 ) & ") =
'" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.Row Source = cSQL

End Sub

Enjoy!

[Allen Browne]

Not sure I have understood your question, but could you use the Change event
of Textbox1?

The Change event fires for each keystroke
Use the text box's Text property, i.e.:
Me.TextBox1.Tex t

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DFS" <nospam@dfs_.co mwrote in message
news:du******** *********@bigne ws2.bellsouth.n et...

This works pretty well, and it's easy, but it's not the ultimate solution.
The kludgey part is it uses a hidden field to incrementally capture the
keystrokes in the visible field (because executing code from KeyPress in
the visible field doesn't work - for some reason Access doesn't see the
letters until Exit or AfterUpdate is executed - maybe Refresh too). So
you have to make sure the data in the hidden field matches what's in the
visible field. You have to handle backspace at least - haven't tried other
keys.

The Controls:

TextBox1 - visible, user enters search string here
TextBox2 - hidden, user doesn't see
ListBox1 - visible, show the results of the incremental search

The Routines:

Private Sub TextBox1_KeyPre ss(KeyAscii As Integer)

'ADD LETTERS TO HIDDEN TEXTBOX
If KeyAscii <8 Then
Me.TextBox2 = Me.TextBox2 & Chr(KeyAscii)
Else 'USER ENTERS A BACKSPACE
If Len(Me.TextBox2 ) 0 Then
Me.TextBox2 = Left(Me.TextBox 2 , Len(Me.TextBox2 ) - 1)
End If
End If

'CALL SEARCH ROUTINE
Call searchDB

End Sub


Public Sub searchDB(search Str)

'THIS VERSION DOESN'T USE WILDCARDS
cSQL = "SELECT FIELDS "
cSQL = cSQL & "FROM TABLE "
cSQL = cSQL & "WHERE ..."
If Len(Me.TextBox2 ) 0 Then
cSQL = cSQL & "AND LEFT(Trim(searc hField)," & Len(Me.TextBox2 ) & ") =
'" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.Row Source = cSQL

End Sub

[DFS]

Allen Browne wrote:

Not sure I have understood your question, but could you use the
Change event of Textbox1?

The Change event fires for each keystroke
Use the text box's Text property, i.e.:
Me.TextBox1.Tex t

Thanks, but that doesn't work either.

It's like Access doesn't recognize what's in the textbox until you Exit,
AfterUpdate, or [maybe] Refresh. I don't want to use Refresh - even if it
does work - because the form is kind of heavy with other lists and may take
a while to refresh.

"DFS" <nospam@dfs_.co mwrote in message
news:du******** *********@bigne ws2.bellsouth.n et...

>This works pretty well, and it's easy, but it's not the ultimate
solution. The kludgey part is it uses a hidden field to
incrementall y capture the keystrokes in the visible field (because
executing code from KeyPress in the visible field doesn't work - for
some reason Access doesn't see the letters until Exit or AfterUpdate
is executed - maybe Refresh too). So you have to make sure the data
in the hidden field matches what's in the visible field. You have to
handle backspace at least - haven't tried other keys.

The Controls:

TextBox1 - visible, user enters search string here
TextBox2 - hidden, user doesn't see
ListBox1 - visible, show the results of the incremental search

The Routines:

Private Sub TextBox1_KeyPre ss(KeyAscii As Integer)

'ADD LETTERS TO HIDDEN TEXTBOX
If KeyAscii <8 Then
Me.TextBox2 = Me.TextBox2 & Chr(KeyAscii)
Else 'USER ENTERS A BACKSPACE
If Len(Me.TextBox2 ) 0 Then
Me.TextBox2 = Left(Me.TextBox 2 , Len(Me.TextBox2 ) - 1)
End If
End If

'CALL SEARCH ROUTINE
Call searchDB

End Sub


Public Sub searchDB(search Str)

'THIS VERSION DOESN'T USE WILDCARDS
cSQL = "SELECT FIELDS "
cSQL = cSQL & "FROM TABLE "
cSQL = cSQL & "WHERE ..."
If Len(Me.TextBox2 ) 0 Then
cSQL = cSQL & "AND LEFT(Trim(searc hField)," & Len(Me.TextBox2 ) &
") = '" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.Row Source = cSQL

End Sub

[Allen Browne]

If you tested the suggestion, you must have either:
a) not used the Change event.
b) looked at the Value of the text box in its Change event instead of its
Text;

a) KeyPress is too early: the keystroke has not been processed yet. That's
the point of the event: you can destroy the keystroke (by setting KeyAscii
to zero.)

b) The Value of the control is not available until its BeforeUpdate event.
The Text property describes the contents of the control before it becomes
the Value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DFS" <nospam@dfs_.co mwrote in message
news:g9******** ********@bignew s5.bellsouth.ne t...

Allen Browne wrote:

>Not sure I have understood your question, but could you use the
Change event of Textbox1?

The Change event fires for each keystroke
Use the text box's Text property, i.e.:
Me.TextBox1.Tex t

Thanks, but that doesn't work either.

It's like Access doesn't recognize what's in the textbox until you Exit,
AfterUpdate, or [maybe] Refresh. I don't want to use Refresh - even if it
does work - because the form is kind of heavy with other lists and may
take a while to refresh.

>"DFS" <nospam@dfs_.co mwrote in message
news:du******* **********@bign ews2.bellsouth. net...

>>This works pretty well, and it's easy, but it's not the ultimate
solution. The kludgey part is it uses a hidden field to
incremental ly capture the keystrokes in the visible field (because
executing code from KeyPress in the visible field doesn't work - for
some reason Access doesn't see the letters until Exit or AfterUpdate
is executed - maybe Refresh too). So you have to make sure the data
in the hidden field matches what's in the visible field. You have to
handle backspace at least - haven't tried other keys.

The Controls:

TextBox1 - visible, user enters search string here
TextBox2 - hidden, user doesn't see
ListBox1 - visible, show the results of the incremental search

The Routines:

Private Sub TextBox1_KeyPre ss(KeyAscii As Integer)

'ADD LETTERS TO HIDDEN TEXTBOX
If KeyAscii <8 Then
Me.TextBox2 = Me.TextBox2 & Chr(KeyAscii)
Else 'USER ENTERS A BACKSPACE
If Len(Me.TextBox2 ) 0 Then
Me.TextBox2 = Left(Me.TextBox 2 , Len(Me.TextBox2 ) - 1)
End If
End If

'CALL SEARCH ROUTINE
Call searchDB

End Sub


Public Sub searchDB(search Str)

'THIS VERSION DOESN'T USE WILDCARDS
cSQL = "SELECT FIELDS "
cSQL = cSQL & "FROM TABLE "
cSQL = cSQL & "WHERE ..."
If Len(Me.TextBox2 ) 0 Then
cSQL = cSQL & "AND LEFT(Trim(searc hField)," & Len(Me.TextBox2 ) &
") = '" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.Row Source = cSQL

End Sub

[DFS]

Allen Browne wrote:

If you tested the suggestion, you must have either:
a) not used the Change event.
b) looked at the Value of the text box in its Change event instead of
its Text;

I thought for sure I had done both, so I went back and tested again. And
you're right - I must have missed one, because using the Change event and
the .Text property together worked. I was able to eliminate the hidden text
field and 7 lines of code. Result: cleaner and tighter system.

Thanks

a) KeyPress is too early: the keystroke has not been processed yet.
That's the point of the event: you can destroy the keystroke (by
setting KeyAscii to zero.)

b) The Value of the control is not available until its BeforeUpdate
event. The Text property describes the contents of the control before
it becomes the Value.

"DFS" <nospam@dfs_.co mwrote in message
news:g9******** ********@bignew s5.bellsouth.ne t...

>Allen Browne wrote:

>>Not sure I have understood your question, but could you use the
Change event of Textbox1?

The Change event fires for each keystroke
Use the text box's Text property, i.e.:
Me.TextBox1.Tex t

Thanks, but that doesn't work either.

It's like Access doesn't recognize what's in the textbox until you
Exit, AfterUpdate, or [maybe] Refresh. I don't want to use Refresh
- even if it does work - because the form is kind of heavy with
other lists and may take a while to refresh.

>>"DFS" <nospam@dfs_.co mwrote in message
news:du****** ***********@big news2.bellsouth .net...
This works pretty well, and it's easy, but it's not the ultimate
solution. The kludgey part is it uses a hidden field to
incrementall y capture the keystrokes in the visible field (because
executing code from KeyPress in the visible field doesn't work -
for some reason Access doesn't see the letters until Exit or
AfterUpdat e is executed - maybe Refresh too). So you have to make
sure the data in the hidden field matches what's in the visible
field. You have to handle backspace at least - haven't tried other
keys. The Controls:

TextBox1 - visible, user enters search string here
TextBox2 - hidden, user doesn't see
ListBox1 - visible, show the results of the incremental search

The Routines:

Private Sub TextBox1_KeyPre ss(KeyAscii As Integer)

'ADD LETTERS TO HIDDEN TEXTBOX
If KeyAscii <8 Then
Me.TextBox2 = Me.TextBox2 & Chr(KeyAscii)
Else 'USER ENTERS A BACKSPACE
If Len(Me.TextBox2 ) 0 Then
Me.TextBox2 = Left(Me.TextBox 2 , Len(Me.TextBox2 ) - 1)
End If
End If

'CALL SEARCH ROUTINE
Call searchDB

End Sub


Public Sub searchDB(search Str)

'THIS VERSION DOESN'T USE WILDCARDS
cSQL = "SELECT FIELDS "
cSQL = cSQL & "FROM TABLE "
cSQL = cSQL & "WHERE ..."
If Len(Me.TextBox2 ) 0 Then
cSQL = cSQL & "AND LEFT(Trim(searc hField)," & Len(Me.TextBox2 )
& ") = '" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.Row Source = cSQL

End Sub

[Bob Quintal]

"DFS" <nospam@dfs_.co mwrote in
news:g9******** ********@bignew s5.bellsouth.ne t:

Allen Browne wrote:

>Not sure I have understood your question, but could you use
the Change event of Textbox1?

The Change event fires for each keystroke
Use the text box's Text property, i.e.:
Me.TextBox1.Tex t

Thanks, but that doesn't work either.

It's like Access doesn't recognize what's in the textbox until
you Exit, AfterUpdate, or [maybe] Refresh. I don't want to
use Refresh - even if it does work - because the form is kind
of heavy with other lists and may take a while to refresh.

Are you sure? the .text property is by design, supposed to hold the
user's typed-in characters, while the control has focus.

the .value property, however behaves by design, as you describe,
unchanged until you ewxit the textbox.

I'd really like to see the .text property misbehave in the way you
describe, because that means there is a bug in Access :-)

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com