You said
The "solutions" I've found from a Google search all say that
> the user should write the data to a sheet and "bind" the
> records to the listbox. This is impractical, since the
> starting record count exceeds the maximum number of rows in
> an Excel worksheet.

Do you mean you are going to have more than 65536 rows in the listbox?

If not, do what you need to do to get the array containing the data you want
in the listbox, write the array to a sheet and set the rowsource for the
listbox to the sheet. I think that should work.

"Therein lies the rub" or something like that.
(with apologies to Shakespere)

I KNOW the other lists are inferior.. No doubt in MY mind, that's why I
participate as much as I do with THIS one.
But, in order to "execute due diligence", I had to attempt a Google search in
order to at least attempt to find it on my own before posting the problem to the
group! But alas, the others seem to be confined to a single solution, no matter
what the circumstances!

The original data set in Oracle has over 700,000 records.
The "filter" fields are used to issue a select statement to retrieve records
from the Oracle database.
Depending on the number of filters, the number of records returned should be
reduced.
But initially, the number of records in the listbox  could exceed the 65536 row
limit.

For instance, the user says indicates that he's only interested in the records
for department #2101.
The Select returns 231,400 records.
Of these, he's only interested in those for product code 046, so the sub-select
returns 121,000 records.
Limiting those records to only those processed in the last 90 days returns
16,000 records.
The user is interested in only those records exceeding $1,000. This returns 300
records.
He may choose to "export" these records to an Excel file for detailed
consideration, print the records, etc...

Now, of course he could enter these fields in any order until his list is
manageble, but he won't know what the minimum requirements are until he begins
entering the filters.

For instance, if he were interested in deparment #2180, the number of records
selected is only 4,000. This may be sufficient for his needs. He can sort the
list numerically and quickly locate the record he's interested in.

So far, in my testing, (on smaller data sets) it's quicker to select the data
from Oracle and clear and refresh the list than it is to create the listbox with
all data and then remove rows based on the selection criteria, but that's not
germaine to the issue at hand.

The listbox seems to function as I wish, except for the headings.



BTW: I've not come across a way to display column  "separators" or Excel-type
column "borders" in the listbox. Is there another term or parameter that I
don't recognize? It sometimes is another "feature" entirely that just happens
to achieve the desired effect.

Also, do you know of a "click" event that relates to the headings in particular?
I'm considering coding sort functions by clicking the column headings and have
the list data sorted by the column values.

(I like creating multi-functional applications, and enjoy it when a user tries
something that makes perfect sense to them and I have anticipated their twisted
sense of potential!)

I develop similar 'data mining' tools for my support group!
This is cool stuff, going outside of the Oracle application to get the
information of interest (which also happens to be faster & less tedious; at
least for my taste!).
Are the filter fields selectable in any order & combo? And, is a query issued
after each drop-down selection?
Anyway, I am an interested peer in your quest for a solution!

Every time you try to process tens of thousands of records in VBA, you are
going to be really slow.

I would suggest thinking about doing the drill-down without actually
retrieving the data, just indications of the number of records that the
filter would select if you tried to use it.

I'd need to have a play to work out the right SQL, but you're probably
talking about Count() clauses in the SQL coupled to either "select distinct"
or "group by" clauses.

Once the user has filtered down enough that the result set is manageable,
you can then do an automatic select of the actual data. You could easily
also allow the user to force the select on "unmanageable" quantities,
although a sanity check would be appropriate to stop the user selecting too
much. E.g. automatic if <=100 records, manual allowed if <=1000 records.

Remember also that the administrators of the database (I assume it's a
corporate one) are not going to take kindly to selects that return tens of
thousands of records.

How about only retrieving a suitably small subset, so that the user
sees what the records look like, along with a message like "1000 of
170,000"? Sort of like what the Web search engines do...

Coming to this very late so sorry if I'm way off base here.

If I'm right then in your original post you just mention column
headings and I notice you have quite a few columns. Would it be too much of
a pain to try the process with only 8 columns and see what happens?

A new query is issued after every selection.
They may be selected in any order and combination.
The Oracle response is fast enough to allow for this approach.
My older Ingres database had performance limitations.

Somtimes, the user is looking for records for a given year,
for a given department. But, since some departments only have 5-8 records/year,
just selecting the department is sufficient to provide the info needed. Other
departments may have 3-4,000 records per year. so that selection is important.

