Autocomplete for a textbox in Access 2010

[langianom]

Hello,

This question has been posted I am sure, but the search criteria I am using is not coming up with any threads. Sorry if this is redundant. I have found this question on other sites, but they did not seem to help me. I am wanting a textbox field on a form to autocomplete. In other words, if I were to enter the letter J into this field, an autocompletion of the alphabetically first entry in the table for that field beginning with J is filled in, and the list would get refined based on further letters entered.

I have figured out how to do this with a Combo Box, but not a Text Box. I tried to apply the same configuration I did for the Combo Box for the Textbox, but I cannot figure out how to set the Row Source and Row Source Type for a Textbox. I would just use a Combo Box, but the administrators above me want it to be a Textbox for cosmetic reasons.

I am using Access 2010 Pro and Windows 7 Pro, I also have Office 2013 Pro if need be. I have a form called Computers, I have a table called AllFields, and the AllFields table contains all of the available fields I need for the form. The field I am working with has a name of Username.

For the Combo Box that I created that auto complete is working on, here is what I did. I created the Combo Box on the form, set the Row Source Type to Table/Query and the Row Source to SELECT AllFields.Username FROM AllFields WHERE (((AllFields.Username) Like "*" & [Forms]![Computers]![Username].[TEXT] & "*"));

I then setup an After Update Event Procedure with the following code:

Private Sub Username_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
Username.Requery
End Sub

If the after update code was not added, it was going to make me close the form to save the info in the table in order for the newly added data in the table could auto complete. This works perfectly. My goal is to do this same thing but with a Text Box. I tried doing this exact same thing with another field called Location that is setup as Text Box, but it will not auto complete. Other than it not being a Combo Box, the only thing I can see is different is the inability to set the Row Source Type and Row Source Criteria.

I tried creating a Query with the same Row Source criteria as the username field above, but altering the field name to be Location and it still would not work.

I have not used MS Access in 10 years, but still remember a lot of the basics. I am a VBA extreme novice and everything I have figured out to make the Combo Box work was from You Tube videos and forum searches. This tactic does not seem to be helping me with the Text Box feature. I would also be up for a good way hide the drop down arrow of the Combo Box as a last resort. I have tried the putting a Text Box on top of a Combo Box trick and it just seems like a dirty way of doing it.

Anything advice that can be offered would be much appreciated.

Thanks,
Matt

[jonh]

"but the administrators above me want it to be a Textbox for cosmetic reasons."

Just tell them it can't be done. For technical reasons. Job done.

[HiTechCoach]

A combo box does auto complete automatically. It is built in. No VBA code required. Simply set the Auto Expand property to YES.



ComboBox.AutoExpand Property (Access)

You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box. This lets you quickly enter an existing value in a combo box without displaying the list box portion of the combo box. Read/write Boolean.

from: https://msdn.microsoft.com/en-us/library/office/ff845130.aspx?f=255&MSPPError=-2147217396



The built in Access textbox control does not support auto expand like a combo box. It is possible to get similar results with a text box is you are willing to write some the VBA code to do it. I have done it but the decrease in performance was huge with a multiple user database. I found it was just to costly to implement.

If you want to give it a try then check out:

Code for Find As You Type utility (http://allenbrowne.com/AppFindAsUTypeCode.html)

[langianom]

Sorry it has been so long since I have replied. I had to put my access project on the back burner for a week or so because I was on the road traveling. I was able to finally convince the admins that a ComboBox was the way to go for this and all is well. Thank you HiTechCoach for your response. That looked really complicated and not worth the effort for a non-significant form. This thread can be closed.