Incremental User input into Listbox display problem...

  • Thread starter Thread starter Regnab
  • Start date Start date
R

Regnab

I've got a listbox which operates off a query:

SELECT FileName.fn_Filename, FileName.fn_Filepath FROM FileName WHERE
(((FileName.fn_Filename) Like "*" & Forms!frmSearch!txtUserInput &
"*"));

The idea is that the user can type in "N4" and all file names with that
sequence will display in the list box from which the user can then make
their choice. Works well when I assign the Me.lstOutput.Requery to a
button.

However, I'd like it to update as each letter is entered into the text
box. The only way I've been able to do that is to assign 'Me.refresh'
to a key stroke event in the text box. I tried requery without it but
Access doesn't seem to register that data has been entered.

The Me.Refresh works, but also selects the previous input in the text
box after each key stroke - effectively causing the user to type over
their previous letter. So if someone types "N44Fleet", only the 't' is
displayed when they have finished. The query still works, but I'd like
the full word displayed. As such, I'm wondering if there is some way to
use the value of the text box without refreshing or if not, how I can
keep typing without over-writing the previous input.

Thanks for all your help,

Cheers

Reg
 
Try the Change Event for the textbox and do a requery of the listbox in that
event.

Private Sub txtUserInput_Change()
Dim txtSearchString As Variant
Dim StrSQL As String

txtSearchString = Me.txtUserInput.Text

StrSQL = "SELECT FileName.fn_Filename, FileName.fn_Filepath" & _
" FROM FileName " & _
" WHERE FileName.fn_Filename Like """ &
txtSearchstring & "*"" "

Me!lstOutput= StrSQL

End Sub


Arvin Meyer has a sample database at
http://www.datastrat.com/Download2.html
Download the ExpandingSearch2K (or the earlier 97 version)
 
Thanks for the ideas John.

I tried that and it doesn't appear to work.

Dim strSQL As String
Dim strInput As String

strInput = Me.txtInput
strSQL = "SELECT FileName.fn_Filename, FileName.fn_Filepath " & _
"FROM FileName " & _
"WHERE (((FileName.fn_Filename) Like ' * ' & strInput & ' * '));"

Me.lstDisplay = strSQL

If you can spot some problems in my coding please let me know. By just
using me.lstDisplay.requery and having the SQL in the actual Row Source
setting, I can get it to requery. The main issue is getting the query
to recognise each incremental letter. It works if I click on a button
which runs the "me.lstDisplay.requery" after each letter. Just how to
get it to work in the 'Change' event.
 
Dim strSQL As String
Dim strInput As String

'Use the text property of the control not the (default) value property
'The value property is not updated until the control loses focus

strInput = Me.txtInput.TEXT '<<< CHANGE

'Build your SQL string as follows - note the added quote marks
strSQL = "SELECT FileName.fn_Filename, FileName.fn_Filepath " & _
"FROM FileName " & _
"WHERE (((FileName.fn_Filename) Like ' * '" & strInput & "' * '));"

'DEBUG statements follow to allow you to check the SQL string to see
'if it is valid. You can copy and paste this into a query and see if it
'returns the expected records. Once it is working get rid of the
debug.print
'statement

Debug.Print StrSQL 'Delete or comment out after this proc is working.

Me.lstDisplay = strSQL
 
Back
Top