I have the following situation and dont even know which tool to
use...excel? access? other?
I have a list of about 10,000 elements. These are chemical compounds. I
need to create an interface to be able to "assemble" the compounds so
that each compound shows its ingredients.
ex:
Lets say I have (in a huge column)

comp1, comp2, comp3, comp4, comp5, comp5, comp6

Now, I select comp1 to work on it... and then select the ingredients
that makeup comp1

comp1--comp2
comp3
comp4

Now I select comp2 and work with it...
comp2--comp4
comp5
comp6

Basically each of the compounds on this list is made up of one or more
compounds on the same list

So I was thinking I should have at least two listboxes. On the left one
I select which compound I would be working on, and then from the list
on the right (with multiselect on) I select all of its ingredients
(both listboxes would how the same columns of data).

Is this at all doable with about 10,000 elements?
I would also like to have a textbox that would work as a search box for
the right side listbox. It should work by simply detecting changes and
doing instant searches....so if I type "c" it would jump to the first C
in the list, but then I add an 'o' (now "co" on the textbox) it would
jump to the first "co" on the list...and so on...by just completing the
word it should jump automatically....is this possible? will it be done
in relatively decent amounts of time or will the 10,000 element list
be so huge it will take minutes to serach?

thanks,
-gguillermo

ok, I've tried it in Excel using the very basic knowledge I have of vba
(or programming for that matter)....and this is what I found...
the initial filling of the 10,000 element listbox is fast....but the
searches are extremely slow. Is there a better way to do this?
here is the code I have till now (it kind of works...it selects the
element right before the one I am looking for...but regardless of that,
its extremely slow)
-------------------------------------------------------------
Private Sub UserForm_Initialize()
With Sheet1
ListBox1.List = Application.Transpose(.Range(.Range("A2"),_
.Range("A2").End(xlDown)).Value)
ListBox2.List = Application.Transpose(.Range(.Range("A2"),_
.Range("A2").End(xlDown)).Value)
End With
End Sub

Private Sub TextBox1_Change()
i = 0
While TextBox1.Text < Left(ListBox1.List(i), Len(TextBox1.Value))
ListBox1.ListIndex = i
Wend
End Sub
----------------------------------------------------------------
Any suggestions?
thank you
-gguillermo

I just discovered the fmMatchEntryComplete feature for listboxes ...now
that one works fast, but....yes, there is a but.....I need to have the
text I am typing show up on a text box...and it has to be persistent
(fmMatchEntryComplete starts from blank after a couple of seconds).
I also need for the search to go backwards if I press the backspace
(not having to type the whole thing again....remember my list is made
of chemical compounds....hard stuff to type :) )
thanks again!
-gguillermo

Me again....
Ok, I just found the perfect example of exactly what I am looking
for....
in Outlook Express on the Newsgroup Subscriptions window....
there is a textbox on the top and a listbox on the bottom...on the
textbox
I type something and the listbox will show only the newsgroups that
include that text ANYWHERE in the name (not just the left side
(beginning)
of the string). I was able to do something like that using a clear
listbox
and an additem with a "for each" loop, but the result is extremely
slow.
In outlook express with 23000 groups it works decently fast....
any idea how I can achieve this with a listbox whent the elements come
from an excel column?
thanks again....hopefully somebody will read all this :)
-gguillermo

How are your compounds named ? IUPAC names or some other systematic
convention?

Do you need to be able to type wildcards in the textbox and match on
those?

You might start out by first loading all of your items in an array and
loading the listbox from there.
Each time you filter your array you could clear the list and reload
only the matching items.

Other possibility - use a disconnected ADO recordset to hold your
items. This has sort and filter methods which can (I think) work with
wilcards.


Tim.




"gguillermo" wrote in message
oups.com...
Me again....
Ok, I just found the perfect example of exactly what I am looking
for....
in Outlook Express on the Newsgroup Subscriptions window....
there is a textbox on the top and a listbox on the bottom...on the
textbox
I type something and the listbox will show only the newsgroups that
include that text ANYWHERE in the name (not just the left side
(beginning)
of the string). I was able to do something like that using a clear
listbox
and an additem with a "for each" loop, but the result is extremely
slow.
In outlook express with 23000 groups it works decently fast....
any idea how I can achieve this with a listbox whent the elements
come
from an excel column?
thanks again....hopefully somebody will read all this :)
-gguillermo

Hi Tim, yes, IUPAC names (in spanish). It would be nice to be able to
type wildcards, but its not a necessity right now. In order of
importance, id say the "features" i need are like this
1) be able to do the same as fmMatchEntryComplete but by typing in a
textbox...i.e. it just takes me to the first element that starts with
those letters...but, if I delete a character it respects what is in the
textbox and searches again (like comboboxes work...but with the visible
listbox at all times)
2) be able to ONLY show the elements that start with those letters
3) be able to ONLY show the elements that contain those letters
4) be able to only show the elements that contain the letters using
wildcards.

If I can get any of these to work I'd be pretty happy....but it has to
be really fast. I was able to get option 2 to work, but its so slow its
ridiculous.

If I do the array option, will that work faster? How would I filter the
array (I am new to programming)...I will search newsgroups to find that
info.
I know ADO recordset refers to something like Access, but I have no
idea how I would get that to work...would I need excel at all or all
within access? or what exactly did you have in mind there?
thank you,
-gguillermo