Selecting from a combo box with a large selection list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a como box set up in a subform to select a value of a conference
session to assign to a speaker. The trouble is, that each time we hold a new
conference we establish several hundred more sessions to add to the list. So
the list is becoming unruly. As a temporary solution I built a statement in
the combo box to first show the event, then the session, and to sort first by
event and then by session. So that way you just have to scroll to the event
you're selecting from, and then scroll to the session. But, it's still a
chore

I'm explore two solutions, neither of which I've been able to get to work.
One is to have a command button that pops up a form that is set to filter by
event and year, thus giving a smaller list to choose from. The user can also
do a find directly on the session name. My goal was to have an "Okay" button
that links back to the original subform and populates with the value the user
selected. But I haven't found the right language for that.

My other solution was to use linked combo boxes, first the year, then the
conference, then the session, but I couldn't get three layers of linked combo
boxes to work.

I'm open to any solutions.
 
My other solution was to use linked combo boxes, first the year, then the
conference, then the session, but I couldn't get three layers of linked combo
boxes to work.
Click to expand...

That would be the simplest solution, IMHO. What did you try? There
should be no limit to the number of layers.

Each combo would be based on a Query referencing the "higher" combo
box (or boxes, depending on your table structure); you'll need to
requery all the "lower" combo boxes in the afterupdate event of each
higher combo.

John W. Vinson[MVP]
 
The problem I'm having with it, is that while I can get it to work in the
subform when opened by iteself, once I open the parent form with the subform
I get a prompt to enter the parameter value for two higher combo boxes.
 
The problem I'm having with it, is that while I can get it to work in the
subform when opened by iteself, once I open the parent form with the subform
I get a prompt to enter the parameter value for two higher combo boxes.
Click to expand...

The syntax for a control on a Subform is a bit more complex. The Form
being used as the Subform is not in the Forms collection, so the
syntax Forms!formname!controlname won't work. What you need is:

=[Forms]![mainform]![subform].Form![controlname]

where subform is the Name property of the Subform control (which may
or may not be the same as the name of the form within that control).

Alternatively, you can explicitly set the Rowsource property of the
dependent combo boxes in the AfterUpdate event of its controlling
combo, and in the subform's Current event.

John W. Vinson[MVP]
 
Back
Top