AutoComplete Entry in Text Box or Drop Down List

[Kevin E.]

I am creating a report in Excel which i am linking to a database containing
part numbers and descriptions. My question is how can i program a text box
or drop down list to "auto complete" a part number after typing the first
three characters of a part number. Or, better yet is there a way i can
populate a drop down list of part numbers that begin with the first three
typed characters of the part number. Any suggestions or comments are greatly
appreciated, thanks.

 

[dan dungan]

How is the report linked?

Are you using a user form?

I think a combobox from the Control Toolbox in Excel 2000 can do that.

 

[Kevin E.]

to create the link to the database i created a recordset object:
Dim rs as Recordset
rs=CreateObject("ADODB.Recordset")
rs.Open "TableName", "ConnectionName"

I am not using a user form, I am trying to fill excel cells with info from
the acess database. I could set a range of cells in excell to be the source
for the Combo box but i dont want the overhead created by bring info over
from the database to excell and then using that to fill the combo box. there
are over 50,000 database entries in the Recordset, so i may have to look at
this problem from a different angle....

 

[dan dungan]

I'm wondering what stops you from creating the report in Access.

Does the Excel report do something that Access doesn't do?

Is it that the users don't have Access or are more familiar with Excel?

 

[Kevin E.]

I'm trying to created the report in Excel because I'm more familiar with that
than i am with access. Maybe i should familiarize myself with access and see
if it would be easier to finish my project with it.

 

[dan dungan]

In my project, I'm going the other way. I take excel data and make the
report in Access. I like the report feature in access because you can
stop users from changing the layout. Also you can have multiple users
at the same time.

It's more difficult to set up initially. But once it's finished, it's
solid.

You could put a combo box on a form in access and launch the report
based on a query.

I'm trying to figure out how to move the whole project to Access.

 

[Hiran]

I have the same challenge as Kevin E.

Access is not an option because the users' work environment of choice
is Excel. But in my app they work in a multi-user architecture as all
the data is saved and retrieved from a central SQL Server database,
which would be where the look up table for the list sits.

The aim, for me, is something like how eg. iTunes Store auto-
completes. The key challenges are: get Excel to send the part entered
into a cell so far - on hitting each character. Perhaps populate a
(reusable) range with the returned recordset (via ADO as Kevin
describes) which is the source for an in-cell dropdown.

Any ideas please? Kevin? Anyone got an existing sample?

Hiran
(e-mail address removed)