Please advise on best practice for memory intensive project

[David]

I'm assigned a challenging project that may consume vast memory resources,
and am turning to the community for recommendations on the most effective
design.
I'm sure I'm going to hit some kind of upper limits on this, and am worried
about it.
I'm working in W97 because I can't be sure which ver the user will have, so
I'm going for the lowest common denominator, which is another limiter, as is
the fact that I won't know how much RAM the user has.

An template will have 5 userforms, each with a CB to choose from a different
list of thousands of choices (different categories of medical codes; e.g.
Meds, Diagnoses, Symptoms, etc). The typist will hotkey the forms while
transcribing to insert data from the lists.

In the past I've opened a word doc with the data in a table, arrayed it, and
pop'd the CB with the array. It's not going to work here, because of the
sheer volume, and the long time it takes to load these, albeit necessary only
once in a session.

What do you think is the best way to populate these CBs with so much data?
ADO?
What techniques can be used to consume the least amount of resources?
Maybe I'm barking up the wrong tree altogher with my concept?

I greatly appreciate any and all advice on this, and thank you in advance...

 

[Jezebel]

As a quick answer, I would say that CBs (I assume you mean comboboxes) with
thousands of options will be unworkable, for two reasons --

1. Even if the total memory issue is manageable, the response time is likely
to be unacceptable, and
2. 'Thousands' is too many options for that style of data entry. It's just
too damned hard for the user.

You can test the memory side of it by writing some loops to fill the CBs
with junk -- see what happens.

But given the importance of the project, you need to solve the user
interface side of the task before you worry about the coding. Sounds like
you need to bring a lot of imagination and creativity to bear before you
start thinking about code. Be afraid. Be very, very afraid. Seriously
though, every hour of interface planning work saves you ten hours of coding
over the life of the project.

 

[David]

Yes I meant to abbreviate ComboBox; sorry for being unclear.
As for the rest, you've summed up the exact reason for my post.

 

[Kodeworks]

David

You can try populating the combobox list with items only after the user
has keyed in the first few characters. Depending on the contents of
your list of thousands of entries, choose the minimum number of
characters to match so that the combobox list is reduced to tens of
entries. Check for the length of the text in the combobox in the
AfterUpdate event and populate the list when it hits the requisite
number of characters. Even on a slow machine, generating a list of
tens of entries shouldn't take more than a couple of seconds.

Sunil Jadwani
Kodeworks - Business Automation Solutions
www.kodeworks.com

 

[David]

Sunil,
This is a great idea.
I was working on a similar concept where a pre-userform would prompt for a
search string to narrow the list.
Your solution is more sophisticated and elegant.

Many thanks

 

[Chuck Henrich]

Following on from Kodeworks' suggestion, you might want to examine the
format(s) of the codes that you'll be populating the combo boxes with. If
there are patterns (for instance a relatively limited number of prefixes) you
might consider adding radio buttons to your userform for the prefixes and
then populate the combo boxes based on which radio button the user selects -
if each radio button has a unique accelerator key that should accommodate
keyboard entry. Using radio buttons as a filter might help avoid problems
arising from typos and should also cut down the number of codes you need to
populate your combo box with.

Even if the radio buttons aren't a good idea for your application, it might
be worthwhile to analyse the data to see what patterns you can use to create
preset filters that cut down the data to be populated into manageable chunks.
I would think that filtering in the combo boxes themselves might cause
annoying time delays for data entry users working at speed.

HTH

 

[David]

Thank you.
You nailed it when you said 'used to working at speed'.