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_KeyPress(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.TextBox2 , Len(Me.TextBox2 ) - 1)
End If
End If
'CALL SEARCH ROUTINE
Call searchDB
End Sub
Public Sub searchDB(searchStr)
'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(searchField)," & Len(Me.TextBox2) & ") =
'" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.RowSource = cSQL
End Sub
Enjoy! 5 6451
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.Text
--
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_.comwrote in message
news:du*****************@bignews2.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 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_KeyPress(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.TextBox2 , Len(Me.TextBox2 ) - 1)
End If
End If
'CALL SEARCH ROUTINE
Call searchDB
End Sub
Public Sub searchDB(searchStr)
'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(searchField)," & Len(Me.TextBox2) & ") =
'" & Me.TextBox2 & "' "
End If
cSQL = cSQL & "ORDER BY ...;"
Me.ListBox1.RowSource = cSQL
End Sub
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.Text
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_.comwrote in message
news:du*****************@bignews2.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 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_KeyPress(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.TextBox2 , Len(Me.TextBox2 ) - 1) End If End If
'CALL SEARCH ROUTINE Call searchDB
End Sub
Public Sub searchDB(searchStr)
'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(searchField)," & Len(Me.TextBox2) & ") = '" & Me.TextBox2 & "' " End If cSQL = cSQL & "ORDER BY ...;" Me.ListBox1.RowSource = cSQL
End Sub
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_.comwrote in message
news:g9****************@bignews5.bellsouth.net...
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.Text
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_.comwrote in message news:du*****************@bignews2.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 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_KeyPress(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.TextBox2 , Len(Me.TextBox2 ) - 1) End If End If
'CALL SEARCH ROUTINE Call searchDB
End Sub
Public Sub searchDB(searchStr)
'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(searchField)," & Len(Me.TextBox2) & ") = '" & Me.TextBox2 & "' " End If cSQL = cSQL & "ORDER BY ...;" Me.ListBox1.RowSource = cSQL
End Sub
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_.comwrote in message
news:g9****************@bignews5.bellsouth.net...
>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.Text
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_.comwrote in message news:du*****************@bignews2.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 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_KeyPress(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.TextBox2 , Len(Me.TextBox2 ) - 1) End If End If
'CALL SEARCH ROUTINE Call searchDB
End Sub
Public Sub searchDB(searchStr)
'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(searchField)," & Len(Me.TextBox2) & ") = '" & Me.TextBox2 & "' " End If cSQL = cSQL & "ORDER BY ...;" Me.ListBox1.RowSource = cSQL
End Sub
"DFS" <nospam@dfs_.comwrote in
news:g9****************@bignews5.bellsouth.net:
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.Text
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: damjanu |
last post by:
Dear All;
I have 3 issues
1) On some of my tables I have unique indexes, so that
for example no two phone numbers can be the same.
When user enters phone number that already exists, a
scary...
|
by: Colleyville Alan |
last post by:
I have an app that looks up an id number and when a button is clicked,
inputs the id # into a query. After running the query, I click a second
button which grabs the client name rather than the id...
|
by: Colleyville Alan |
last post by:
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...
|
by: rh |
last post by:
Hi, I'd like to add an incremental search to my windows form DataGrid that
works like the one used in Windows Explorer.
In Windows Explorer, when you type a character it jumps to the first item...
|
by: Hrvoje Voda |
last post by:
I'm going through the listbox items with cursor.
I would like to put the selected listbox text into a textbox.
What method should I use?
Hrcko
| |
by: Luis Ferrao |
last post by:
Comming from Windows Forms development, I never had problems with this
issue before.
What i'm trying to achieve is an iTunes like incremental search. For
those who never used iTunes, it's...
|
by: allansiu823 |
last post by:
Hi I am new to MS Access and VBA and have been bothered by this problem for a looooong time. I have this listbox containing all the records (~1000) in a form and I want to be able to type something...
|
by: Eugenio |
last post by:
Hi there, I'm returning to this forum for the second time and I would like to say thanks for the great help provided. I've encountered a new problem now and hope that you will be able to help me...
|
by: weirdguy |
last post by:
Hello,
Just for anyone information, there is a similar title "Search in Listbox" but it is via Combo Box. In case, anyone need it, I put a link to here.
Please let me know if I break any rules...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |