John
I have two tables A and C that are linked with a one to many relationship to a
center table B: A < B > C. I then have created a query to provide the link
between the many to many relationship of the two outer tables so they will
update A and C. Using this query as the basis for a form, I have a datasheet sub
form that shows all the records in the table C. The subform is linked child to
master using one field, say field 1. On the parent form is also field 2. When
selecting a record to show in the datasheet in the subform, the records relating
to ALL field 2 entries are shown. I would like to filter this so only the
possible records shown are those matching the chosen value in the field 2 on the
master form.
Now, I think I have captured the essence of the situation, thought I fear that
it is not clear. In the process of creating this master form, I have worked up
three queries to get to the working situation that I currently have. To describe
this relationship would really confuse matters. I have created a mini version of
the database that I would really like for you or someone to look at and see if
you can figure out if it is possible to filter the records as I would like.
The problem is apparently because you're trying to do everything all
on one form.
Typically, a many to many relationship is handled using a Form based
on one of the "one" side tables (TableA let's say), with a Subform
based on the junction table, TableC; the master/link field would be
the primary key of TableA and the corresponding foreign key in TableC.
On the Subform you would have a combo box based on the other "one"
side table - Table B - bound to the foreign key linked to TableB. This
Subform will then show all the chosen TableB records; on the "new
record" you can choose any existing TableB record as a new entry into
TableC.
If you want to add a new record to tableA and at the same time a new
record to TableB, and then link those two new records via TableC,
typically you would have a *separate* popup form to update TableB;
this can be called from the NotInList event of the combo on the
subform.
