Combo Box Problem

Is there a limit to the number of rows a combo box can contain within
Access 2002?
I have a list of drug names with all the NDC codes which contains over
200,000 records. I do have this filtered to only non-obsolete drugs
(80,000). I can only get around 65,000 records to poplate in the combo
box. I can type the name of the drug in and not get any errors but can
only scroll through the first 65,000.
Any ideas on how to list all rows?

thanks

re: Combo Box Problem

Gosh, just my 2 cents here, but the combo box control really isn't
intended to show tens of thousands of entries. When you're dealing with
such a large number of items, it's best to try something else - like
leaving your combo box with no source until the user types in a couple
characters and then limiting their choices by what they've typed in.
For example, they type in 'Ad' and you change the query of your combo
to be "select mydrug from drugs where drugname like 'Ad*' order by
mydrug" You can use the on-change event to capture the user typing in
the control.
-td

re: Combo Box Problem

Thanks td. I'll give that method a try.

re: Combo Box Problem

Well that almost works. Maybe I need to be more descriptive since this
is rather tricky.
I have a continuous form that displays all the meds a patient may have
ever been on. For each row there is a combo box that stores the NDC
code for the med and displays the medication name. When a user enters a
new record I want that combo box to be display only non-oboselet meds.
Otherwise there is almost 300,000 meds in the list. But if they are not
entering a new record the combo box's row source must be all meds
otherwise I end up with no med name displayed for records were the med
is obsolete.
Is there a way that I can have the rowsource as all meds and some how
filter that list when entering a new record without messing up what's
displayed in the previous records?

thanks for your help.
Your suggestion made me approach this in a new way and it might just
work.

mike

re: Combo Box Problem

Ah, the dreaded "combo box in a continuous form" problem... I see the
trouble. Here's my recommendation: First, join to your meds table in
the source for your continuous form so you can display a field with the
full name of the medication. No combo box required. This field will not
be editable, however. What I often do in this case is to put a very
narrow combo box just to the right of this field, one that I adjust the
rowsource of depending upon the row the user is on and that is bound to
the underlying field. BUT, you've got a problem in that you want them
to type in and have the source limited... argh.

At this point I see a couple options: a little "edit" (or "find" or
something) button next to the drug name that pops up a small form for
selecting the drug, where you can use the type-ahead thing we talked
about. Or, you could change things around so that editing of these
records doesn't happen in the continuous form, but in a little
one-record form you place below your continous form. A bunch of work
and not nearly as elegant, though.

I hope these ideas help you come up with something... like a letter to
MS for a sexier combo box control that will handle these very common
cases...
-tom

re: Combo Box Problem

argh!! Precisely put. I came up with a solution although it slows the
system down rather considerably. Good thing this feature will be rarely
used. I am using a variety of RowSource statements for various events
to both form and field to get the job done. The bottome line, it works.
I did considered using a seperate form for selecting the meds but I
just didn't want to do that. I think that this problem is going to be
passed off to the next set of developers. They are using vb.net for the
front and MSDE for the back end. They get to look at my work and laugh
at the complexity of it. Some things in Access just have to be done the
long way. I am developing the pilot version that they needed finished
on the yesterday timeline.
I think a letter is definitely in order. Although I'm sure it will get
filed in the circular bin.

Thanks for you input and the break from the grind.

mike