make a textbox Autoexpand (aka Autocomplete)

[e.mel.net]

in response to a very old topic:
http://groups.google.ca/group/micro...rmscoding/browse_frm/thread/f7163c49c5da6801/

It's probably too late to be useful to TonyT, but I had the trouble
this morning and maybe my solution will help someone else.

The hardest part was sorting out what order events happened in - ie
weather to put the function call in KeyPressed, KeyDown, KeyUp, or
KeyDown. And also what triggers these events - any key press will
trigger KeyUp/Down and gives an ansii code, KeyPress will react to
Bksp, but not Del and it gives an ascii code. As far as performance
goes, its not bad, the shortcircuits save alot of time by not looking
for long strings it wont find.


here is the code______________________________________

'Auto expander
'As far as I can tell it makes a textbox behave just like a combo box
with AutoExpand
' except when tabbing out of the field it doesnt autocorrect
capitalization
'Parameters: strBox - Name of textbox as a string
' strTable - name of table to lookup values in
' strField - name of field to get values from
'
'Usage:
''Stash the KeyAscii Character for later - cant call expand
'' yet - the letter pressed hasnt been added to the textbox
'Private Sub txt_box_KeyPress(KeyAscii As Integer)
' keyPressed = KeyAscii
'End Sub
'
''Call from Change event - After letter has been sent to the textbox
'Private Sub txt_box_Change()
' expand "txt_box", "table", "supplier", keyPressed
'End Sub
'
Public Function expand(strBox As String, strTable As String, strField
As String, KeyAscii As Integer)
Dim cursor As Integer
Dim typed As String

'Check the key pressed
' if alphnumeric auto-expand (32-126)
' DEL or BackSpace or something else do nothing (8, 127, <=31)
' 32-127 seems to work for all letters, numbers, punctuation, and
nothing else
' If this causes problems maybe just exlude DEL(127) and
BS(8)
If (KeyAscii < 32 Or KeyAscii > 127) Then Exit Function

'get typed text using .text property, .value hasn't been updated
yet
typed = Screen.ActiveForm(strBox).Text '& Chr(KeyAscii)
'if we've already looked for this with no luck we dont need to look
again
If (Nz(strNotFound) <> "") And _
(strNotFound = Left(typed, Len(strNotFound))) Then Exit Function

'save cursor position (end of text)
cursor = Len(typed)

'open up a recordset
Set rstTable = CurrentDb.OpenRecordset("SELECT " & strField & "
FROM " _
& strTable & " ORDER BY " & strField,
dbOpenDynaset)

'

find first [field] like box*
rstTable.FindFirst "[" & strField & "] like '" & typed & "*'"

'if not found exit sub or else the first item will be put in
If rstTable.NoMatch Then
strNotFound = typed
Exit Function
End If

'add found text to what was typed, keeping capitalization of whats
been typed
Screen.ActiveForm(strBox) = typed & Mid(rstTable.Fields(strField),
cursor + 1)

'set highlight from cursor on
Screen.ActiveForm(strBox).SelStart = cursor
Screen.ActiveForm(strBox).SelLength = 100

End Function