Textbox like combo?

  • Thread starter Thread starter **Courtney
  • Start date Start date
C

**Courtney

Hello,

I have a form that allows users to enter criteria for a
search. I was wondering if its possible to make a
textbox act like a combobox, ie: when they start entering
something in a field that matches a field in the table,
the whole thing appears for them (like a combobox), any
help would be appreciated, thanks.

Courtney
 
Fred,

Yes i know, but a combo box has predefined entries, id
like them to be able to write one of MANY field values
into the textbox rather then finding it on a list (even
tho it automatically searches), also its a keyword search
so they dont have to pick a predefined value either. I
dont want a combobox with thousands of values, id rather
they just write the beginning of a value and it will show
up in the textbox.. is it possible?

Courtney
 
Hi Courtney,

What you trying to do is to emulate the Intellisense function of many
applications. Unfortunately, there is no easy way to accomplish this in
Access without using the combobox control (or maybe a third party control).

You could write a function that will search a defined list on each keystroke
within a textbox and fill the text with the returned value. You would then
have to use the SelStart and SelLength properties of the textbox to select
the portion of the text the user did not actually type, so it can be
overwritten with the next keystroke.

So, the short answer is yes, it can be done. It just depends on how much
effort you want to put into it. :~)-

- Glen
 
Well, after my initial response in this thread, I became curious as to how
to accomplish this task. I came up with a workable solution. The following
code assumes that the values you wish to present to the user are present in
a table or query (or link to table or query).

Note that in the form, a global boolean variable is dimensioned to be used
for validation between the two events from the text box. In the KeyPress
event, it's set to true if character is pressed and ignored otherwise. In
the Change event, the flag is used to determine if either the keycode is
valid or if the text is being changed programmatically from the IntelliSense
procedure. Both events must be used in conjunction with the global boolean
to ensure proper processing.

Here's the code:

In the form:
----------
Option Compare Database
Option Explicit

Dim blnLookup As Boolean ' Global lookup flag

Private Sub txtMyText_Change()

If blnLookup Then
' Turn off lookup flag to keep from looping back
' when programmatically changing textbox contents
blnLookup = False
Call IntelliSense(txtMyText, "qryTest", "First")
End If

End Sub

Private Sub txtMyText_KeyPress(KeyAscii As Integer)

' If the keycode < 33, ignore it
blnLookup = Not Eval(KeyAscii < 33)

End Sub
----------

In a separate module:
----------
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~
' Procedure : IntelliSense
' Date : 7/21/2003
' Author : Glen Appleton
' Purpose : Present user with "first match" lookup value in a textbox
' Returns :
' Usage : Call IntelliSense(MyTextBox, MyTableOrQuery, MyFieldName)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~
Sub IntelliSense(MyTextBox As TextBox, TableName As String, FieldName As
String)

Dim intUserChars As Integer ' Count of characters typed
Dim intLength As Integer ' Count of result characters
Dim strSQL As String ' SQL string for recordset
Dim rsTest As DAO.Recordset ' Recordset (Data Access Object)
Dim strResult As String ' Result of lookup

' Get the first match (if any)
strSQL = "SELECT [" & FieldName & "] from " & TableName & " " & _
"WHERE ([" & FieldName & "] Like '" & MyTextBox.Text & "*') " &
_
"Order By " & FieldName & ";"
Set rsTest = CurrentDb.OpenRecordset(strSQL)
With rsTest
If Not .RecordCount = 0 Then strResult = .Fields(FieldName).Value
End With
Set rsTest = Nothing

' If a matching value was found, present it to the user
If Not strResult = "" Then
With MyTextBox
' Get the number of characters the user actually typed
intUserChars = Len(.Text)

' Get the size of the result
intLength = Len(strResult)

' Assign the result to the textbox
.Text = strResult

' Select the portion of the text the user didn't enter
.SelStart = intUserChars
.SelLength = intLength - intUserChars

End With
End If

End Sub
----------

If you have any comments or questions about the procedures above, please let
me know and I'll try to explain my logic. :~)-

Hope this helps,
- Glen
 
Back
Top