Just a thought....
on a form, can the field 'header' be placed in the 'label' section above the
selection box?

That's precisely my point!
My SQL select statements are returning 200,000 records in 10-12 seconds.
VBA processing of these records is what takes so long!
It's much more time-efficient to re-issue the select than it is to use
VBA to do the filtering!

Still doesn't solve the problem.
With this solution, I would STILL have to write the records to an Excel sheet,
then bind the range to the listbox.
In some cases (rare) I may WANT to report on 100,000 records!
In which case I'm doing all of the summaries in VBA arrays without writing to a
sheet. Then only update the Summary sheet.

I'm just looking for a way to display column  headings without writing the data
to a sheet!

"I notice you have quite afew columns"...
If you only knew! I'm actually only using 8-10 for selection.
I have 72 fields to report!

From my testing, the problem with the column  headings is the same with 2 or 20
columns.

It seems to be an issue with how the column headings are defined.
when binding a range, I understand that you're not supposed to select the
heading as part of the range, the listbox  "infers" the header location.

when loading the listbox from an array, however, the listbox doesn't seem to
know where to look for the heading!
Just trying to figure out if I can define it another way....

I wonder if you could do something like have each field display in a
separate list box and sync the scrolling. Then you could have text boxes
instead of headers, and using a click-event would be easy. However I think
it would be too slow.

It may be that you will need to use a control that is outside the normal
range of VBA controls. I have not experimented with this, but perhaps
something like a flex grid control.

Not sure how my point and yours are the same. I'm saying never retrieve
large numbers of records at all, just retrieve counts. Your question was
regarding handling large numbers of records in Excel.

Why would you return 200,000 records at all? It won't help the user, who
certainly can't look at 200,000 records. If you want just the possible
sub-filter values for a given group of filters, use "select distinct" to
return just the sub-filter values.

The way that our two points are the same is, that they are directly opposite.
You're saying "don't ever retrieve large numbers of records" and I'm saying that
I have an issue because I HAVE to retrieve large numbers of records.

If I say, in order to do my job, I have to collect this information, to have you
say "well don't do your job" isn't going to sit well with my boss.

In this case, returning 100-200,000 records IS useful for the user.
It's for a governmental audit. We have to be able to produce a report based on
specific criteria, then print on PAPER the entire report. The auditors then
randomly select records from different areas (usually between 100 and 200 over a
4 week period) and check the record to see that the inventory matches the
report. This is an extreme example, not the daily use.

Using "select distinct" doesn't help.. these are distinct records...

Separate listboxes is awkward because I lose the horizontal scrolling capability
provided with the listbox.

I've been trying something though.
I've created textboxes above the listbox.
I then populate  the textboxes with the column  headings.
Now, when I scroll to the right, I have to determine which columns are currently
displayed and update the names in the textboxes.

Not sure if it's going to work, but I'm giving it a shot.

The suggestion about number of columns was because I believe there is an
issue with bound and unbound columns and more than 9 columns.

One way to get around  the scrolling is to not do it.
Provide [<< Page] [Page >>] buttons maybe?

If your design needs you to retrieve huge numbers of actual records, then
your design is wrong. (And this from a 40+ year professional
programmer/analyst.) SQL offers lots of ways of retrieving summaries of
information in the database, and these are ideal for successively refining
filters before doing the actual retrieval of the small number of records the
user wants to see.

> If I say, in order to do my job, I have to collect this information, ...

I imagine that your job is actually to construct a method of filtering
information so that the needed records can be found.

Anyway, if you don't want advice, I'll stop giving it.

Yeah, I thought of that.
Since the length of the record fields exceed the width of the listbox,
It has a horizontal scroll bar.
I'm playing with the idea of seeing if I can use a click event on the scroll bar
to also up date the header labels. (along with width, to match the field
widths).
It would just be so much easier if Excel would use the Array(0) element as the
header for the list!

Have you heard of an item in Excel called, a 'Dictionary'?
It has some properties like array but behaves a bit different. I have never
spent any time experimenting, but it may offer a solution.

I use VBA Dictionary object extensively.
They're very quick to return values by index.

I'm not sure how you're suggesting using one here though.