Well, if you have a second subform on your main form to collect interested
"Other Committees," make the "Other Committees" field in the last subform a
combobox based on a new lookup table including only names of "Other
Committees." You will need only ONE combobox which will be your "Other
Committees" field/control. Use the Combobox Wizard to build your combobox
from the new table. In your 1st subform, select one of your "Documents."
Go the 2d (last) subform and open the combo box and make as many selections
(from the list of all your Other Committees) as required to populate your 2d
subform.
Your 1st subform (Documents) can contain hundreds of records relating to ONE
record in your main form (Committees). Your 2d subform (Other Committees)
can accept hundreds of records relating to ONE record in your 1st subform
(Documents).
Denny G.
"Tony Williams" <Ta***********@btinternet.com> wrote in message
news:bj**********@titan.btinternet.com...
I have worked out the relationship situation and the use of subforms but
my problem now is how does the user choose which are the interested
committees when they are inputting a document record on a form? As I said there could
be 1 or say 10. Is the answer to create as many combo boxes as there are
possible interested committees? This seems a bit cumbersome and what if
there are more interested committees than I have comboboxes?
Tony
"Tony Williams" <Ta***********@btinternet.com> wrote in message
news:bj**********@hercules.btinternet.com... I have two tables one lists the names of committees and the other is a
list of documents they generate. I have a form based on the documents table
which gives details of the document including which committee generated it. On
the form I also want to indicate which other committees are interested in
that document, there could be 1 or say 8, it differs for each document. I had
thought of creating say 10 combo boxes based on the committee table
called comm1, comm2 comm3 etc and using them to store the other committees but
this seems a bit cumbersome and if say all the committees are interested, and
there are upto 30, then that approach would not be enough.
Anyone any ideas how I could work this? I am a relative newbie,
particularly at VBA!!!!
TIA
Tony Williams