Simple AutoComplete TextBox
No attachments for this post
This code will autofill a textbox from a database table using the keyup event.
Original Author: Darrin
User input into textbox and database via ADODB recordset.
It will autofill the textbox with the the first match of the letters the user types in.
I used the keyup event to keep it very simple, although if a user types quickly, and presses a key before the previous key is released (keyup'd), it will cause and error and clear the textbox.
Place the variables that are in ALL CAPS as global variables, and set STRNAME = "" and the INTPLACE = 0 on the GotFocus and the LostFocus events of the textbox.
You will also need to set "cn" = to an ADODB connection to your database.
Private Sub Textbox1_KeyUp(KeyCode As Integer, Shift As Integer)
Dim rsTable as ADODB.recordset
Set rsTable = New ADODB.recordset
On Error GoTo ENDOFSUB
rsTable.Open "Select * from TABLE", cn, adopenstatic, adlockoptomistic
STRWORD = Me.textbox1.Text
If Len(STRWORD) < INTPLACE Then
INTPLACE = Len(STRWORD) - 1
If KeyCode = vbKeyBack Or KeyCode = vbKeyLeft Then
If INTPLACE > 0 Then
INTPLACE = INTPLACE - 1
STRWORD = Mid(STRWORD, 1, Len(STRWORD) - 1)
ElseIf Me.textbox1.Text = "" Then
INTPLACE = 0
STRWORD = ""
ElseIf KeyCode <> vbKeyDelete And KeyCode <> vbKeyShift Then
INTPLACE = INTPLACE + 1
STRWORD = STRWORD & Chr(KeyCode)
If Me.textbox1.Text <> "" Then
Do While Not rsTable.EOF
If Mid(Trim(rsTable!Field1), 1, INTPLACE) = UCase(Mid(Me.textbox1.Text, 1, INTPLACE)) Then
Me.textbox1.Text = Trim(rsTable!Field1)
If KeyCode <> vbKeyShift Then
Me.textbox1.SelStart = INTPLACE
Me.textbox1.SelLength = (Len(Me.textbox1.Text)) - INTPLACE
Me.textbox1.Text = ""
INTPLACE = 0
Comments on this post
No comments have been added for this post.
You must be logged in to make a comment.