listbox in access 2000+

[chuck]

Is it possible to overcome the 65535 row (max integer) limit for a listbox?

TIA for any help.

Chuck

 

[Ken Snell \(MVP\)]

No.

 

[John Vinson]

Is it possible to overcome the 65535 row (max integer) limit for a listbox?

TIA for any help.

Chuck

No, and I cannot IMAGINE any user EVER being willing to scroll down
even a tenth as many rows as that, or even using the (single
character) typeahead.

Could you explain the real-life problem you are attempting to solve?
I'm sure there is a solution *not* involving a nine hundred foot tall
listbox.


John W. Vinson[MVP]

 

[chuck]

No, and I cannot IMAGINE any user EVER being willing to scroll down
even a tenth as many rows as that, or even using the (single
character) typeahead.

Could you explain the real-life problem you are attempting to solve?
I'm sure there is a solution *not* involving a nine hundred foot tall
listbox.


John W. Vinson[MVP]

Problem is selecting a set of attendees to enroll in a selected seminar
- attendees in one list, seminars in another, on a form. Select a bunch
of attendees in a multi-select list, select a seminar in the other list,
click a button to enroll them all. Not a problem unless there are more
than 65535 attendees. Scrolling is actually pretty quick even with a
huge listbox. Could use a datasheet (formatting capabilities limited,
but not a showstopper), but can't multi-select non-contiguous rows that
way. Anyway, it's a real-world problem: the alternative is to
select/register one attendee at a time. Again, not a showstopper, but
kind of a simplistic, brute-strength solution. I was looking for
something a little less user-hostile.

Thanks in advance for any ideas.
Chuck

 

[Ken Snell \(MVP\)]

If you're using ACCESS 2002 or 2003, I have a sample database that uses a
combination of a textbox and a combo box to allow initial filtering so that
the limit is not exceeded:
http://www.cadellsoftware.org/SampleDBs.htm#CombinedTextComboBoxes

This could be modified to work with a list box instead of a combo box if
desired; however, Arvin Meyer (MVP) has posted a sample database that does
something like this already:
http://www.datastrat.com/Download/ExpandingSearch2K.zip (ACCESS 2000
version)
http://www.datastrat.com/Download/ExpandingSearch97.zip (ACCESS 97
version)
--

Ken Snell
<MS ACCESS MVP>

 

[MikeB]

http://www.mvps.org/vbvision/grouped_demos.htm

Scroll to the "PopUp Listbox" example.

In there is VB code that is likely to work in VBA...

 

[MikeB]

http://www.mvps.org/vbvision/grouped_demos.htm

Scroll to the "PopUp Listbox" example.

and the two other sequential examples following that one..

 

[John Vinson]

Problem is selecting a set of attendees to enroll in a selected seminar
- attendees in one list, seminars in another, on a form.

OK... point taken. <g> I see you have some suggested solutions for
the listbox, but let me toss out another: a continuous (not datasheet)
Subform based on an Outer Join query listing all attendees, joined to
the table of seminar attendees, sorted alphabetically. If the Query
includes both tables' AttendeeID it will be updatable, and selecting
any field in the seminar-attendance table (a checkbox say) will
"dirty" that record, causing a new record to be created in the
many-side table.

John W. Vinson[MVP]

 

[chuck]

OK... point taken. <g> I see you have some suggested solutions for
the listbox, but let me toss out another: a continuous (not datasheet)
Subform based on an Outer Join query listing all attendees, joined to
the table of seminar attendees, sorted alphabetically. If the Query
includes both tables' AttendeeID it will be updatable, and selecting
any field in the seminar-attendance table (a checkbox say) will
"dirty" that record, causing a new record to be created in the
many-side table.

John W. Vinson[MVP]

Wow - I got a lot of good answers to what I was afraid was a really dumb
question! The responses are going to take some time to check out, I'll
reply when I know how they helped solve the problem. Thanks to everybody
for your great responses. More later...
Chuck

 

[chuck]

OK... point taken. <g> I see you have some suggested solutions for
the listbox, but let me toss out another: a continuous (not datasheet)
Subform based on an Outer Join query listing all attendees, joined to
the table of seminar attendees, sorted alphabetically. If the Query
includes both tables' AttendeeID it will be updatable, and selecting
any field in the seminar-attendance table (a checkbox say) will
"dirty" that record, causing a new record to be created in the
many-side table.

John W. Vinson[MVP]

John:
I tried this in several variations and the recordset was never
updatable. The SQL I used looked like this (I added a Yes/No field to
each table & tried clicking each one of them):

SELECT * FROM [Attendees] LEFT JOIN [Registrations] ON [Attendees].[ID]
= [Registrations].[Attendee] ORDER BY [Attendees].[LastName],
[Attendees].[FirstName], [Attendees].[MI];

([Attendees].[ID] and [Registrations].[Attendee] are autonumber fields).

I tried left and right joins and select distinct, all with the same
result - recordset not updatable.

In any case, I couldn't figure out how to apply this recordset to my
situation, since what I get (with a left join) is a set of rows with an
entry for each attendee/seminar combo, plus a single row for each
attendee not registered in any seminar which is a larger list than just
[Attendees] only! Since attendees can register for many seminars, it
seems a list of multi-selectable attendees on one side and a list of
multi-selectable seminars on the other, with the ability to select
either 1 attendee & several seminars, OR 1 seminar & several attendees,
then a button click to perform the registrations (automatically
waitlisting, avoiding dupe registrations, etc., all the stuff associated
with this kind of app) is the approach to take.

Did I misunderstand your reply (I'm great at not understanding stuff!),
or not explain my issues well (I'm great at that, too!) -

I appreciate any thoughts and/or assistance, & I REALLY appreciate you &
the other MVPs efforts to overcome MS Access' many weaknesses &
shortcomings; I'd have given up on Access long ago if it weren't for
Lebans' site!!