Can Anyone Improve This Utility?

[Guest]

fm_Main
txtFind
fs_SubForm (Datasheet - not linked to main form)
Displays a list of Australian Postal Codes, States & Localities (Suburbs)

As you type in the txtFind textbox, the list of matching Postal Localities
reduces.
I was having trouble dealing with the backspace key which now works with the
code shown below.

I was wondering if anyone could come up with a more elegant way to achieve
this.

fm_Main Code Sample (no code in fs_SubForm)
Option Compare Database
Option Explicit


Private msWhereCrit As String


Private Sub txtFind_KeyUp(KeyCode As Integer, Shift As Integer)
Dim sSQL As String

sSQL = "SELECT Locality, State, PCode, Category FROM PCodes " & _
"WHERE Locality like('*" & msWhereCrit & "*');"

txtFind = msWhereCrit
txtFind.SetFocus
txtFind.SelStart = Len(msWhereCrit)

fs_SubForm.Form.RecordSource = sSQL
fs_SubForm.Form.Requery

End Sub

Private Sub txtFind_KeyPress(KeyAscii As Integer)
Dim sSQL As String
Dim sKey As String

Select Case KeyAscii
Case 8
If txtFind = "" Then
Exit Sub
Else
msWhereCrit = Left$(txtFind, Len(txtFind) - 1)
KeyAscii = 0
End If
Case 32 To 126
sKey = Chr$(KeyAscii)
KeyAscii = 0
msWhereCrit = msWhereCrit & sKey
Case 127
' do nothing
Exit Sub
Case Else
KeyAscii = 0
Exit Sub
End Select

End Sub

 

[Allen Browne]

Use the Change event of the text box.

This event fires with every keystroke, but after the character is processed.
This solves the Backspace keystroke, etc. Just read the Text property of the
control for what's in there.

You might be interested in the code in this article:
Combos with Tens of Thousands of Records - Postcodes example
at:
http://allenbrowne.com/ser-32.html

The example uses a combo so it auto-completes and can identify the state and
postcode, but it suppresses the lookup until 3 characters have been typed,
since there are some 10k Australian localities that have postcodes.

 

[Guest]

Hi Allen,

Thanks for your quick responce.
I've just spent the last hour browsing your great website... Packed with
useful tips and workable code snippets.
--
Thanks
Graham

Use the Change event of the text box.

This event fires with every keystroke, but after the character is processed.
This solves the Backspace keystroke, etc. Just read the Text property of the
control for what's in there.

You might be interested in the code in this article:
Combos with Tens of Thousands of Records - Postcodes example
at:
http://allenbrowne.com/ser-32.html

The example uses a combo so it auto-completes and can identify the state and
postcode, but it suppresses the lookup until 3 characters have been typed,
since there are some 10k Australian localities that have postcodes.