Access 2007 Autocomplete Unbound Text Box with relevant names from a table

[wm138]

Hello,

I am building my first database and need some help. I have a form set up with an Unbound Text Box that I use as a search box. When I need to search for a name, I simply type the name in the text box, click submit, and the record of the company name appears in the next form, Company information and all. Unfortunately, when a company name is mispelled, the next record will appear blank.

I would like to set up my text box to autofill or auto complete or give suggestions as the user is typing the company name. For example, if a user types "a", the company names that begin with the letter "a' will appear. The user now can choose from the list provided in order to prevent error. I am currently using Access 2007. Any suggestions would be greatly appreciated.

Thanks you.

[JoeM]

Instead of using a Text Box, use a Combo Box, and populate it with the list of your company names. Then they will just be selecting the company name and not typing it in (and it will exhibit the behavior you want, where if they type in a letter, it jumps to the entries beginning with that letter).

[wm138]

Thanks for the replay JoeM,

Unfornately, I am constantly vreating new company profiles. My co-workers, the users, are not trained to add a company name to the combo box field when a new company profile is made. iI under stand what your saying but I don't think my boss wants it done that way. She wants it to begin to populate the text box when the user begins to type the name.

[JoeM]

You don't need to manually add it to the Combo Box, it can be dynamic. You can use a query as the Source of your Combo Box so it is dynamic (all new company names are automatically added to the list).

Let's say that your table with the companies listed is called "tblCompanies". Then the source of your Combo Box would be a query that looks something like this:

SELECT CompanyName
FROM Companies
ORDER BY CompanyName

[wm138]

I tried it and it works great thank you!

[anashashmi]

You don't need to manually add it to the Combo Box, it can be dynamic. You can use a query as the Source of your Combo Box so it is dynamic (all new company names are automatically added to the list).

Let's say that your table with the companies listed is called "tblCompanies". Then the source of your Combo Box would be a query that looks something like this:

SELECT CompanyName
FROM Companies
ORDER BY CompanyName

Hate to correct here: but you might want to add DISTINCT to companyname. Otherwise you will have repeats.

Btw, you can give each field in a table a lookup property of list or combo with the source being a query. The query can run on the table itself. So as you add more data, the query updates automatically.

[JoeM]

Hate to correct here: but you might want to add DISTINCT to companyname. Otherwise you will have repeats.

If the Companies table is designed correctly to not allow duplicates in the CompanyName field, this should be a non-issue.