Browse All Articles > AutoComplete TextBox
When you look through the list of available controls in Access you will sooner or later come to one of the most comfortable controls, the ComboBox.
It has the ability to show you a list of records with multiple columns without the need to switch to another form to find the right one. And one of the really best functions of it is that you are able to enter a text and with every added character the ComboBox searches for any matching value in the bound or display column so you don't need to enter the complete text and you also don't need to open the dropdown list and search for the value manually.
Little side remark about the display column of the ComboBox, well known by any Access expert but not by beginners: The bound column is the column which usually is the ID of the record you really want to save into the table but you don't want the user to enter it as the user wants to work with the textual entry in the ComboBox.
If you set the first column (usually the bound column) to the ID column you would hide it with a setting of 0 as width. The next VISIBLE column > 0 is ALWAYS used as the column for the textual AutoComplete function of the combobox. But if you have a combobox which consists of Lastname and Firstname fields you maybe want to enter AND display "Smith, John", but the columns in the ComboBox should show "LastName | FirstName" as single columns.
In this case you would use a trick: Let the query assemble the two fields as Display column like this:
Now you can use the "Display" column for the AutoComplete function of the Combobox, the ID as value and separated columns in the Dropdown list.
But now back to the attached file.
The ComboBox has not only advantages, it also has disadvantages. One of them is that the number of records you can use to display in the list is limited, depending on the number of columns you specified and the length of the contents. So in my current project I want i.e. to select a customer but the list is around 45,000 entries and the ComboBox gave up at around 30,000 entries. So some customers could not be entered using the ComboBox.
The solution is to use the AutoComplete TextBox class in the attached database. It simply simulates the AutoComplete functionality of the ComboBox. You can use a table or a query for the initialization so the "Display" trick above would also work here.
The AutoComplete class simply opens a recordset at initialization time which is kept open as long as the object exists. If you enter anything into the TextBox control the recordset will be searched for the part of the entered text to find the first matching text.
That alone would not be enough because you don't want to enter the search text but the ID into the underlying table. So this TextBox used for AutoComplete is simply an unbound TextBox only used to search the ID. You need a second TextBox which is also assigned at initialization which can be a bound TextBox which is bound to the underlying ID field. In case of my customers example it now works with any number of records in the customer table and also you would use a visible ID TextBox here if the ID is also the customer number. So that would help to identify if you've selected the right customer as more than one name could exist with different customer numbers.
The class also allows to add as many additional TextBoxes as you want which would show additional fields of the table/query in further TextBoxes on the form so you can see all the other columns at the same time, like with in the Country Search demo form in the attached database, it also displays the ISO and ID field of the used table.
The Country Search demo form also shows that you can use the TextBoxes in a double manner: The international control uses one AutoComplete object to enter the value using the international country name and uses the German one to display the German column as additional TextBox only, the German control uses another AutoComplete object to do it vice versa. So that offers a lot more possibilities for comfortable input.
But there's also a problem which the AutoComplete TextBox cannot handle: It can only find the first matching value. If you have i.e. three customers with the same name in different cities with different IDs it can only find the first in the list. The solution is simple: Use a CommandButton next to the TextBox and set the caption i.e. to "v". That looks very similar to the ComboBox dropdown button. When the user clicks the button, open a continuous selection form which contains all customers and position the record directly to the ID the AutoComplete TextBox has returned. If the continuous form is ordered by the same criteria as the query used in the AutoComplete TextBox the user can now select the right customer with any function in the popup-form and you can set the needed values into the bound ID TextBox when the form will be closed.
On this way you have the advantage of a quick search with the AutoComplete TextBox and a detailed search with a continuous form with full graphic and comfort functions to find the right customer if needed.
I successfully use this method for some years now in a production database and the users find that very helpful.
The attached file also contains a class variant for ADO if you use a database server or work with ADPs. The recordset here is an ADO recordset and the functions to check if the field exists in the table/query are deleted because this is different in different target databases so there would be no general way to perform this check.
The class is disabled completely with a comment block, because you cannot use that in the same database as the DAO variant. You can of course create a mixed class which can use this or that if you want but that's on you.
You can use the classes like you want as long as the comments on module level remain unchanged.
Have fun in experimenting with the new AutoComplete TextBox.
Christian
TextboxAutoCompleteClassV1-0-EN.zip
It has the ability to show you a list of records with multiple columns without the need to switch to another form to find the right one. And one of the really best functions of it is that you are able to enter a text and with every added character the ComboBox searches for any matching value in the bound or display column so you don't need to enter the complete text and you also don't need to open the dropdown list and search for the value manually.
Little side remark about the display column of the ComboBox, well known by any Access expert but not by beginners: The bound column is the column which usually is the ID of the record you really want to save into the table but you don't want the user to enter it as the user wants to work with the textual entry in the ComboBox.
If you set the first column (usually the bound column) to the ID column you would hide it with a setting of 0 as width. The next VISIBLE column > 0 is ALWAYS used as the column for the textual AutoComplete function of the combobox. But if you have a combobox which consists of Lastname and Firstname fields you maybe want to enter AND display "Smith, John", but the columns in the ComboBox should show "LastName | FirstName" as single columns.
In this case you would use a trick: Let the query assemble the two fields as Display column like this:
SELECT ID_Person, [LastName] & ", " & [FirstName] As Display, LastName, FirstName FROM tblPersons ORDER BY LastName, FirstName)0,0.01,3,3Now you can use the "Display" column for the AutoComplete function of the Combobox, the ID as value and separated columns in the Dropdown list.
But now back to the attached file.
The ComboBox has not only advantages, it also has disadvantages. One of them is that the number of records you can use to display in the list is limited, depending on the number of columns you specified and the length of the contents. So in my current project I want i.e. to select a customer but the list is around 45,000 entries and the ComboBox gave up at around 30,000 entries. So some customers could not be entered using the ComboBox.
The solution is to use the AutoComplete TextBox class in the attached database. It simply simulates the AutoComplete functionality of the ComboBox. You can use a table or a query for the initialization so the "Display" trick above would also work here.
The AutoComplete class simply opens a recordset at initialization time which is kept open as long as the object exists. If you enter anything into the TextBox control the recordset will be searched for the part of the entered text to find the first matching text.
That alone would not be enough because you don't want to enter the search text but the ID into the underlying table. So this TextBox used for AutoComplete is simply an unbound TextBox only used to search the ID. You need a second TextBox which is also assigned at initialization which can be a bound TextBox which is bound to the underlying ID field. In case of my customers example it now works with any number of records in the customer table and also you would use a visible ID TextBox here if the ID is also the customer number. So that would help to identify if you've selected the right customer as more than one name could exist with different customer numbers.
The class also allows to add as many additional TextBoxes as you want which would show additional fields of the table/query in further TextBoxes on the form so you can see all the other columns at the same time, like with in the Country Search demo form in the attached database, it also displays the ISO and ID field of the used table.
The Country Search demo form also shows that you can use the TextBoxes in a double manner: The international control uses one AutoComplete object to enter the value using the international country name and uses the German one to display the German column as additional TextBox only, the German control uses another AutoComplete object to do it vice versa. So that offers a lot more possibilities for comfortable input.
But there's also a problem which the AutoComplete TextBox cannot handle: It can only find the first matching value. If you have i.e. three customers with the same name in different cities with different IDs it can only find the first in the list. The solution is simple: Use a CommandButton next to the TextBox and set the caption i.e. to "v". That looks very similar to the ComboBox dropdown button. When the user clicks the button, open a continuous selection form which contains all customers and position the record directly to the ID the AutoComplete TextBox has returned. If the continuous form is ordered by the same criteria as the query used in the AutoComplete TextBox the user can now select the right customer with any function in the popup-form and you can set the needed values into the bound ID TextBox when the form will be closed.
On this way you have the advantage of a quick search with the AutoComplete TextBox and a detailed search with a continuous form with full graphic and comfort functions to find the right customer if needed.
I successfully use this method for some years now in a production database and the users find that very helpful.
The attached file also contains a class variant for ADO if you use a database server or work with ADPs. The recordset here is an ADO recordset and the functions to check if the field exists in the table/query are deleted because this is different in different target databases so there would be no general way to perform this check.
The class is disabled completely with a comment block, because you cannot use that in the same database as the DAO variant. You can of course create a mixed class which can use this or that if you want but that's on you.
You can use the classes like you want as long as the comments on module level remain unchanged.
Have fun in experimenting with the new AutoComplete TextBox.
Christian
TextboxAutoCompleteClassV1-0-EN.zip
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)